I have a macro which is required to pull information from an Access Database, so I set up a connection using the code below:
This is fine, but every now and then, it gets to '.Open' and I get the error:
Shutting the computer down and restarting clears the connection, but this means they have to load up anything else they have open on the computer and (as this is one read-only spreadsheet designed to be used by many people without saving) it means messing around saving a copy of the spreadsheet first. This also then means I have to keep clearing out the 'Copy of' files every now and then.
Is there a way of closing any open or hanging connections to a database in VBA so this error can't happen? I've tried using '.Close' first, but it won't let me close a connection which it hasn't already opened. It's not a common problem, but it's frustrating when it does happen - especially as I'm not always around to fix it?
Thanks
Chris
VBA Code:
Set cnnDB = New ADODB.Connection
' open connection to database
With cnnDB
.CursorLocation = adUseServer
.ConnectionTimeout = 500
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strPathToDB & ";"
.Open
.CommandTimeout = 500
End With
This is fine, but every now and then, it gets to '.Open' and I get the error:
Shutting the computer down and restarting clears the connection, but this means they have to load up anything else they have open on the computer and (as this is one read-only spreadsheet designed to be used by many people without saving) it means messing around saving a copy of the spreadsheet first. This also then means I have to keep clearing out the 'Copy of' files every now and then.
Is there a way of closing any open or hanging connections to a database in VBA so this error can't happen? I've tried using '.Close' first, but it won't let me close a connection which it hasn't already opened. It's not a common problem, but it's frustrating when it does happen - especially as I'm not always around to fix it?
Thanks
Chris