[RESOLVED] Use DAO to query a database

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

VB Code:

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

you can run the query with just sql

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

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?

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.

VB Code:

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.