Can't reuse public ADODB connection to SQL Server. Works once fails the second time.

[Martinc4611]

I haveAccess frontend/SQL backend application that uses an ADODB connection toconnect to SQL server. The application is an imaging system with roughly 4Kusers that populates data on forms by creating ADO recordsets and setting therecordset value to the forms to display data. Currently, the application opensa connection, opens the recordset, sets the form rs= to ADO recordset, andcloses the connection. This results in a lot of opening and closing ofconnections, which slows performance. I would like to set a globalconnection and reuse that connection (as opposed to opening and closing theconnection). Here is the problem. It works perfectly the first time theconnection is used. However the second time the connection is used, the codestops on the open recordset and just hangs (no error, just stops execution).The connection is open, it just doesn't work the second time. It’s like it iscreating a use once connection.

Any ideas on how to create an ADODB connection that can be used more than once. Here is a small sample of the code:

'Function to open SQL connection
Public APPcnn as ADODB.connection
Public mysql as string
Set APPcnn = New ADODB.connection
APPcnn.Open "ODBC;Description=DSC_RADD;DRIVER=SQL Server;SERVER=sqlserver001;DATABASE=ImagingSystem; Trusted_Connection=Yes"
...



'Code to open ADO recordset
Private rs As New ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open mysql, APPcnn, adOpenStatic, adLockReadOnly (note, mysql is public)
Set Me.Recordset = rs
...

[rpeare]

Where are you closing the recordset?

You'd have to close it before you can re-use it.

have you put in debug.print statements in your code to determine where it's breaking?

I'm guessing it's when you try to open a recordset when you already have a defined/open recordset with the same name but without knowing where it's breaking that's just a guess.

[Martinc4611]

rpeare - very good guess!! I didn't inlude all the code but we are closing the recordsets; however, maybe we missed one. I am also using a few public recrodsets (CursorLocation = adUseClient) that may be causing a problem. It is exactly as you described, it blows on open recordset (actually it doens't blow, no error it just hangs). This gives me a good starting point. Thank you!