Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Run MS Access Module from Macro / Bat File

Posted on 2016-11-15
9
Medium Priority
?
196 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 57

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 57

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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 57

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 57

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

704 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