Don't bother opening more than one connection to the database unless doing special operations that require it. The most common mistake programmers make when coding for databases is opening several connections at once. The second is not coding for multi-user usage. Here are a few things you should know and learn before tackling a database project. Open a database once, use it then close it when you are finished using it. Once you open a connection you can use as many cursors as you like or as the database supports. Having many connections open at once is a waste of resources (unless absolutely needed) and will lead to some unpredictable failure of the database or application. The only time this method should not be used is in a web based application, then you would want to open the connection do what you need then close the connection. You will do this for there may be ten, hundreds or thousands of users hitting the database and you want each one to get a connection when it is needed.
Opening and closing the database leads to more work for the database and possible corruption (unless it is a web database). Think about your database as if going to the library. Think of the books on the shelves as recordsets and the library as the database. Would you leave and re-enter the library every time you looked through a book and placed it back on the shelf? No, that would be really stupid and the same reasoning applies here. Think of all the extra effort that would take! Even if you were in top shape this would soon tire you out. Some databases support a limited amount of connections generally one connection per user. Let's say your database had a license for 10 users and your program took up five connections. You see at that rate only two people could use the database with your program not ten!!! So you can see how you could waste enormous database resources.
Do not fall into the database connection trap. Efficient code manages all of its resources not just some. Following these rules will not only make you a better coder it will make your app easier to support. One misconception on Access databases is that it can only support a very small number of users (under 10). This however is not entirely true. The real problem is ALWAYS the code. People do not code a multi-user database system correctly and then blame it on the database. Access does not have all the behind the scenes work being done automatically so YOU must do more work.
It takes a lot more effort and understanding to code a true multi-user database system and most will not even try to do it correctly or don't understand what exactly needs to be done. Not a believer? Just asks the coder what concurrency checking, record locking and transaction processing are and if they are doing it. Many skip this because it takes up a considerable amount of coding to accomplish. All these things HAVE to be done to have a true mult-user database work properly.
You can avoid major problems by using simple multi-user checking techniques.
1 - Read the record with a lock then modify
2 - Before updating to database RE-READ
the record to see if changes have been made.
3 - If changes have been made, dump your record then add your changes to the retrieved updated record. Checking all fields that you are updating to make sure you are not overwriting previous update or verifying that it is OK to overwrite.
4 - Update the record and commit the data.
Following these rules should allow you to be error free in your updates in a multi-user environment. Doing these steps inside a transaction will help you best.
Here is a VB sample project of Concurrency Checking in a Multi-User EnvironmentNote: I can't stress this enough for VB coders. Place your SQL statements into a variable and do a Debug.Print of your SQL statement before attempting to execute it. It will show you exactly what the statement looks like and will bring to light possible obvious errors in your statements
. Get the Multi Database ControlVB Memory Leak Test with Databases