Reading with ADO from Access database over network

[Marius]

Hi everybody !
I made an Excel sheet that is reading from an Access
database (using ADO)) located on other computer in the
same workgroup.
From time to time, when trying to open the ADO connection
it reports an error (no. -2147467259) saying that the
database is open exclusively...
To overcome this situation my solution is to manually
display the source folder content using Network
Neighborhood - then the connection can be opened normaly,
without error.
Do you have any solution to avoid such problem.
Thanks in advance !
Marius

 

[onedaywhen]

Is your ADO code trying to open the Connection with exclusive locks
e.g. Mode=adModeShareExclusive? If so, instead consider locking the
*data* at the row- or perhaps table-level rather than locking the
database.

If someone else if doing this (e.g. another application, a user
opening the DB in the MS Access GUI, etc), find out who it is and ask
them to stop! You may need a different security approach e.g. use
workgroups security so the database is only locked when someone with
administrator rights actually needs to do administrator-type things in
the database.

 

[Marius]

For sure it is not the problem that someone is opening
exclusively the database. The error reported in err.no -
2147467259. Once again, my solution is manually opening
the folder using Network Neighborhood (or Explorer) -
after that the connection can be opened without error.
What I need is to programatically mimic the usage of
Network Neighborhood or something similar to refresh (???)
the connection - apparently the database host computer is
not found.

 

[onedaywhen]

As I often ask, do you want to treat the the symptoms or solve the
problem? I suspect the *problem* isn't the failure to open the folder
using Network Neighborhood programmatically.

Re: the error you quoted. This from MSDN: "AD0 2.7: Provider Errors:
When a provider error occurs, a run-time error of -2147467259 is
returned. When you receive this error, check the active Connection
object's Errors collection, which will contain one or more errors
describing what happened."

Perhaps you could follow the above instructions and post the *ADO*
error descriptions rather than the VBA run-time error.

--

 

[Marius]

I checked the connection.errors collection, has 1 item
with err.number the same 2147467259.
At this time the connection is not opened.
I know for sure what the solution (the manual solution
with NN) and try to avoid claims from the users that
cannot read the database from time to time. I suspect that
is something with the network - some computers cannot be
found sometimes.
Marius