ADO Connection Method

[AGP]

I have created an application that uses data pulled from an access database. Each time that data needs to be displyed I create a new ado connection and recordset. Would it be better to create one connection and just change the recordset each time? This would certainly reduce the amount of code, but how would it affect performance?

 

[Morpheus1981]

Well I've been wondering the same for a long time. What I have found is that the right way to do it is opening the connection just once and change ur recordset any times u want. Even if u don't feel so sure about this open ur conection by forms, what I mean is onLoad: "opens" and Unload: "closes" it.
One more thing is: that recordset can be defined to save or expend resources based on ur needs. For example if ur closing the conection after a query try to create a recordset: ForwardOnly, ReadOnly, this kind of cursor expend less resources then any another.
Please have this in mind if u don't close the conection u can use this kind of recordset:

Location : adUsedServer, Type =Dynamic, Lock: Optimistic
(Some of these conditions depends on the database)
But if u do that u can use ur recordset with all its power
for exmple u'll be able to do:
Dim rs as New ADODB.recorset
rs.AddNew 'Set the fields to add a New Record
rs.Update 'Update changes and New Records
rs.Delete 'Deletes record(s)

And so forth. this kind of recordset are the most expensive,
be careful when using it.
I hope this help u in some way.

 

[genomon]

Maybe educate me too! I have always DIMmed the objects like this:
Dim cnConnection As Connection
Dim rsRecordset as Recordset
Then use a Set statement to instantiate the object with the New Keyword:
Set cnConnection = New Connection
Set rsRecordset = New Recordset
I guess implicit in VB6 is the connection and recordset objects created in this manner are ADO. Only been doing it this way (it DOES work) 'cuz that's how I was taught.
Can you actually create an instance of the object in the DIMension statement? Thought that just set aside memory...
-Geno

 

[petermeachem]

I always make the connection global, open it once and shut it once. No problems, can't think of any reason to do otherwise. Peter Meachem
peter@accuflight.com

 

[chiph]

AGP-

I always have one connection object, unless there's reason to connect to multiple databases at the same time (one per database, I guess would be a good way to explain it).

Genomon-

You're on the right track. You should always Dim the variable as the correct type, then instantiate it with the New keyword. What I'd do differently is prefix the types with the COM typelib name:
[tab]Dim adoConn as ADODB.Connection
[tab]Set adoConn = New ADODB.Connection

That way if for some reason I add a reference to another typelib that also has a Connection object, I'm assured of getting the right one.

Chip H.

 

[BasSchouten]

I always use a class i've created to setup the connection. I make extensive use of disconnected recordsets, so every time i need the recordset to connect to the database i set the Connection property of the recordset (or command) and afterwards i set the conneciton to Nothing, cvreating a disconnecte drecordset.

The dbConnection class has a function "Connect" which returns a connection-object. The function checkes if the connection is open. If it is not open, the globaly defined connection-object is opened, if it is already open .. it stays that way.

This way you dont have to check the state of the connection while working with the recordset, and share the connection object among several recordsets. Bas Schouten
System Development & Webdesign
CBIS BV Holland

 

[BigDaddy]

I agree with Bas Schouten. One global connection for your database and then open it when you need it. The environment I work with may lead to this style though. I work with connections to Oracle, or MS SQL and the number of connections your applications keep a hold of is important. You don't want to connect to the database at the beginning of the day and have it maintained throughout the day by a user keeping the application open on his/her desktop.

By opening the database, when you need it and closing it when you are done, you keep the overhead on the server as low as possible. By this I open the Db on retrieval of data, create the disconnected recordset, close the db, the user does whatever he/she needs to do with it, open the db, make the edits (addnews, updates, deletes), close the db.

The determination probably would be different if you have one user accessing an MS Access database though. The first method will allow you to scale your applications to many users and different database systems easier though. One more benefit of this idea, is that with DCOM or MTS, you can begin to share object pools. User A creates and closes an adoConn, User B can use that object (no resources needed for instantiation) when they need it.

 

[BasSchouten]

How much overhead does an open connection generate when it is not used with any recordset?
And how much time does it take for a connection to open?


Does anybody have any numbers or ideas on that? Bas Schouten
System Development & Webdesign
CBIS BV Holland

 

[petermeachem]

I think it rather depends on the programme you are trying to write. Most of my stuff is for not a lot of users and not a vast database. The most is 50 users, usually around 10.

I have always been a great one for doing something in the simplest possible way that works. Laziness you see. And I think laziness is a good thing for a programmer as it discourages you from making the programme overly complex, putting 'features' in that no-one actually wants and making the whole thing harder to debug and less likely to work.

So I open the connection and close it just the once. After all most users actually use the programme (or at least try to) so they are doing reads and writes the whole time. So I don't see the point in opening and closing connections.

I shall carry on just as I am now until such time as I have to alter the method for some suitable technical reason and not just because it is a fashion.

I feel much the same about using classes. I can't actually see any advantage from my point of view (single stepping through a 'classy' programme is a nightmare!), and I can't see any advantage from a speed point of view either (must be a longer execution path), so I shan't bother thanks very much!
Peter Meachem
peter@accuflight.com