Open a macro in a databese2 in database1

[mansied]

Hello
I have two databases,1and 2,I want to run a macro from database 2 in database 1 :
I have the below code, but not work and no error .can you hint where is the issue?

Public Function run_main()
Call CallMacro()

End Function

Public Sub CallMacro()
'
Dim strDbName, strMacro
Dim objApp
'
strDbName = "*****.accdb"
strMacro = "Main"
'
Set objApp = CreateObject("Access.Application")
'
objApp.OpenCurrentDatabase strDbName
'
' Run Macro:
'
objApp.DoCmd.RunMacro strMacro
'
objApp.Quit
Set objApp = Nothing
'
End Sub

 

[Isaac]

Maybe it did work. What does it do?

 

[mansied]

from database 1 It should open database2 and run macro "main" on it.
it doesn't open database2.

 

[isladogs]

Change OpenCurrentDatabase to OpenDatabase

 

[Isaac]

Excellent @isladogs good catch!
I think OpenCurrentDatabase would be more appropriate if it were being used as a method to Set a database variable?

 

[mansied]

I changed it and the result:

 

[Gasman]

Wouldn't you need the full path of the dB file anyway?

 

[isladogs]

Wouldn't you need the full path of the dB file anyway?

Good point. Yes you would need the full path
You might also be able to open direct to the required macro using the /macro switch

 

[Isaac]

I use OpenCurrentDatabase with no problem

Solved - Open External Access Database | Access World Forums (access-programmers.co.uk)

Here is an actual VBScript file that I use currently (scheduled via Windows Task Scheduler) - I've only made changes to sanitize/redact, but it's actual code in-use.

dim accessapp, db

set accessapp=createobject("access.application")
accessapp.visible=true
accessapp.opencurrentdatabase("\\server\folder\folder\DBName.accdb")
accessapp.Run "NameOfSUB"
accessapp.closecurrentdatabase
accessapp.quit

 

[Gasman]

So O/P is not using () and access thinks xxx.accdb is a property or method?

 

[Isaac]

So O/P is not using () and access thinks xxx.accdb is a property or method?

Yeah, good point ... Maybe that's the problem.

I don't know what I was thinking earlier, my own code proved my comment wrong. My memory is very unreliable.

 

[isladogs]

I use OpenCurrentDatabase with no problem

Solved - Open External Access Database | Access World Forums (access-programmers.co.uk)

Here is an actual VBScript file that I use currently (scheduled via Windows Task Scheduler) - I've only made changes to sanitize/redact, but it's actual code in-use.

dim accessapp, db

set accessapp=createobject("access.application")
accessapp.visible=true
accessapp.opencurrentdatabase("\\server\folder\folder\DBName.accdb")
accessapp.Run "NameOfSUB"
accessapp.closecurrentdatabase
accessapp.quit

So do I. My only comments on your code are not defining your variables explicitly (so they are variants) and it may help that you made Access visible before using OpenCurrentDatabase (though I don't in the code below as its already open)

FWIW here is some generic code I use to open an external database from Access in different ways...hence much has been commented out

Public Function RunExternalDatabase() As Boolean
  
    Dim app As Access.Application, strPath As String
  
    'Start a new MSAccess application
    Set app = New Access.Application
  
    'Open the remote database, then close the current (or remote) database
    With app
        'Syntax: .OpenCurrentDatabase(filepath, Exclusive, strPassword) - the last 2 items are optional
      
        strPath = "C:\Programs\MendipDataSystems\JATFA\JATFA.accdb" 'replace with your file path
       ' strPath = "C:\tmp\DB2.accdb" 'full file path to your database
         .OpenCurrentDatabase strPath, True 'no db password
     '   .OpenCurrentDatabase strPath, True, "password" 'for use if password exists
  
    '    .DoCmd.RunMacro "mcrRefreshPersTable" 'run your macro
       ' .CloseCurrentDatabase 'closes external database as that is current
    End With
  
    'Quit the spawned app - DISABLED as not wanted here
    'app.Quit acQuitSaveNone
    'Set app = Nothing
  
    'Quit the current app
    Application.Quit acQuitSaveNone
  
End Function

 

[Pat Hartman]

What does the macro do? If it doesn't open a form and you don't need any response, you can create a batch file to open the database with the /m switch to run the specified macro and run the batch file.

 

[Isaac]

My only comments on your code are not defining your variables explicitly (so they are variants)

keep in mind what i posted is being run as VBScript.