Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-05-2019, 05:06 AM   #16
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Run Access VBA code from .bat file

Some things I'm not seeing addressed:
Quote:
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.

__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 09-05-2019, 05:09 AM   #17
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Run Access VBA code from .bat file

accidentally double posted
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 09-05-2019, 05:22 AM   #18
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Access VBA code from .bat file

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

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-05-2019, 06:17 AM   #19
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,354
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Run Access VBA code from .bat file

Quote:
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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 09-05-2019, 06:35 AM   #20
Insane_ai
Not Really an A.I.
 
Join Date: Mar 2009
Location: Cleveland, OH USA
Posts: 264
Thanks: 15
Thanked 27 Times in 24 Posts
Insane_ai is on a distinguished road
Re: Run Access VBA code from .bat file

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
__________________
I actually know a few things, just not all of them.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Insane_ai is offline   Reply With Quote
Old 09-05-2019, 04:43 PM   #21
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Run Access VBA code from .bat file

Quote:
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.
Micron is offline   Reply With Quote
Old 09-05-2019, 10:28 PM   #22
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Run Access VBA code from .bat file

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

Ben_Entrew is offline   Reply With Quote
Old 09-05-2019, 10:55 PM   #23
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Access VBA code from .bat file

Quote:
Originally Posted by Micron View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-05-2019, 10:57 PM   #24
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Access VBA code from .bat file

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?

Quote:
Originally Posted by Ben_Entrew View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by Gasman; 09-05-2019 at 11:07 PM.
Gasman is offline   Reply With Quote
Old 09-05-2019, 11:04 PM   #25
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Access VBA code from .bat file

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

Code:
Call Countables(appcess)
Code:
Public Countables (app as Access.Database)
----------------------------

Quote:
Originally Posted by Insane_ai View Post
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
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-05-2019, 11:49 PM   #26
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,082
Thanks: 421
Thanked 751 Times in 729 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Run Access VBA code from .bat file

Ben,

You appear to have deleted this post

Quote:
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.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 09-09-2019, 11:45 AM   #27
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Run Access VBA code from .bat file

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.
Ben_Entrew is offline   Reply With Quote
Old 09-09-2019, 01:16 PM   #28
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,607
Thanks: 50
Thanked 1,052 Times in 1,033 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Run Access VBA code from .bat file

Quote:
Originally Posted by Ben_Entrew View Post
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?
Attached Files
File Type: zip DB1.zip (47.5 KB, 5 views)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-09-2019, 02:42 PM   #29
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,149
Thanks: 15
Thanked 1,572 Times in 1,494 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Run Access VBA code from .bat file

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-09-2019, 11:26 PM   #30
Ben_Entrew
Newly Registered User
 
Join Date: Dec 2013
Posts: 166
Thanks: 19
Thanked 0 Times in 0 Posts
Ben_Entrew is on a distinguished road
Re: Run Access VBA code from .bat file

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

Ben_Entrew is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import Excel File into Access VBA code Sim0n Modules & VBA 2 06-06-2014 12:15 AM
Access Code & Module 32bit to 64bit system - Browse For File Code Thorope Modules & VBA 2 04-04-2011 09:13 AM
Access 2002-2003 file format--code question lmcc007 Modules & VBA 16 04-12-2009 01:02 AM
Code for formating a file in access using excel Help. tingler0 Visual Basic 0 10-29-2007 08:00 AM
Possible to execute an .exe file from within Access through VB Code everblue Forms 2 08-31-2002 08:55 PM




All times are GMT -8. The time now is 06:37 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World