Inner join to Access table

[Databaseguy]

I have an Access table that is populated with results from an EDI document via visual basic. The data contained is purchase order numbers and ship tos. It's purpose is to change the ship to in a FoxPro table.

Normally, I would simply use the ODBC driver for fox pro and link the table. Problem is, I'm running 6.0 and the application I have to access is an older version, so I can't link the tables because I have a different ODBC driver.

I can get at the FoxPro data like this:

Dim rstFox As New ADODB.Recordset
Dim connFoxPro As String
Dim strSQLFox As String
connFoxPro = "Driver={Microsoft Visual FoxPro Driver};" & _
"SourceType=DBF;" & _
"SourceDB=\\asnplus\asnplus\dbfs\;" & _
"Exclusive=No"
strSQLFox = "SELECT * from MyFoxproTable"
rstFox.Open strSQLFox, connFoxPro, adOpenDynamic, adLockOptimistic


And to get to the Access data, I can do this:

Dim rstLocal As New ADODB.Recordset
rstLocal.Open "MyLocalTable", CurrentProject.Connection, adOpenDynamic, adLockOptimistic

What I want to do is inner join a field in both tables (PO Num).

Any ideas ?





Tyrone Lumley
augerinn@gte.net

 

[rgbean]

Tyrone,
This appears to be Access / VB code, so once you get the VFP data into a RS, the question might better be asked on either the Access or VB forums. ("Recordsets is Recordsets - I see no VFP here!&quot

Rick