ADODB Connections & Recordsets

[Ejohnson]

Hello all,

This is my first post, although I've found this forum very useful over the past couple of years (thanks to all who contribute).

I have a general 'best practices' ADODB question that I haven't resolved through my various references and online searches. I'm wondering what is the best practice for connecting to dbs and creating recordsets with sql, when one needs to get info from a database many times during an access session.

For example, if I use vba like this in a form of some kind:

Dim cnDB as ADODB.Connection
Dim rsDB as ADODB.Recordset
Dim strConnection as string
Dim strSQL as string
Dim strPathtoDB as string

strPathtoDB = "path to db goes here"
'(put real path in)

strSQL = "SELECT * FROM TABLENAME"
'(put real SQL here)


'SET UP DB CONNECTION FOR USE IN THIS SESSION
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoDB & ";"

Set cnDB = New ADODB.Connection
cnDB.Open strConnection

Set rsDB = New ADODB.Recordset

With rsDB
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.LockType = adLockBatchOptimistic
.Open strSQL, cnDB
.ActiveConnection = Nothing
End With

'do whatever with the recordset, e.g. reading records

'and then ...
set rsDB = nothing

​

And later, I need to query the database again, say from a different form ... what should be done at the tail end of the code above to leave things in good shape for further querying of the db using cnDB?

Should I:

1).

cnDB.close
set cnDB = nothing

​

(this would require completely redoing the connection to the db next time I need to query it)

-or-

2).

cnDB.close
​

(presumably I could just cnDB.Open strConnection next time I need to use it)

-or-

3). Something else entirely.

When I first starting using ADODB, I would create a new connection, and then close it and set it to nothing after each use of a recordset (e.g., number 1 above).

This worked okay for a while, but as the application grew, I started to run into errors related to the connection. My understanding now is that constantly opening new connections and closing them greatly increases the risk of db corruption.

If I need to populate recordsets from the same db many times during a user's session in an application, should I open the ADODB connection once on startup only, and close/set it to nothing upon exit from the application?

Any thoughts would be greatly appreciated!

-- Eric Johnson
​

 

[Mr. B]

Eric,

Just curious, since you are trying to get data from another Access database, is there some specific reason that you are not simpley linking to the tables in the database.

I personally use DAO which is now the native data connection type for Access.

 

[SOS]

I personally use DAO which is now the native data connection type for Access.

To state that a little more correctly - DAO IS the native data connection type for Access. It always has been. Even when Microsoft had A2K and A2K2 have ADO set as the default in the References, DAO was still the NATIVE data connection for Access/Jet.

 

[Mr. B]

SOS,

Thanks for the clarification. I appologize if I did not state this cleary enough.