I have a VB project that uses an A2K DB as the DataSource. In my code I use a Public Connection sub that keeps the connection active for the entire time the application is open. I have been wondering if this is the most efficient way of doing this or should I be opening and closing a new connection each time I need it. I am looking to reduce the amount of network traffic but I also want to reduce the footprint that the application makes on each PC.

Here is the sub:
Public Sub Connection()

'Set DB Connection
Set cxn = New ADODB.Connection

'Define and open DB Connection
With cxn
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Path\DB.mde"
  .Open
End With

End Sub

The connection is closed when the application exits by the way.
Any suggestions would be greatly appreciated.

Kenny Alligood

__________________
Kenny Alligood

ideally you should only stay connected to your DB when you are doing something on the tables. you can stay offline (disconnected to your db) even when you're working on a data set. or if you really want to stay connected, why don't you try to stay connected on a module level basis. that is you won't stay connected for the whole duration that you're program is running.

I strongly disagree.

Opening and closing the connection to the database creates a tremendous amount of overhead. It also can create problems especially in Access if you frequently open and close the connection to the database. You can find documentation on MSDN.

In some cases using disconnected records is a very good idea. But correct me if I am wrong you still need to maintain the database connection even though the recordset is disconnected.

My approach developed over time trying to optimize performance and maintain the integrity of the data has been to use a global connection object and a local recordset object. I connect to the database when the application starts up and disconnect when the application shuts down. Recordset objects I keep local to the function and if needed to share just pass it as a parameter.

Now these are general rules I try to follow. Most of the applications I develop are data centric. As with anything else you need to evaluate the situation.

Good post. I hope this helps.

Larry Asher

Ok. I guess I could learn a few things from you regarding optimization.

In your data centric objects you passed along the global connections right? This has been my concern also. What I wondered about was how they (or how I could implement it) use it ASP if you have this global connection. I just want to learn this concept, as I am not much into ASP.

When I mention global I am talking about declaring the variable at the project level. Therefore, I only create one instance of the object and it can be seen anywhere in the project. Typically I will use a variable named gCn which represents the global connection object. I will also have a function that make the database connection where I pass the connection object by reference. I do this because if I need to make a different connection for a specific purpose I can then pass a local connection object, lCn.

As mentioned in my last post you really need to evaluate the use of the application. Many of the application I write exist solely to allow users to access data. Some examples are Document Management, Order Entry, or Scheduling. These applications are very data intensive and the data is dynamic. Most of the time the data comes from one database that becomes the gCn. If I need to access a different database to complete a function I will make a temporary connection using the lCn.

Also, I have done some application that poll for data to process. In a case like that I will not want to maintain a connection to a database. I still use a global connection object but within each function that executes a call to the gCn I call a function named CheckDBConnection. In this function I pass a connection object by reference if the object is set to nothing or the state is not open it will pass the connection object to the DBConnect function creating the connection.

Hope this helps.

Larry Asher

Ok. I already know the concept you explained. But thank you for the info.

But what I'm trying to understand is if it is implemented in an internet connection, say a page accesses one of your ActiveX exe (which may be handling all the processes -fetching, saving records for all your COMs), where does the global connection saved. If it is VB application, or some other language may be, I could have a global connection as long as my program runs. But what I would like to understand is that can the explorer keep the same object (our global connection)?

Hope my explanation is clear.

Thanks.