Run Access VBA code from .bat file

[Ben_Entrew]

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]

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]

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

 

[Ben_Entrew]

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]

your batch file would look something like this:

"<path>\msaccess.exe" "<path>\MyDatabase.mdb" /x MacroName

"C:\Program Files (x86)\Microsoft Office\root\Office16\msaccess.exe" "C:\Test\MyDB.accdb" /x MyMacro

 

[Insane_ai]

This may be of assistance:

Windows batch script - How to filter files with defined extension

https://stackoverflow.com/questions...pt-how-to-filter-files-with-defined-extension

 

[theDBguy]

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:

"C:\FolderPathTo\MSACCESS.EXE" "C:\FolerPathTo\YourDatabase.mdb" /x macroname

 

[Ben_Entrew]

Hi All,

I just learned that it's not allowed to use vb scripts and batch files in our company.

How can I open another database and run the original module code there?

I tried this,without success:

https://bytes.com/topic/access/answers/900155-how-run-procedure-database-code-another-db


Thanks.

Regards,
Ben

 

[Insane_ai]

Please post the code you used so we can help you better.

 

[Lightwave]

I just learned that it's not allowed to use vb scripts and batch files in our company.

Looks like you might be stuck.
If you can't run code you can't run code...

For anyone else
https://rounduptheusualsuspects.org/?p=1386

 

[Ben_Entrew]

Hi Lightwave,

my playground is a little restricted here.

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

Thanks.

Rgds,
Ben

 

[Lightwave]

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

 

[Gasman]

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]

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]

Show your code then, as I have just created these and it works fine.

Calling DB

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

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

 

[Deleted member 147267]

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.

 

[Deleted member 147267]

accidentally double posted

 

[Gasman]

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.

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]

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]

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