vbscript to start an Access database

[seaport]

My database system is an front-end/back-end system. The backend db is on the file server and the frontend db is on users' local computers.

I created a vbs file and intended to use it to update the frontend database automatically. A user runs the vbs to start the frontend db. The vbs checks the version number of the frontend db and, if the verison number is not right, copy the front-end db from the file server over.

Here is the script.

'code to check the version number

'code to copy over frontend using FileSystemsObject

dim appAcc
set appAcc=CreateObject("Access.Application")
appAcc.OpenCurrentDatabase "c:\prod\prod.mdb"
appAcc.Visible=True
set appAcc=nothing

The code works fine. I found out that, after the db is started, the db shuts down without a warning when a user tries to put a form in design mode. This sounds an extra to me because I certainly do not want a user to see a form in design mode. However, I want to know why this happens and whether this means that an Access db started by vbs is less stable.

Any advice is appreciated.

Seaport

 

[FancyPrairie]

I have a vbs that does basically the same thing. However, it checks for several other things also (so I can't just cut and paste it here). But the just of it looks like this (it assumes you have a workgroup file...if not exclude that part):

Dim oShell
Dim FileSystem

'****************************************
'*  Copy New version to user's machine  *
'****************************************

Set FileSystem = CreateObject("Scripting.FileSystemObject")
FileSystem.CopyFile strCopyFrom, strCopyTo

'***************************************
'*  Run new version on user's machine  *
'***************************************

Set oShell = CreateObject("WScript.Shell")
oShell.Run """\\path\msaccess.exe"" ""\\path\FEdatabse.mdb"" /wrkgrp ""\\path\workgroup.mdw"""

 

[seaport]

FancyPrairie,

Sorry for not responding sooner. I got pulled away for another project. Your code definitely works, and so did my code. I just figured out the shut-down problem. It is caused by the CreateObject method. It seems to be that, when using CreateObject method to start an Access database, closing the startup form (closing it or putting it in design mode) will immediately shut down the Access application.

Also, the reason I need to create an instance of Access in the vbscript is that I can run some command or macro inside the Access db. For example, I use the following code:

appAcc.RunCommand 10 'acCmdAppMaximize
[/code}
to maximize the Access window. If I use Shell command to start the Access, I have to use windows API to achieve that.

Seaport

 

[JerryKlmns]

>I have to use windows API to achieve that.

just include a macro in the command line start up options of oShell.Run .... which shall do any maximizations you like!