Access Macro VBA Question

[basebalplayr33]

My problem is pretty simple; I do not know much VBA however so I do not know how to code my problem properly.

Basically I have 5 Access databases that act as an assembly line...Each month I get a file that I plop into a folder that Db #1 references and then I run a macro in Db#1. Close Db#1. Open Db#2 (which is referencing Db #1) and run a macro. Close Db #2. Open Db #3. Run a Macro. Close Db#3. Open Db #4 Run a Macro. Close Db #4. Open Db#5. Run a macro.


Monthly file ----linked------Db#1 run macro-----Db#2 run macro----Db#3 run macro----Db#4 run macro----Db#5 run macro.


Db #5 exports my completed results into a formatted excel template to be printed (this is irrelevant but I figured I'd explain that).


MY QUESTION....

I want to create 1 more database that contains VBA code opens db 1, runs macro, closes db1, opens db 2, runs macro, closes db2, etc.

I think this code should be pretty basic, but like i said I have no experience writing VBA in access.

Anyone have any idea? Any help would be GREATLY appreciated.

Thank you,

Mike

 

[Darkspartan]

Maybe I am missing something here, but you could just import all of the macro's into a singular database and link all of the necessary tables and queries to that database as well.

 

[HiTechCoach]

I agree that everything should be in a single database. That would be best for doing what you want. Is there some reason you can't combine the database into a single database?

 

[basebalplayr33]

I agree that everything should be in a single database. That would be best for doing what you want. Is there some reason you can't combine the database into a single database?

The reason for having multiple databases is simply because the data is too large. I realize that this should probably be run more quickly directly in SQL, however our firm does not have access to a SQL programmer, and all of our analysts are trained in access. Is there some way to write VBA code in another database that simply opens db 1, runs the first macro, closes db 1, etc.?

 

[Norie]

Mike

How 'big' is the data?

 

[xenou]

For what it's worth you can have your tables in separate databases but still put the macros in one. However, you are correct it is not hard to do this with vba. You don't even need to put the code in another Access database - you can use a VBScript file to do that:

For example, imagine this file is saved as RUN_REPORT.vbs:

On Error Resume Next

'//This is the report of ADP hours (labor hours on the clock)
Set objAccess = CreateObject("Access.Application")
    With objAccess
	    .OpenCurrentDatabase "G:\Accounting\ADPHrs.mdb", False
	    ret = .Run("ADPDetailReport")
    End With

'//Alert user of execution completion (optional)
If ret = 0 Then
    CreateObject("WScript.Shell").PopUp "Complete", 3, "Run Report"
Else
    CreateObject("WScript.Shell").PopUp "Completed with errors.", 3, "Run Report"
End If

And your done. Just double-click the file, or even put it into a windows scheduled task to run automatically. My functions return 0 if all goes well, so I evaluate my return value accordingly - you might use true or false instead, or not bother with return values at all.

 

[basebalplayr33]

For what it's worth you can have your tables in separate databases but still put the macros in one. However, you are correct it is not hard to do this with vba. You don't even need to put the code in another Access database - you can use a VBScript file to do that:

For example, imagine this file is saved as RUN_REPORT.vbs:

On Error Resume Next

'//This is the report of ADP hours (labor hours on the clock)
Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "G:\Accounting\ADPHrs.mdb", False
        ret = .Run("ADPDetailReport")
    End With

'//Alert user of execution completion (optional)
If ret = 0 Then
    CreateObject("WScript.Shell").PopUp "Complete", 3, "Run Report"
Else
    CreateObject("WScript.Shell").PopUp "Completed with errors.", 3, "Run Report"
End If

And your done. Just double-click the file, or even put it into a windows scheduled task to run automatically. My functions return 0 if all goes well, so I evaluate my return value accordingly - you might use true or false instead, or not bother with return values at all.

This is exactly what I am looking for but I can't seem to get the code to work. For example, my 1st database is named C:\Users\Desktop\Database1.accdb and my macro is called Run_All_Queries. I tried changing your "G:\Accounting\ADPHrs.mdb" to "C:\Users\Desktop\Database1.accdb" and I changed your "Run("ADPDetailReport")" to "Run("Run_All_Queries"). I saved it as a .vbs to my desktop and then closed it and double clicked on it...A window titled Run Report came up and it said "Complete", but my macro didn't run. Any ideas?

 

[xenou]

I would put the code in an access or excel code module so we can look at the error message. Try this (in Access). I will also assume you have subs in your databases rather than functions so there is no return value. We can skip the ret = ... stuff in that case.

Sub Foo()

Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "C:\Users\Desktop\Database1.accdb", False
        .Run("Run_All_Queries")
    End With

End Sub

Sometimes I get nervous when you use the word macros. If you mean real Access Macros in the Macro module the are run differently. If by macros you just mean vba subs or functions then the above is what you want. But they should be in a standard module and be declared as Public Sub(...) or Public Function(...)

 

[basebalplayr33]

I would put the code in an access or excel code module so we can look at the error message. Try this (in Access). I will also assume you have subs in your databases rather than functions so there is no return value. We can skip the ret = ... stuff in that case.

Sub Foo()

Set objAccess = CreateObject("Access.Application")
    With objAccess
        .OpenCurrentDatabase "C:\Users\Desktop\Database1.accdb", False
        .Run("Run_All_Queries")
    End With

End Sub

Sometimes I get nervous when you use the word macros. If you mean real Access Macros in the Macro module the are run differently. If by macros you just mean vba subs or functions then the above is what you want. But they should be in a standard module and be declared as Public Sub(...) or Public Function(...)

Sorry I should have been more clear. Yes, I am using REAL ACCESS MACROS that I've created to run a certain set of queries in order. I did like you said and placed your above code into VBA in access and ran it. I got the following error messge.

Run-time error 2517:
Microsoft Access Cannot Find the Procedure 'Run_All_Queries'.


How would I have to alter this code given the fact that I am trying to run "real access macros"?

Thank you so much for your ongoing help.

~Mike

 

[xenou]

Okay, in that case you replace .Run("Run_All_Queries") with this:

 .DoCmd.RunMacro "Run_All_Queries"

That should do the trick this time, I think. Don't forget the dot at the front - inside a With-End block that will chain it to the Access object you created.