[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.