Correct code for opening a recordset from another database?

[Access9001]

Can anyone write a quick sub that basically opens a recordset from another database, iterates through them all, then closes correctly? My code isn't working properly:

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = OpenDatabase("\\path\otherdb.mdb")
Set rst = db.OpenRecordset("SELECT * FROM Table_Name")

Do While Not rs.EOF
MsgBox rs("Col_name")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing



I'd also like to use ADODB

 

[Access9001]

Trying this, still no dice:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.jet.oledb.4.0;Data source=""\\path\otherdb.mdb"""

rs.Open "SELECT * FROM Table_Name", conn, adOpenDynamic, adLockOptimistic



Do While Not rs.EOF
MsgBox rs("col_name")
rs.MoveNext
Loop

rs.Close
Set rs = Nothing


Maybe I should stick with DAO? What should I do?

 

[Access9001]

When I try this, it says MS jet Engine can't find the input table/query even though it exists:

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = OpenDatabase("\\path\otherdb.mdb")
Set rs = db.OpenRecordset("SELECT * from Table_Name", dbOpenDynaset)




Do While Not rs.EOF
MsgBox rs("col_name")
rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[Access9001]

I think the syntax is right (it opens up other tables fine within that db) but when I open the one I want, I get "undefined function rdate in expression" even though when I open up the table with the same SQL syntax in the db itself, it works fine?

 

[Access9001]

Nevermind. Figured it out. Thank... me.

 

[SOS]

I wasn't able to pop in here until now. Can you post what your fix was so others might benefit?

 

[Access9001]

There was a custom function "rdate" in the module in the otherdb -- just had to copy that function over to the local db in a new module. Apparently functions in other databases need to be copied over locally if they are used in such expressions.

 

[SOS]

Well, actually they don't need to be but it is a bit of a process to use them outside of them. It is the easiest to copy them in.