OpenDatabase Method

[Amileaux]

I am trying to run a procedure in another access database. I have the following code in the "active" database. The query and "PopBucket" procedures are in the "other" database. I got it to work - but would like to understand why the changes I've made have caused it not to work.

I got this to work this way: (very abbreviated)

Private Sub cmdRuntblBuildQuery_Click()
dim appAccess as New Access.Application
strFile as String
strFile = "C\filelocation"
appAccess.OpenCurrentDatabase strFile
appAccess.Visible = True
appAccess.Docmd.OpenQuery "qryMkTbl"
Call PopBucket(strFile)
rest of code
appAccess.close
appAccess = Nothing

Public Sub PopBucket(strFile)
Dim db as as Database
Dim rst as Recordset
Set db = DBEngine(0).OpenDatabase(strFile)
Set rst = db.OpenRecordset("tbl",dbOpenDynaset)
rest of code


Since I had to use the OpenDatabase method to get the PopBucket procedure to work, I decided to do the same with the first procedure - so I changed it as follows:

Private Sub cmdRuntblBuildQuery_Click()
Dim db as Database
Dim strFile as String
strFile = "C\filelocation"
Set db = DBEngine(0).OpenDatabase(strFile)
db.Docmd.openquery "qryMkTbl"
Call PopBucket(strFile)

When I compile I get "Method or data member not found" and the db.Docmd.openquery is highlighted. I know I have to refer to the database because if I remove the "db" in front of the DoCmd access says it can't find the query.

Question: Why does this "opendatabase" work in one procedure and not in the other? Thanks for your thoughts. Marie

 

[Alex McDevitt]

Hi,

It must be because you have set a new instance in the first procedure i.e. New Access.Application

Out of interest, why are you opening & calling external dB's, can you not group these dB's together?..

I am trying to run a procedure in another access database. I have the following code in the "active" database. The query and "PopBucket" procedures are in the "other" database. I got it to work - but would like to understand why the changes I've made have caused it not to work.

I got this to work this way: (very abbreviated)

Private Sub cmdRuntblBuildQuery_Click()
dim appAccess as New Access.Application
strFile as String
strFile = "C\filelocation"
appAccess.OpenCurrentDatabase strFile
appAccess.Visible = True
appAccess.Docmd.OpenQuery "qryMkTbl"
Call PopBucket(strFile)
rest of code
appAccess.close
appAccess = Nothing

Public Sub PopBucket(strFile)
Dim db as as Database
Dim rst as Recordset
Set db = DBEngine(0).OpenDatabase(strFile)
Set rst = db.OpenRecordset("tbl",dbOpenDynaset)
rest of code


Since I had to use the OpenDatabase method to get the PopBucket procedure to work, I decided to do the same with the first procedure - so I changed it as follows:

Private Sub cmdRuntblBuildQuery_Click()
Dim db as Database
Dim strFile as String
strFile = "C\filelocation"
Set db = DBEngine(0).OpenDatabase(strFile)
db.Docmd.openquery "qryMkTbl"
Call PopBucket(strFile)

When I compile I get "Method or data member not found" and the db.Docmd.openquery is highlighted. I know I have to refer to the database because if I remove the "db" in front of the DoCmd access says it can't find the query.

Question: Why does this "opendatabase" work in one procedure and not in the other? Thanks for your thoughts. Marie

 

[Amileaux]

Because I do not "own" the other database. I simply need some information from it, which I gather by creating the table and then I link back to the table. But I have found a work around - once I've built this table and linked to it, I can manipulate it with simply queries inside my own database. Although I still don't understand why it didn't work - I have achieved the same end result another way. Thanks for replying - Marie

 

[isladogs]

Is this meant to be a file or folder location?

strFile = "C\filelocation"

As written it won't work. Perhaps this ....

strFile = "C:\filelocation"

 

[MarkK]

...
db.Docmd.openquery "qryMkTbl"
...

This code fails with that error because DoCmd is not a member of DAO.Database, DoCmd is a member of Access.Application. To execute a query in a DAO.Database, you can use the execute method, but the DAO.Database also has a QueryDefs collection, so you could run a named query like...

db.QueryDefs("qryMkTbl").Execute

...using the execute method (or OpenRecordset) of the QueryDef itself. No need to invoke it via DoCmd if you are not using an Access.Application.

Keep in mind that you can also specify the path to a table directly in your SQL, and avoid having to open an Access.Application or a DAO.Database in your code. Consider this code, notably the SQL...

Sub Test0239857924572934()
    Const SQL As String = _
        "SELECT Count(*) " & _
        "FROM YourTable IN 'C:\FileLocation\YourDatabase.accdb'"
    
    With CurrentDb.CreateQueryDef("", SQL)
        Debug.Print .OpenRecordset.Fields(0)
        .Close
    End With
End Sub

See how that code prints the record count from a table in different database directly from a path specified in the SQL?
hth
Mark