Losing ADODB.Connection

[mikeymay]

I am using the following to create a connection to an Access Db file via the Workbook_Open procedure in ThisWorkbook

Set cnConnection = CreateObject("ADODB.Connection")

strDbName = ThisWorkbook.Path & "\" & strDbFile

cnConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDbName

In a module I have

Public cnConnection As ADODB.Connection

to set the connection variable to span all modules, userforms, etc

I am then adding new records to the Db which executes as expected the majority of the time but sometimes the connection is lost and I'm wondering if there is a default timeout period for the connection?

Sometimes I can add a new record, but I also have an amend process that finds a record, populates a userform with the record details and then I want that already open record to be updated but at the point of amending the record

rsDriver.Fields("Surname") = strSurname
rsDriver.Fields("First_Name") = strFirstName
rsDriver.Fields("Location") = strLocation
etc
etc

The connection has been lost which I am assuming is because of a timeout and as I'm stepping through the code for development it's taking a while to get to the point of amending the record.


TIA

 

[Norie]

Do any errors ever occur in the rest of the code?

Perhaps errors 'hidden' by On Error Resume Next, or something similar?

 

[ZVI]

This can be effect of a memory leakage - see BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO).

 

[mikeymay]

It only seems to happen after a period fo time has passed, as if I run an Add and then and Amend quickly there is no issue.

I have set a timeout to a persistent connection with

cnConnection.CommandTimeout = 0

which seems to be working.

If the problem continues I will just have to make the connection again and where a query is open, re-open to enable amending. Not ideal and going round the houses a bit but should work.


Thanks

 

[mikeymay]

So I have just checked the timeout for the connection and it's still at '15'.

Prior to adding the timeout code it was 15.......but the connection seems to be persistant now...