I am trying to execute a VBA function in an Access DB from an Excel VBA module.

Now I can happily do this if I use the ‘access’ object, but this opens the DB which means when the code closes the DB my ‘Compact on Close’ kicks in which I dont want to happen.

I can run a query on the DB from Excel VBA using the code below which creates a connection as opposed to opening the db, but I just can’t find anything on what I need to amend so that the DB connection executes a VBA function instead of a query

Any ideas?

Public Function RunQuery() As Object
Dim objCmd As Object 
Dim objRec As Object

strQueryname = "CheckFields"
strDBPath = "db pathdb.accdb"
Set objCmd = CreateObject ("ADODB. Command")

With objcmd
    .ActiveConnection = "Provider=Microsoft .ACE.OLEDB. 12.0;" &
                       "Data Source=" & strDBPath & ";"
                       "Jet OLEDB: Engine Type=5;" &
                       "Persist Security Info=False;"
    .CommandText = strQueryname
     Set objRec = .Execute (Option:=4)
End With
Set RunQuery = objRec
End Function