ADODB.Connection network access interrupted.

[ccordner]

I have a macro which is required to pull information from an Access Database, so I set up a connection using the code below:

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

 

[jsb1921]

on error resume next
with
... ... ...
End with
if err.number <>0 then
 goto exit1
end if


exit1: set Set cnnDB = nothing
exit sub

if this helps you, pl mark it as a solution for the future readers of the thread

 

[ccordner]

Good evening

Thanks for replying. This stops the error message, but it isn't killing the connection to the database. If I run the code with that bit in, it just does nothing taking that code out again just brings back the same error.

Thanks
Chris.

 

[jsb1921]

post ur full code
after if end if, you will have your processing.
after your processing you will do
cnnDB.close
set cnnDB = nothing
if you google, there are lots of samples.

 

[ccordner]

Good evening

I've tried setting cnnDB = nothing before doing anything else, and after creating an ADODB connection, I don't think it's the connection that's causing the problem - I think it's the database itself that isn't letting Excel connect to it.

I can't really post the full code because there's pages and pages of it.

Thanks
Chris.