run vbscript in access

[rcollins]

I am trying to copy a database from the c drive to the r drive. I have a script that will copy the database, but what I really want is two things: when a user opens the main database, I want R:\MDS_DBs\Medi cal\UIs\NewMeds .mdb to copy to C:\Database\New Meds.mdb, and when one specific user modifies the database on her c drive, I want C:\Database\New Meds.mdb to copy to R:\MDS_DBs\Medi cal\UIs\NewMeds .mdb. Can you run a vbscript at open and close? If so, how? Thanks

[pod]

Hello RCOLLINS

You might run into problems in the second part "when one specific user modifies the database " if another user is in the process of copying the file.

You could separate the Tool from the Data: two files on the network, i.e. one file for the FORMS and one file for the Database tables. You would have to create your own record locking scripts but this way the data is always up to date... you can either distribute the tool or have everyone use the same one on the network

You could also do the project in .NET, it could do exactly what I described and it distributes the tool onto the user's computer (automatically)

Hope this helps


P:oD

[rcollins]

I already have the databse seperated. It has always been a common practice here to keep the front ends seperate from the tables, excpecially since we have so many databases that aere all linked together.
As far as the rest of your post, I guess I am not understanding. Can you give me more detail?

[pod]

Maybe I do not understand exactly what you want to do. But the setup you are trying to get, "users overwriting the main database", can be disastrous.

If user A and user B both take a copy of the database approximately at the same time (a few seconds apart), and they both make changes to their copies.

Then user A overwrites the main database with his copy ... so far everything is fine.

But when user B overwrites the main database with his copy, user A's changes will be lost.

What you need is to keep the main database on the network, add a locked record table (tblRecordLocki ng) which holds at least the information about the accessed record and the user.

So when a user requests a certain record, the script will check if the record is locked by another user or not and will give the user either editing rights or READ-ONLY access if locked.


As for what I was saying about .NET, it simplifies the tasks of distributing the tool.

Keeping the main database in a centralized location is the key.


I hope I made myself clear and this helps you in any way


P:oD

[rcollins]

Lets see if this helps:
When User A closes her database, the front end (not the tables) needs to be copied to the server. The changes she makes are in the queries, forms and reports. Now user B, C ect open the database (front end) they will need to have a surrent copy of the changes that user A has made. I could do this manually, but I would be putting new copies 3 to 4 times a day and for 6 users. this gets old after a while. Thanks

[NeoPa]

Shell() will give you access to running something as from the command line (VBScript file for instance). The problems I see are that regardless of whether the code is stored in a VBScript file or run from within the VBA of the database, the user, to trigger this, will need to have the database open.

It may well be possible to copy FROM an open database, but copying TO one isn't without some checking in your VBScript code.

[rcollins]

The very first thing the user sees when they open the database is a front end that manages the databases they can use. When they click on the one they want it actually opens a whole new database. So technically, when they open the database, the one to be copied is not open yet. Also, copying to and from the network, these databases on the network aren't opened as we put a copy, not a shortcut on thier desktop. Does this make sense?

[NeoPa]

That makes sense. I expect you can handle running the VBScript using the Shell() command then. You could also handle file manipulation to a fair extent from within VBA of course, but if you already have the VBScript files available then I'd use Shell() to invoke them.

[rcollins]

So next question, where do I implement Shell()?

[NeoPa]

I'm not sure what you mean :S

Shell() is a VBA function which you can trigger from anywhere you can trigger VBA.

Be aware though - Shell runs Asynchronously. This means the following (succeeding) line of VBA code will continue to execute as soon as the Shell() command has been invoked. It will NOT wait for your script to finish before continuing.

I'm currently working on a routine which will work as an equivalent to Shell() but with a wait built in. It involves Windows API function calls so it's a little cumbersome, but if you're interested just let me know.

To be sure, it's generally only needed when you want to trigger something to run AFTER your script has completed or you want to stop the operator from doing anything else until after it's completed.