Solved

Run MS Access Module from Macro / Bat File

Posted on 2016-11-15
9
102 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 54

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 54

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 54

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 54

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

679 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