Solved

Run MS Access Module from Macro / Bat File

Posted on 2016-11-15
9
94 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 53

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 53

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 53

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 53

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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

837 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