Automate to Run Module without opening Access (1 Viewer)

donsi

Registered User.
Local time
Today, 02:26
Joined
Sep 1, 2016
Messages
73
Hello all,

Getting stuck again on trying something new. I have few action queries which updates TblUsers based on imported table from HR data to addnew users and update current users. I put this action query in a VBA module(ActiveListSync) and attempting to run a VB Script to run the module without having to open Access. (Want to schedule the script with windows task to run it everyday at certain time) Every time I run a script it gives me an error. (see attached)

Error: "MS Access can't open the database because it its missing, or opended exclusively by another user, or it is not an ADP file.
Code: 800A1EBA



Here it code for module

Code:
Public Sub Sync()
'Running active List Sync to update users table
Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "QryAppendUsers", dbFailOnError
dbs.Execute "QryAutoUpdtUsrs", dbFailOnError
dbs.Execute "QryUpdtUsrsDeact", dbFailOnError
dbs.Execute "QryUpdtCarDeact", dbFailOnError
End Sub


VB script

Code:
dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = False
accessApp.OpenCurrentDataBase("\\hwhr-fs-01\Root Data 01\Data\ParkingPermit\Test.mdb")
accessApp.Run "ActiveListSync"
set accessApp = Nothing
 

Attachments

  • VB error.png
    VB error.png
    24.2 KB · Views: 133

Solo712

Registered User.
Local time
Today, 05:26
Joined
Oct 19, 2012
Messages
828
VB script

Code:
dim accessApp
set accessApp = createObject("Access.Application")
accessApp.visible = False
[COLOR="Red"]accessApp.OpenCurrentDataBase("\\hwhr-fs-01\Root Data 01\Data\ParkingPermit\Test.mdb")[/COLOR]
accessApp.Run "ActiveListSync"
set accessApp = Nothing

Hi, try putting brackets in the directory in the path whose name has spaces in it. like
Code:
\[Root Data 01]\

Best,
Jiri
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:26
Joined
Aug 30, 2003
Messages
36,118
Also, you don't run the module, you run the desired sub or function.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:26
Joined
May 7, 2009
Messages
19,169
I thought I would see what the title does.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:26
Joined
Feb 28, 2001
Messages
26,996
Jiri, Windows file semantics allow the presence of spaces in a path element. You don't need brackets - but DO need the whole string to be quoted.

Donsi, you normally do this sort of thing by building a named Macro that will include a RunCode of a function (and the macro ends with an Application Quit action.) You can make the function call your subroutine. Nobody can tell you why, but Macro RunCode actions require that the code to be run is a function, not a subroutine.

Then what you schedule is to launch the database in a way to run the Macro. Look up Access Command Line Options for the /X:macroname option. You probably also need to schedule that operation to run as a valid user of the database if it cares about users.
 

Users who are viewing this thread

Top Bottom