Any VBA gurus here? Need help!
 

I'm trying to learn VBA and I have an Excel model that sends and receives information to an MS Access (all 2003 versions) database that is stored in a shared network location (network location is global read-write access and I have tested this).

Basically the way it works is that I have a macro that automatically runs when the Excel file is opened, that queries the Access DB and sends data back to the Excel file to update some sheets.

It works fine when anyone in my LOCAL site (ie on the site and network where my Access DB is physically located) but when remote users try to use it I come up wiht the following error (attached is the error and the code). It's a run time error run time error 2147467259 80004005 saying "Microsoft JET Engine cannot open \\Natfile02\depts\Global Share Folder\Rev@Risk.mdb. It is already exclusively opened by another user, or you will need permission to view its data".

Anyone know what this means and how to fix it? I've tried everything, including:

1. making sure the database is in a folder with global read write access
2. asking them to map the drive and folder location to make sure it shows up in their My Computer


You can see my code - the yellow highlight is where the debug is telling me the problem is.

Any insights?

Re: Any VBA gurus here? Need help!
 

Not sure how they are connecting remotely but hard coding the network path with "\\whatever" executes as the program trying to find the DB on their own network. Though that would probably give a different error message.

Alternatively depending on what permissions your local users have you might try User Level Security Wizard on them or the User & Group Permissions in Access (In MS Office 2003 try Tools > Security > User & Group Permissions).

Re: Any VBA gurus here? Need help!
 

I see you've got a lot of other things opened there as well, Memnoch. Any chance one of them's this file? That message is basically one of three things:

1. Someone else already has it opened for editing
2. The file exists, but you're not allowed to access it
3. The file doesn't exist, but my coding isn't smart enough to tell you that

Make sure you can access the file on that path directly, and open it up. You can also get funky things when your app tries to open it a second time... *you* might be the one who opened it the first time.

I've also seen Access do *helpful* things when connecting to data sources... like keeping them open after I've closed the query I was working on. Shutting down Access was the only way to get it to let go...

Good luck.

Re: Any VBA gurus here? Need help!
 

The problem is almost certainly related to permissions, unfortunately using MS Access for remote access DB functionality is not as straightforward as it ought to be and nor is setting user permissions.

The code will be running under the context of your various users which most likely will be an account local to each machine. A server sitting next to it, in the same domain or not, will not recognise the user trying to access this NETDRIVE share, and the remote server will deny acccess to it.

I suggest you look at this article: http://databases.aspfaq.com/database...er-server.html

Also bear in mind that all users of an access databse must have read/write permissions for the folder containing the database file, and not just the file. This is because they need to be able to create, modify and delete the database lock control file (dbname.ldb) in order to allow multi-user activity. Without the lock file, only one user will be able to connect to the database at a time.

Let us know how you get on (presuming you haven't already solved this problem).

Re: Any VBA gurus here? Need help!
 

Hey UV,
..where the heck you been?