Run Access VBA code from .bat file

[Deleted member 147267]

I think you are looking at this in the wrong way.

If that comment was meant for me...No - I understand it exactly as you reiterated it. That's what my suggestion was about.

 

[Ben_Entrew]

Hello Gasman,

unfortunately I get the same error message:

Run-Time error 2517

Procedure Test couldn't be found by Microsoft Access.

I'm getting desparate, shouldn't be so difficult to open another database and run a program

 

[Gasman]

If that comment was meant for me...No - I understand it exactly as you reiterated it. That's what my suggestion was about.

No Micron, it was for the o/p

 

[Gasman]

It is not. I had to copy that code from that link of yours and write a little of my own, but I managed it, so I am puzzled as to what you are doing wrong.

Can you show us your code in exactly the same manner I did in post 15?

Hello Gasman,

unfortunately I get the same error message:

Run-Time error 2517

Procedure Test couldn't be found by Microsoft Access.

I'm getting desparate, shouldn't be so difficult to open another database and run a program

 

[Gasman]

Wouldn't that try and run the procedure in the newly opened db?

I beleive you need to pass the newly opened db as an object to the procedure which is in the Maint db (the db that remains open all through this procedure and has that code) , so that the procedure runs against each individual db.?

Call Countables(appcess)

Public Countables (app as Access.Database)

----------------------------

Taking from GasMan's example, Here is what the code should look like when you run it from a single database.

Sub TestSubInAnotherDB()
Dim appAccess As Access.Application
Dim strFileName As String
Dim strDBFolder As String
     
      
      strDBFolder = "c:\test\db\"  'change the path accordingly
      strFileName = Dir(strDBFolder)
 
      Do While strFileName <> ""
           If strFileName Like "*.MDB" Then 'Or *.ACCDB
                'Create instance of Access Application object.
                 Set appAccess = CreateObject("Access.Application")
                'Open Test Database in Microsoft Access window.
                 appAccess.OpenCurrentDatabase strFileName, False
  
                'Run Sub procedure.
                appAccess.Run "CountTables"
  
                Set appAccess = Nothing
           End IF
      Loop
End Sub

 

[Gasman]

Ben,

You appear to have deleted this post

Hi Gasman,

unfortunately it didn't work out.

I can run the procedure with Application.Run on the same database, but it doesn't apply the code on the other database.

Gettin desperate here
***************

Do you mean you cannot run code in the other db?

As I have mentioned I do not think this is what you want to do.
I believe you need to run code in your same open database but have it work on each newly opened database.

If I have got this all wrong and have just confused you even more, then I apologise, but I really think you are going about this the wrong way.
For you run some code in the newly opened database it would need to be there in the first place, and my understanding is this code is going to change the file paths?

My approach is some code to change the filepaths, but that it runs against each opened DB, except what I call the Maint db, otherwise it would affect that code as well.?

I'm off to work now, so out for most of the day. Good luck with it.

 

[Ben_Entrew]

Thank you so far.

Let me describe my issue.

I got a database with a procedure I want to run in another database.

It still doesn't work. I don't know the issue. I'm using Access 2013,maybe that's the issue???

Can I open a table in the new opened database, as a simple test?

Thank you.

 

[theDBguy]

Thank you so far.

Let me describe my issue.

I got a database with a procedure I want to run in another database.

It still doesn't work. I don't know the issue. I'm using Access 2013,maybe that's the issue???

Can I open a table in the new opened database, as a simple test?

Thank you.

Hi. Not sure I understand what you're trying to do, but I tried a little experiment. Please download the attached file and extract its contents into a Trusted Folder. Open DB1 and make sure you can execute the function in it called Test(), which merely opens a Message Box. Close DB and try the same thing with DB2. What DB2 does is basically execute the Test() function in DB1. In my tests, the message box opens up behind the window I had for DB2, so you may have to use the Taskbar to find it. I didn't create any form to execute the code. I just used the Immediate Window. Is this what you mean?

 

[Pat Hartman]

The code in the database sample of mine that Gasman posted earlier shows you how to relink tables. You would need to extract that code and put it into a loop that opens different databases. The loop would read through a specific directory (and subdirectories if you want) and for each Access database, set a db reference to that specific file name so the tables you reference with db.Tabledefs will be in THAT database rather than the database which is running the code.

 

[Ben_Entrew]

Thank you Pat Hartman,

the code I want to execute contains some more stuff not only changing links.

I've checked the databases provided by thedbGuy, thanks for this.

But the code is stored under the new opened database db1.

I have the code in the original database and want to execute this program in the new opened database. Can I copy the sub module to the new database?

Regards,
Ben

 

[Gasman]

I *thought* all this was linked to another post of yours where you need to alter hardoded file paths in modules on a lot of dbs?

Whilst the link that you first posted shows one how to run code in another DB, that simple code runs in the called DB and applies whatever it does to the called db.

I *believe* you need a db that will call all your other dbs that have these hardcoded file paths. This db will have the code to alter the file paths BUT will run it against each called db, not itself.?

If that is not the case, then I apologise for muddying the waters. However if that is not the case, I am having great difficulty trying to think as to why you cannot use that simple code in your initial link as a test to running code in another db?

The code I created as a test displays the tables in a db. That way I could see whether the code was running against the called db or the calling db, as the tables within are completely different.

Have you tried using the debugger and executing each line one by one with F8.?

 

[theDBguy]

I've checked the databases provided by thedbGuy, thanks for this.

But the code is stored under the new opened database db1.

I have the code in the original database and want to execute this program in the new opened database. Can I copy the sub module to the new database?

Regards,
Ben

Hi Ben. I knew I could be thinking of it backwards. So, can you post the code you want to execute against the other databases or at least give us an idea what you're trying to do? Maybe we can come up with another way to do it. Cheers!

 

[The_Doc_Man]

The way to execute code already in another DB (presuming it to be compiled) is to qualify the module, as CALL DB1.MySpecialSubroutine(...). However, if you wanted to insert code into another DB, there IS such a thing as creating a module and inserting lines of code into it. After all, an event wizard can insert code into a module. There's no "special trick" involved. You just pick a starting line where you want to start inserting and then it is merely a Module.InsertLine "text" operation. The trick is picking the starting line for insertion.

I've never tried to actually compile another DB, but you would have to compile it if you wanted to execute code you just inserted externally. I suppose if you could then launch that external DB and fire up some macro to get it to run some code, it would auto-compile - but you have to hope you have no compilation errors.

However, there is a logic issue here and I'm struggling to imagine who is doing what to whom? (Sounds like the punch line of an old limerick.) Gasman and theDBguy (both of whom are quite good) are scratching their heads over this and I can see why.

In the final analysis, if the code requires special attention in the context of the other DB, you probably CAN'T execute it from the original database "on behalf of" the other DB. You could execute it to take effect on your target DB from the original DB but it would still be in the context of that original DB's workspace. You could direct it to do its thing in that target DB by properly qualifying everything. At the end of all that, if you have to do a remote launch for context purposes, if it isn't properly self-contained and self-directing, it ain't gonna fly.

So, from my comments and the comments of my colleagues, I hope you can understand the focus of our confusion and perhaps clarify your intent.

 

[Ben_Entrew]

Thank you all for the help.
I've found a hot fix.
The sub is copied to the new opened database, runs it there and deleted after the run.
Now it works.

Thank you.

Kind regards,
Ben

 

[theDBguy]

Thank you all for the help.
I've found a hot fix.
The sub is copied to the new opened database, runs it there and deleted after the run.
Now it works.

Thank you.

Kind regards,
Ben

Hi Ben. Congratulations! Glad to hear you got it sorted out. Good luck with your project.