Excel 2010 VBA using ADODB and join on two TAB's comparing the left 6 characters, get Join Error 1

[DougP]

We have numbers coming form 2 different systems and the last character is different.
System 1 = A123451 and system 2 has A123450. They are the same number for our report but instead of making a separate column for one or the other and adding a 1 to it lets say, I thought about making a Select which would make fast work of it and also filter only for “YES” on the one table/TAB

But Excel ADO does not like it, where Access did, I made the SQL string in the QBE grid in Access and ran it to reurn results.
Any ideas how to appease the Excel ADO Gods?
TIA

    FileName = Application.ActiveWorkbook.Fullname
    
    ' open ADODB connection
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & FileName & ";" & _
        "Extended Properties=Excel 8.0;"
        .CursorLocation = adUseClient
        .Open
    End With
    
    'open recordset and
    SQLString = "SELECT [Project ID], [Program ID], [Total Hours], [SOP Capital Status] " & _
                "FROM SOP INNER JOIN Report ON left([Program ID],6) = left([Project ID],6) " & _
                "WHERE ((([SOP Capital Status])='YES'));"

    rs.Open SQLString, cn <<< Error

DougP

 

[PHV]

What about this ?

SQLString = "SELECT [Project ID], [Program ID], [Total Hours], [SOP Capital Status] " & _
            "FROM SOP, Report WHERE Left([Program ID],6) = Left([Project ID],6) " & _
            "AND [SOP Capital Status]='YES'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

sweet and FAST !!!!

DougP

 

[DougP]

only one issue I found though is while this is simple and to the point, Excel ADO takes "FORRRRR EVERrrrr" to run it. so much so I have to Ctrl-Alt "dump" it.
is there anyhting to help speed it up, tweaking something, I have a Intel i5 Processer, Win 7 64bit and 3gig RAM.

Or I guess it's back to doing in one step at a time.
But thanks PHV it works in Access too.


DougP

 

[SkipVought]

often you destroy the effectiveness of table keys, using a substring of the key, and that means that it will take much longer to execute a query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DougP]

so Skip, is there a solution beside the "slow" way?

DougP

 

[SkipVought]

have you db people make another key that works

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}