Solved

Run MS Access Module from Macro / Bat File

Posted on 2016-11-15
9
75 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 52

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 52

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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 52

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 52

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

867 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

16 Experts available now in Live!

Get 1:1 Help Now