Ms Access VBA: How do I run a Sub Procedure that is stored in one DB on another DB?

Question

I have a DB (we'll call it DB1) with a VBA procedure that loops through all sub folders in a file path and looks for other DB files with a certain string in the file path. When it finds a DB with that string (we'll call it DB2), I need to run a Sub Procedure (that is stored in DB1) on DB2. I have the code down to the point where it Opens DB2, but I can't figure out how to run the macro from DB1 on it. I tried the appAccess.Run method first and then I tried the docmd.runmacro method but neither worked.

Sub RunExternalProcedure(strFilePath)
    Dim appAccess As Access.Application
    Set appAccess = CreateObject("Access.Application")
    appAccess.OpenCurrentDatabase strFilePath, False
    'Run Sub procedure. 'appAccess.Run "TruncateTables"
    DoCmd.RunMacro "TruncateTables"
    Set appAccess = Nothing
End Sub

Adapt your TruncateTables procedure so that it operates on the database which is identified by strFilePath — HansUp
– HansUp, Commented Aug 29, 2019 at 20:25

Doug Coats · Accepted Answer · 2019-08-29 20:00:55Z

3

The problem is that (most likely) you are trying to run a procedure that isnt there. If i were you I would either:

Learn to check for existence of an object and on "not found" use the DoCmd.TransferDatabase command to move the module and then execute it.

https://learn.microsoft.com/en-us/office/vba/api/access.docmd.transferdatabase
Make the file with the module you want to run a reference in the references. Then the access file without it can call it remotely.

Access 2010: linked database, reference, or add-in?

I would probably do #2, because then thats get you into a mindset of having a utility function DB that other access applications use for commonly used things (instead of copy/pasting the same funciton/sub everywhere).

edited Aug 29, 2019 at 20:00

answered Aug 29, 2019 at 18:26

Doug Coats

7,2179 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chuck0185 Over a year ago

Thanks for the response Doug. I had never heard of this method until now, sounds like something that could be extremely helpful for the work I do. I couldn't find clear guidance on the simplest way to use a .accdb file as a reference in another DB (and also to do so programmatically for multiple files). There is a lot of info there but also a lot of links/wormholes that get off the topic. Any further guidance would be much appreciated. Thanks again Doug!

Doug Coats Over a year ago

@Chuck0185 See edited answer. It really is this simple.

Chuck0185 Over a year ago

Hey Doug, so I do know how to add references manually. The issue is that I have hundreds of files that I need to run this procedure on. So I would need to add the reference through the VBA itself. Is that possible?

Doug Coats Over a year ago

@Chuck0185 Yes, check this : stackoverflow.com/questions/9879825/…

Collectives™ on Stack Overflow

Ms Access VBA: How do I run a Sub Procedure that is stored in one DB on another DB?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related