***//ADO coding in modules

[jhsurti]

Is it necessary to declare a connection object every-time in every module which tries to access a recordset from the the same .mdb file the module is in? e.g.
dim cnn as connection
Dim rst as ADODB.Recordset
set cnn = New ADODB.Connection
Set cnn = CurrentProject.Connection
rst.Open "tblMyTable", cnn

'other code

Should it not be simple if u r accessing the same .mdb then the module should assume the active connection to the current db. why do we have to declare this in every module?

If anyone can throw some light on this, i would be grateful.

 

[BillPower]

Hi jhsurti,

This is what I often do.

In the Declarations Section of a Module:

Global cnn As ADODB.Connection

In the On Load Event of a Form that Loads at StartUp(Switchboard or Menu and one that remains Open):

Set cnn = New ADODB.Connection
cnn.Open "Provider=MSDASQL;" & _ 'Example Only
"DSN=Demo;" & _
"Uid=;" & _
"Pwd="

In the On Unload Event of the same Form that Loads at StartUp:

cnn.Close


You will be able to use "Cnn" from any Form now.

Bill

 

[danvlas]

ADO is NOT Access.
It's a different object and it has to do with data from various sources.
Therefore, when invoking ADO, you have to tell it where to connect to get the data. It is just a 'coincidence' that you're accessing data in the current project.

You don't have to do it in every module, just dim them as global variables and you have to set them only once. But think about the memory overload: your connection and recordset will be two persistant objects opened in your application. Good programming practice states that any object variable should be set to Nothing when not needed anymore.

Hope it's clearer now.





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[TommieB]

I would suggest that you create a code module. Create a function that makes the connection. Pass your sql statement to the function, place the returned recordset into an array and pass this array back to the calling event. Create a sub that closes the connection when the data is returned.

This way you only have to write the connection code once and can use it over again by calling it and passing a different query.

 

[jhsurti]

Thanks a lot. That cleared my doubts and taught me something. I am really thankful to u all.