ADO joining tables from different databases

[bartekR]

Hi

I've got 2 excel files which i want to compare and find out how many records with the same code (Sedol) exist in both of them. In other words it is an equivalent of creating an inner join on 2 tables.

The problem is that my 2 tables(SHEETS :B5LEPF$,B5LIPF$) are in different workbooks.

I have found a link that shed some light on what i want to achieve :

http://vb-helper.com/howto_ado_2db_join.html

however when i use this code in my macro i get an error :
Unrecognized database format - pointing to the path of my 2nd database. Is it because the 2nd databse is an excel spreadsheet? Can this be used with excel at all?

Could anyone help me with the SQL statement that would eneable me to create a join between 2 tables from different datasources?

PS : i know that there are easier ways of doing this in excel but i want specifically do this via ADO for training purposes

thank you and here's the code :


Sub Test()

On Error GoTo errorhandler

Dim rs As ADODB.Recordset
Dim sConnect As String
Dim sSql As String

Dim sDbase1Path As String
Dim sTable1Name As String
Dim sCol1Name As String

Dim sDbase2Path As String
Dim sTable2Name As String
Dim sCol2Name As String

Set rs = New ADODB.Recordset

sDbase1Path = ThisWorkbook.Path & "\" & "Portfolio -Equity.xls"
sTable1Name = "[" & "B5LEPF$" & "]"
sCol1Name = "[" & "SEDOL CODE" & "]"

sDbase2Path = "[;Database=" & ThisWorkbook.Path & "\" & "Portfolio - INTL .xls" & "]."
sTable2Name = "[" & "B5LIPF$" & "]"
sCol2Name = "[" & "SEDOL CODE" & "]"


sSql = "SELECT * FROM " & sTable1Name & ","
sSql = sSql & sDbase2Path & sTable2Name
sSql = sSql & " " & "WHERE " & sTable1Name & "." & sCol1Name & "=" & sTable2Name & "." & sCol2Name


'MsgBox sSql

sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sDbase1Path & _
";Extended Properties=Excel 8.0;"

rs.Open sSql, sConnect, adOpenForwardOnly, adLockReadOnly

Range("A1").CopyFromRecordset rs


errorhandler:

Debug.Print Err.Number
Debug.Print Err.Description

End Sub

 

[SkipVought]

Hi,

I do this on occasion, using these techniques -- faq68-5829.

No ADO VBA coding required. All drag 'n' drop.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ettienne]

You will have a problem with comparing 2 tables in 2 different databases - this is the way ADO views the 2 Excel spreadsheets.

You have a few options to get around that:
1. Put both worksheets in one Excel spreadsheet, this gives you one database with 2 tables from an ADO viewpoint.
2. Attach both Excel worksheets to an Access database, this again gives you one database and 2 tables to compare.

I do a lot of work using option 2 to compare Excel, Access, MS SQL & Pervasive databases, this certainly works and is relatively easy to do in ADO.