Updating ADP - Which ADODB connection to use

[Diarmuid]

Hi
One of my team is updating an Access ADP which I developed years ago. So he is after creating an ACCDB using Access 365, linking to tables via ODBC, and most of it is working.
However, there are some strange connection issues occurring, with the connection dropping after a certain amount of time.
Looking through his code, I see

Dim rsSubmissions As New ADODB.Recordset
Dim cnnADO As ADODB.Connection
Dim stSQL As String
    
    On Error GoTo Err_UpdateSubmissions
    
    stSQL = "SELECT * FROM Submissions WHERE SubID = 1"    
    Set cnnADO = CurrentProject.Connection
    rsSubmissions.Open stSQL, cnnADO, adOpenDynamic, adLockOptimistic

   Do stuff
   
    rsSubmissions.Close
    cnnADO.Close

Which is the code as it was back when the database was an ADP. I'm actually surprised that CurrentProject.Connection still works. I remember that Access ADP had a SQL Server connection. But I don't understand how this maps to ACCDB. After all, linked SQL tables could be from multiple databases. Online help just states:
Use the Connection property to return a reference to the current ActiveX Data Objects (ADO) Connection object and its related properties. Read-only Connection.

Anyway - Is this still an accepted way of creating an ADO connection? Or is there a better way?

Thanks
Diarmuid

 

[sonic8]

I'm actually surprised that CurrentProject.Connection still works. I remember that Access ADP had a SQL Server connection. But I don't understand how this maps to ACCDB.

It creates an ADODB.Connection to the current ACCDB database, which in turn then uses an ODBC connection to the SQL Server backend.

This works to some extent, but I would not do it this way because you forgo the advantages of a direct ADO connection to the backend and it also might cause some weird glitches. E.g. try to check the RecordCount of such an Recordset and you might be unpleasantly surprised.

My recommendation: Replace CurrentProject.Connection with your own, custom ADODB.Connection directly to the SQL Server backend.

 

[Diarmuid]

Apologies for late reply. Thanks for your help.
We did indeed get some weird glitches, replacing with custom ADODB.Connection solved much of it.