jjrr007
asked on
Run MS Access Module from Macro / Bat File
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.
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"
Also, if Import is a module, don't you actually want to specify the name of a SUB in that module to execute?
~bp
~bp
ASKER
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 picture
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
Here is a small example, try it there and if it works then look at the sample macro, module and function.
~bp
Database5.accdb
~bp
Database5.accdb
ASKER
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?
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?
" I needed to rename the module import to something different than the function name. "
Exactly what Jim noted :-)
Exactly what Jim noted :-)
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
DoCmd.SetWarnings false
Application.DisplayAlerts = false
~bp
Open in new window
~bp