Cant connect to Access database from excel using vba

[jdg]

Hello

I created an excel addin which I am distributing to members of my
organisation. I have managed to install it successfully onto a number of
computers however I am having problems with one computer.

The line that brings an error is as follows

dbConnectStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath &
";"

Where dbConnectStr is a string variable used to set up the active connection
to an access database for example

rs.Open strConnectionString, dbConnectStr, adOpenStatic, adLockPessimistic

and dbPath is the file location of that database.

The computer which is giving problems has both excel 2007 and excel 2003
installed on it which I believe may be the cause of the problem, as all the
other computers only have one or the other installed and the application
works. However this is more of a guess and even if I were right I don’t know
how to fix the problem.

Any thoughts or advice would be appreciated.

Regards,

 

[Tim Zych]

What's the problem?

 

[jdg]

Thank you for your reply, Apologies for leaving out the actual problem.
Unfortunately the person whose pc is giving the error has been out of office
for the last few days so i have been unable to get the exact error message. I
will be able to send the error message through on Monday.
The problem is that the code cannot connect to the database (which is saved
on a network drive), its as if the it cant find the database, however i
checked that the file location (dbPath in code below) for the database was
correct. And it connects with no problem on all the other computers.
I will be able to provide more details on Monday though.
Thanks again.

 

[Tim Zych]

Is dbPath using a mapped drive letter? Maybe their machine has not mapped
that drive. Have you tried using the UNC (\\server\dir\dbname.mdb) as a
test?

 

[Chilidog]

Tim,

I have a similar problem. I have pushed an Excel tool that uses Access as a
backend to a mapped drive letter. It works fine for me, but I have users
that have access to it as well. The problem is that that I mapped it to
something like V:\drive and they might have mapped it to W:\ drive. Excel
then kicks out an error to them saying that the file location is not valid
and fails to call the Access tool. Could you elaborate more on the UNC
(\\server\dir\dbname.mdb) solution you mention, or point me to some resources
around that? I appreciate any assistance you might be able to provide.

Scott Elrod