[RESOLVED] Use DAO to query a database

[Ranthalion]

Hi,

I am using MS Access 2003. I am trying to query a table in another database, but can't seem to get it... This is my first time trying to run a query against any database other than the one I'm working in. I've read through some of the Object Viewer documentation, but I can't seem to get a good overview of how it should all come together.

When I try to run the following code I receive an error:
Operation is not supported for this type of object

Public Function QueryOtherDB()
    Dim dbc As DAO.Workspace
    Dim rsInfo As DAO.Recordset
    Dim conn As DAO.Connection
    
    'This line throws the error
    Set conn = OpenConnection("myConn", , False, "ODBC;DATABASE=Q:\path\myDB.mdb")
    Set rsInfo = conn.OpenRecordset("SELECT * FROM tblErrorLog")
    
    While Not rsInfo.EOF
        MsgBox rsInfo.Fields(0) & " " & rsInfo.Fields(1) & " " & rsInfo.Fields(2)
    Wend
    
    rsInfo.Close
    conn.Close
    dbc.Close
End Function

[Static]

you can run the query with just sql

SELECT * FROM TABLE IN 'Q:\path\myDB.mdb'

[Ranthalion]

Thanks. How would I fill the recordset with the results? I can't just say rsInfo.OpenRecordset("SELECT * FROM tblErrorLog in 'Q:\path\file.mdb'"). Is there another object I would use to fill it?

[Ranthalion]

Alright!
I found out that I can use DoCmd.RunSQL and use the IN clause to specify the database file if I am running an action query. To run the select query I am using the following.

Public Function QueryOtherDB()
    Dim dbc As Database
    Dim rsInfo As Recordset
   
    Set dbc = DBEngine.Workspaces(0).OpenDatabase("q:\path\file.mdb")
    Set rsInfo = dbc.OpenRecordset("SELECT * FROM tblErrorLog")
   
    Do Until rsInfo.EOF
        MsgBox rsInfo.Fields(0) & " " & rsInfo.Fields(1) & " " & rsInfo.Fields(2)
        rsInfo.MoveNext
    Loop
    
    rsInfo.Close
    dbc.Close
    
End Function

Thanks again for your help. I have marked this thread resolved.