07-31-2009, 09:59 AM | #1 |
Ironworks Moderator
Join Date: February 28, 2001
Location: Boston/Sydney
Posts: 11,771
|
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? Last edited by Memnoch; 07-31-2009 at 10:27 AM. |
07-31-2009, 02:16 PM | #2 |
Ra
Join Date: March 11, 2001
Location: Ant Hill
Age: 49
Posts: 2,397
|
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). |
08-03-2009, 01:11 PM | #3 |
40th Level Warrior
Join Date: October 29, 2001
Location: Western Wilds of Michigan
Posts: 11,752
|
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:
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.
__________________
*B* Save Early, Save Often Save Before, Save After Two-Star General, Spelling Soldiers -+-+-+ Give 'em a hug one more time. It might be the last. |
08-08-2009, 03:26 AM | #4 |
Silver Dragon
Join Date: August 2, 2006
Location: i ngach aon áit (but mostly Western Europe)
Age: 55
Posts: 1,619
|
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).
__________________
Make a decision, even if it's wrong, and remember.... ...it's never too early for a salad. |
08-08-2009, 05:14 AM | #5 |
Jack Burton
Join Date: May 16, 2003
Location: Dartmouth, NS Canada
Age: 58
Posts: 5,634
|
Re: Any VBA gurus here? Need help!
Hey UV,
..where the heck you been?
__________________
A MAN WHO WANTS FOR NOTHING HAS INFINITE WEALTH. (me) |
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Any Linux gurus? | Zebodog | General Discussion | 13 | 04-28-2006 07:29 PM |
Attention all compute gurus! HELP NEEDED! | Piestrider | General Conversation Archives (11/2000 - 01/2005) | 13 | 12-24-2003 11:13 AM |
Might and Magic GURUS: gripes with wizardry. If any | Strider_314 | Miscellaneous Games (RPG or not) | 13 | 01-30-2002 12:14 AM |
Another question for the gurus | ccannon | Wizards & Warriors Forum | 5 | 06-18-2001 01:35 AM |
Questions for the Gurus | catzenpewters | Wizards & Warriors Forum | 6 | 03-26-2001 10:52 PM |