Recordsets from disparate sources 1

[mike777]

Hello!
Is there a way to do joins on recordsets when one comes from, say a table in an Interbase database, and the other an MS Access table?
I am used to using FoxPro where you can create multiple cursors from wherever (it doesn't matter where they came from), and then perform any table/join/filter/order/etc. function on the cursor after it is created in FoxPro (a cursor is an invisible table, it doesn't have any visible GUI to go along with it).
I was wondering if you could do the same thing in ADO/MS Access, or do you have to actually create a table, and then use the table to create a new recordset or query.
Like:

Dim rst1 as new adodb.recordset
dim rst2 as new adodb.recordset
Dim sSQL as string
Dim cnADO as adodb.connection
cnado.open "SOMEINTERBASECONNECTION"
sSQL="SELECT * from someInterbasetable"
rst1.open sSQL,cnADO
cnADO.close
cnADO.open currentproject.connection
sSQL="select * from SomeNativeMSAccessTable"
rst2.open sSQL,cnADO

....then join rst1 to rst2????
Many thanks.
-Mike

 

[MikeTompkins]

Mike -

Is linking through ODBC an option for you? I connect to Oracle tables, spreadsheets, and other Access files through that method and just use the gui to create querys connecting the tables together.

Michael

 

[cmmrfrds]

Here is an example of joining across 2 mdb files using 2 ADO connections and executing from the current connection. You should be able to substitute one connection for Interbase.

Function ExternalTables2()

Dim cn As New Connection, cn2 As New Connection
Dim rs As New Recordset, rs2 As New Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
connString2 = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee2.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString2
cn.Open connString2

sql1 = "select * from cn...employees as A inner join cn2...employees as B" & _
"ON A.employeeID = B.employeeID"

rs.Open sql1, CurrentProject.Connection, adOpenStatic, adLockOptimistic
Debug.Print "rs = "; rs(0); " "; rs(1)

End Function

 

[mike777]

Thanks for the responses.
CMMRFRDS, that's a helpful suggestion. The remaining question, though, is what would happen if you needed to perform functions on the data from the disparate data sources?
For example to convert a datetime from 05/03/03 1:03:03pm to 05/03/03, in MS Access, I would say

format([somedatecolumn],"mm/dd/yy")

, but in Interbase you have to say (I believe)

cast([somedatecolumn] as date)

. Regardless, I am certain MS Access has functions Interbase doesn't understand and vice-versa.
My assumption at this point is that MS Access can't join disparate ADO recordsets; you actually need a physical object (query or table). This is easy enough to do via code

 dim dbs as database, qdf as query def:set dbs=currentdb:set qdf=dbs.createquerydef("mynewquery"):qdf.sql=someSQL:blahblahblah

, but I would like to avoid having to do all that if possible.
Anybody has any other ideas on this, or see something that is wrong that I have concluded here, your input is welcome.
Thanks again.
-Mike

 

[cmmrfrds]

The only way I know is to retrieve both recordset and then add one recordset to the end of the other recordset one record at a time, but this seems rather cumbersome and the data types and columns would need to match. Since the queries are database specific anyway, I would do the formatting in the query for the dates etc.. and go the query route.

 

[mike777]

cmmrfrds...got it....thanks