Calling Access subroutine from another program (early vs late binding)

[waubain]

I am trying to call a Access 2010 subroutine from Reflection UNIX and OpenVMS. I have a fairly lengthly program that uses early binding with DAO database and recordsets and for now would like keep it as early binding, but I now need to call a sub in Access. The problem is I get an error 7952 "You made an illegal function call" when using early binding. I can get it to work with late binding.

What am I missing?

Access subroutine(starting simple)

Public Sub HelloWorld()
    MsgBox "Hello World"
End Sub

Early binding:

Sub RunAccessSubEarlyBinding()

    Dim objAccess As Access.Application
    Dim db As DAO.Database
    Dim dbPath As String
    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Inpatient\TeamRounds.accdb"
    Set objAccess = New Access.Application
    Set db = objAccess.DBEngine.OpenDatabase(dbPath)
    
         objAccess.Run "HelloWorld"     '<<Get Error 7952 "You made an illegal function call">>
 
End Sub

Late Binding

Sub RunAccessSubLateBinding()

    Dim objAccess As Access.Application
    Dim dbPath As String
    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Inpatient\TeamRounds.accdb"
    
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase dbPath

    objAccess.Run "HelloWorld"

End Sub

Thank you for any suggestions.



You don't know what you don't know...

 

[strongm]

Your example of late binding isn't actually late binding.

 

[Andrzejek]

It would be if you would do this change:

Sub RunAccessSubLateBinding()

    Dim objAccess As [s]Access.Application[/s] [red]Object[/red]
    Dim dbPath As String
    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Inpatient\TeamRounds.accdb"
    
    Set objAccess = CreateObject("Access.Application")
    objAccess.OpenCurrentDatabase dbPath

    objAccess.Run "HelloWorld"

End Sub

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[waubain]

Thanks strongm and Andy. Sorry for the late response, I have been away. I understand, the error, to much cutting and pasting to show my example, yes it should have been an Object.

So the question come back to my original question of why won't objAccess.Run work in the first example if both pieces of vba were early binding?

Sub RunAccessSubEarlyBinding()

    Dim objAccess As Access.Application
    Dim db As DAO.Database
    Dim dbPath As String
    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Inpatient\TeamRounds.accdb"
    Set objAccess = New Access.Application
    Set db = objAccess.DBEngine.OpenDatabase(dbPath)
    
         objAccess.Run "HelloWorld"     '<<Get Error 7952 "You made an illegal function call">>
 
End Sub

You don't know what you don't know...

 

[PHV]

What happens if you replace this:
Set db = objAccess.DBEngine.OpenDatabase(dbPath)
with this:
objAccess.OpenCurrentDatabase dbPath

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[waubain]

Thanks everyone. Whether it is correct, this works. The part I needed was set the current database so I could open a recordset.
What you all knew I have to learn is the objAccess.OpenCurrentDatabase dbPath is required to run commands for another program. to set the database I have to use Set db = objAccess.CurrentDbIn a simplified example this worked.

Sub RunAccessFromReflection()

    Dim objAccess As Access.Application
    Dim db As Database
    Dim dbPath As String
    dbPath = "S:\Pharmacy General\Databases Automation\Databases\Inpatient\TeamRounds.accdb"
    Set objAccess = New Access.Application
    objAccess.OpenCurrentDatabase dbPath
    Set db = objAccess.CurrentDb

         objAccess.Run "HelloWorld"
'tidy up
    db.Close
    objAccess.Quit
    Set db = Nothing
    Set objAcess = Nothing
End Sub

What is still unclear and I am testing, is if "HelloWorld" is a sub that needs to work with the same database and I need to open a recordset do I only have to
Set db = Currentdb.
Do I need to db.Close or will that also close the db in my Reflection session?

Sub HelloWorld()

    Dim db As Database
    Dim rst as Recordset
    Dim strSQL as String

    strSQL = "SELECT...;"
    Set db = CurrentDb
    Set rst = db.OpenRecordSet(strSQL)

   ...work with recordset

   rst.Close
   db.Close ???
   Set rst = Nothing
   Set db = Nothing

End Sub

Thank you.

You don't know what you don't know...