Run Access VBA code from .bat file (1 Viewer)

Ben_Entrew

Registered User.
Local time
Today, 08:03
Joined
Dec 3, 2013
Messages
177
Hi guys,

can someone help me out?

Following issue:

Wanna open all Access databases under one specific folder and run the same VBA code. The code is ready, the only thing for me is to find out how to start Access from a bat file and run the same code for all databases.
The background is that I want to replace directory names for all linked tables and in the underlying VBA codes of all Access databases.

Has someone an idea?

Thanks.

Regards,
Ben
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:03
Joined
Oct 29, 2018
Messages
21,358
Hi Ben. This should be easy. You could create a macro to call the function in each database file. You can then start each file using the command line switch /x to execute the macro. Hope it helps...
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:03
Joined
Sep 21, 2011
Messages
14,044
Pat Hartman has supplied a db on here to relink multiple FE to BE.
I can't find it on the site, but I downloaded it in case it would be of use to me some time.

HTH
 

Attachments

  • RelinkMultipleBEwithValidTableTest.accdb
    608 KB · Views: 146

Ben_Entrew

Registered User.
Local time
Today, 08:03
Joined
Dec 3, 2013
Messages
177
Hi, thanks for the suggestion. Unfortunately there are 100s of databases. Somehow I would like to open them up subsequently and run the same code.
I tried to start at first one database running a bat file with:

Start "folder/XX.mdb"

Didn't work out.



TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
 

Insane_ai

Not Really an A.I.
Local time
Today, 11:03
Joined
Mar 20, 2009
Messages
264
your batch file would look something like this:

"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /x MacroName
Code:
"C:\Program Files (x86)\Microsoft Office\root\Office16\msaccess.exe" "C:\Test\MyDB.accdb" /x MyMacro
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:03
Joined
Oct 29, 2018
Messages
21,358
Hi, thanks for the suggestion. Unfortunately there are 100s of databases. Somehow I would like to open them up subsequently and run the same code.
I tried to start at first one database running a bat file with:

Start "folder/XX.mdb"

Didn't work out.

TA-1012 cihazımdan Tapatalk kullanılarak gönderildi
Hi. I don't seen in your batch file where you tried to use the /x macroname switch. What you're showing us is the name of your database is XX and it's in a folder called "folder/." If that's what you actually did, then I don't expect it to work because it's not what I suggested. I was expecting your to do something more along the lines of:
Code:
"C:\FolderPathTo\MSACCESS.EXE" "C:\FolerPathTo\YourDatabase.mdb" /x macroname
 

Insane_ai

Not Really an A.I.
Local time
Today, 11:03
Joined
Mar 20, 2009
Messages
264
Please post the code you used so we can help you better.
 

Ben_Entrew

Registered User.
Local time
Today, 08:03
Joined
Dec 3, 2013
Messages
177
Hi Lightwave,

my playground is a little restricted here.

Any idea how to start a module in another Access database?

Thanks.

Rgds,
Ben
 

Lightwave

Ad astra
Local time
Today, 15:03
Joined
Sep 27, 2004
Messages
1,521
Sorry I would be in the same situation as you if I had those limitations.

Posts script : I would have another go as Gasman states below
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 15:03
Joined
Sep 21, 2011
Messages
14,044
That link you posted showed you how.?

What could you not get to work.?

Hi Lightwave,

my playground is a little restricted here.

Any idea how to start a module in another Access database?

Thanks.

Rgds,
Ben
 

Ben_Entrew

Registered User.
Local time
Today, 08:03
Joined
Dec 3, 2013
Messages
177
I get the error message:

Run-Time error 2517

Procedure Test couldn't be found by Microsoft Access.


I have a Sub routine called Public Sub Test().
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:03
Joined
Sep 21, 2011
Messages
14,044
Show your code then, as I have just created these and it works fine.

Calling DB
Code:
Sub TestSubInAnotherDB()
Dim appAccess As Access.Application
 
'Create instance of Access Application object.
Set appAccess = CreateObject("Access.Application")
 
'Open Test Database in Microsoft Access window.
appAccess.OpenCurrentDatabase "C:\Temp\db\Test.accdb", False
 
'Run Sub procedure.
appAccess.Run "CountTables"
 
Set appAccess = Nothing
End Sub

Called DB
Code:
Sub CountTables()
Dim iTblCount As Integer
Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs
    Debug.Print tbl.Name
    iTblCount = iTblCount + 1
Next
MsgBox (iTblCount & " tables in " & CurrentDb.Name)
End Sub

Hoever, I would have thought you would want a db to open all the others and run that code from your other thread against the db opened via the code.?

Could you perhaps prefix the objects with the correct object prefix, like appaccess. ?

I must admit, way beyound my skill level, but curious, just the same.:)

HTH
 

Micron

AWF VIP
Local time
Today, 11:03
Joined
Oct 20, 2018
Messages
3,476
Some things I'm not seeing addressed:
Procedure Test couldn't be found by Microsoft Access.
I have a Sub routine called Public Sub Test().
Is it in a standard module, or in a form/report module. AFAIK, it can't be on a form/report module - unless maybe that form/report is open. I've never tested.
You are not saying that NO vb code will run in any db so I don't understand the notion that you are "stuck".

Take this one step at a time and get one db to open another (perhaps visible so that you don't have to go to Task Manager to figure out if it worked). Suggest that you then code to have the opened db1 close after say 10 seconds (timer event). Have the "master" db test for db1 being open (Get Object could suffice) and open db2 when db1 is not available. Otherwise, any code you attempt to run from a master db to open "100" other db's will likely crash the system as it won't wait for one to close before it opens another. Unless anyone knows how to get db1 to return a value to the master ("I'm closing"), I have no other idea than to test for it being open (Get Object). If you can't do this part, I'd say getting code to run in db1 from another db might be pointless. Your current issue may be that the code isn't in a standard module. And are you saying that your company won't allow an autoexec to run in Access either? That would be the simplest way for each opened db to execute code, but might require that you open them with a switch so that code doesn't automatically run if a person opens any of these 100 db's. That would require use of the Command property in autoexec run code for each db.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:03
Joined
Sep 21, 2011
Messages
14,044
I think you are looking at this in the wrong way.
The code you want to run is in a database that remains open all through the procedure, let's call it Maint.accdb

That opens each of the dbs that need to be corrected in turn.
You then pass that object to your replace routine so that it works on the opened db and not the Maint db.?

This should be doable as I have just changed the called procedure to give me the table details of the calling db.

Code:
Sub CountTables(db As Database)
Dim iTblCount As Integer
Dim tbl As TableDef

For Each tbl In db.TableDefs
    Debug.Print tbl.Name
    iTblCount = iTblCount + 1
Next
MsgBox (iTblCount & " tables in " & db.Name)
End Sub

HTH
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:03
Joined
Feb 28, 2001
Messages
26,999
I just learned that it's not allowed to use vb scripts and batch files in our company.

Invariably this comes from the IT department, not management. Your solution is to talk to the person who wants this stuff updated and have HIM talk to IT about how their policy will end up costing the company time and money. Then the boss can make it IT's problem and suddenly there will be an exception.

Except that if this ISN'T coming from some boss wanting something, but rather YOU wanting to do it as a matter of code improvement, that motivational method won't work.

The solution I might try is:

1. Create a new empty DB
2. Write code in a module that uses FileSystemObject calls to find each file and decide whether it is a database. (File types of .MDB, .ACCDB, but be sure you DON'T diddle with the special database you are writing for this purpose!)
3. For each qualified file, open the database.
4. For each table in this targeted database, look at the connect string. If it is pointing to the file you opened, it is a local table. Skip it. If it is pointing externally, it might be a candidate for your actions. Update it / relink it.
5. Close the targeted database. This is necessary because if you don't, you have a limit to the number of databases you can have open at once. From your description, you'll crack that limit easily.
6. Of course, keep statistics so that you will be able to list every DB and every table you touched. It'll make a nice report.
 

Insane_ai

Not Really an A.I.
Local time
Today, 11:03
Joined
Mar 20, 2009
Messages
264
Taking from GasMan's example, Here is what the code should look like when you run it from a single database.

Code:
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
 

Users who are viewing this thread

Top Bottom