Solved

Run MS Access Module from Macro / Bat File

Posted on 2016-11-15
9
46 Views
Last Modified: 2016-11-15
I'm trying to run a MS Access module from a bat file.  When I run the code below, I get an error saying- can't find the object import.
Import is a module, I think I would need to run the module from a Macro, but I'm not sure how to do that.

START /WAIT "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "E:\Water Meter Project\WaterMetering v2.2.accdb" /x "Import"

Open in new window

0
Comment
Question by:jjrr007
9 Comments
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41888363
First thing, try this.  There is a little feature (quirk) of START that requires the TITLE parm (can be blank) when the program to launch is quoted...

START /WAIT "" "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE" "E:\Water Meter Project\WaterMetering v2.2.accdb" /x "Import"

Open in new window

~bp
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41888371
Also, if Import is a module, don't you actually want to specify the name of a SUB in that module to execute?

~bp
0
 
LVL 1

Author Comment

by:jjrr007
ID: 41888378
I tried adding the double quotes and get the same error.  How do I make the change you mentioned about specifiying the sub?  Below is the module's code.  I tried to call the import module from a macro and get the error shown in the pictureError when I try to call Module from Macro

Function Import()
On Error GoTo Import_Err

DoCmd.OpenQuery "Step 2- Delete", acViewNormal, acEdit
DoCmd.OpenQuery "Step 1- New Data", acViewNormal, acEdit
DoCmd.RunSavedImportExport "ImportNov14"
DoCmd.OpenQuery "Step 4", acViewNormal, acEdit
DoCmd.OpenQuery "Step 5- Processing", acViewNormal, acEdit
DoCmd.OpenQuery "Step 6- Save Historical Data", acViewNormal, acEdit
DoCmd.OpenQuery "Step 7- Processing", acViewNormal, acEdit
DoCmd.Quit
Import_Exit:
    Exit Function

Import_Err:
    MsgBox Error$
    Resume Import_Exit

End Function

Open in new window

0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 41888388
Import() must be in a standard module (not in a form and not in a class module), and no module can be named the same as any function.

Jim.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:jjrr007
ID: 41888399
I'm not sure how to do what you mentioned.   I clicked on the create tab, then click on module icon.  Then, I pasted the import code in the screen and saved it. When I try to run it from the macro, I get the same error.  Not sure what to do?
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41888412
Here is a small example, try it there and if it works then look at the sample macro, module and function.

~bp
Database5.accdb
0
 
LVL 1

Author Comment

by:jjrr007
ID: 41888480
Based on the database you provided, I found my mistake.  I needed to rename the module import to something different than the function name.  They had the same name.  Once I did that, it ran fine from a bat file or from the macro itself.

One issue,  I got a message box to ask if want to delete a table that was part of the function import.  Since this bat file will be run automatically, how can I suppress these messages from appearing?
0
 
LVL 75
ID: 41888531
" I needed to rename the module import to something different than the function name.  "
Exactly what Jim noted :-)
0
 
LVL 51

Expert Comment

by:Bill Prew
ID: 41888627
Try one or both of these, I haven't done this in Access in a bit, but I think they are the proper way to suppress in your VBA code right before it exists or closes.

DoCmd.SetWarnings false
Application.DisplayAlerts = false


~bp
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

You may have already been in the need to update a whole folder stucture using a script. Robocopy does it well and even provides a list of non-updated files in a log (if asked to). Generally those files that were locked by a user or a process by the …
I have published numerous articles here at Experts Exchange that present programs/scripts written in a language called AutoHotkey. Each of those articles has a brief paragraph describing where to download the product and how to install it. I have al…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now