0

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
braX
  • 11,506
  • 5
  • 20
  • 33
Chuck0185
  • 531
  • 3
  • 15
  • 36
  • Adapt your `TruncateTables` procedure so that it operates on the database which is identified by `strFilePath` – HansUp Aug 29 '19 at 20:25

1 Answers1

3

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

  1. 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

  2. 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).

enter image description here

Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • 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! – Chuck0185 Aug 29 '19 at 19:38
  • @Chuck0185 See edited answer. It really is this simple. – Doug Coats Aug 29 '19 at 20:01
  • 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? – Chuck0185 Aug 29 '19 at 20:29
  • @Chuck0185 Yes, check this : https://stackoverflow.com/questions/9879825/how-to-add-a-reference-programmatically – Doug Coats Aug 29 '19 at 20:32