Run-Time error 3146 ODBC - Call failed

I'm getting this error Run-Time error 3146 ODBC - Call failed when i try to run this code. I dont know what i'm doig wrong.  The code worked until i changed the SQL query. I have pasted both, the difference is the first SQL query. pLease help.

Original Working Code.
Sub Fill_Invoice_Info()
    Dim InvRS As Recordset
    Dim RDRS As Recordset
   
    Dim qdf As QueryDef
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = "odbc;dsn=SQLServer;uid=UsersGroup;pwd=password;database=nicabm;"
    qdf.ReturnsRecords = True
    SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Invoice.PCode, Invoice.BadgeName, Invoice.Leadership  "
    SQL2 = "FROM Invoice INNER JOIN Participants ON Invoice.InvID = Participants.InvID;"
    qdf.SQL = SQL1 & SQL2
    Set InvRS = qdf.OpenRecordset(dbOpenSnapshot)
       
    SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.transdate) AS MinOftransdate "
    SQL2 = "FROM Participants INNER JOIN InvoiceTransaction ON Participants.InvID = InvoiceTransaction.invid "
    SQL3 = "GROUP BY Participants.CustID, Participants.InvID;"
    qdf.SQL = SQL1 & SQL2 & SQL3
    Set RDRS = qdf.OpenRecordset(dbOpenSnapshot)
       
       
    For i = 2 To NumOfPart + 1
        CurrCust = MT.Cells(i, CustIDCol).Value
        Criteria = "CustID = """ & CurrCust & """"
        InvRS.FindFirst Criteria
        MT.Cells(i, InvStart).Value = InvRS.Fields(3).Value     'Field(3) is BadgeName
        If Not (IsNull(InvRS.Fields(4).Value)) Then             'Field(4) is Leadership Council
            If InvRS.Fields(4).Value = 1 Then MT.Cells(i, LCCol).Value = "Leadership Council"
        End If
        RDRS.FindFirst Criteria
        MT.Cells(i, RDCol).Value = RDRS.Fields(2).Value     'Field(2) is RegDate
    Next i
End Sub




New COde that doesnt work.

Sub Fill_Invoice_Info()
    Dim InvRS As Recordset
    Dim RDRS As Recordset
   
    Dim qdf As QueryDef
    Set qdf = db.CreateQueryDef("")
    qdf.Connect = "odbc;dsn=SQLServer;uid=UsersGroup;pwd=password;database=nicabm;"
    qdf.ReturnsRecords = True
        SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Invoice.PCode, Invoice.BadgeName, [NewAttendance#1].[#conf attended]"
   SQL2 = "FROM (Invoice INNER JOIN Participants ON Invoice.InvID = Participants.InvID) INNER JOIN [NewAttendance#1] ON Invoice.CustID = [NewAttendance#1].CustID"
    SQL3 = "WHERE ((([NewAttendance#1].[#conf attended])>2));"

       
    qdf.Sql = SQL1 & SQL2 & SQL3
    Set InvRS = qdf.OpenRecordset(dbOpenSnapshot)
   
       
    SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.transdate) AS MinOftransdate "
    SQL2 = "FROM Participants INNER JOIN InvoiceTransaction ON Participants.InvID = InvoiceTransaction.invid "
    SQL3 = "GROUP BY Participants.CustID, Participants.InvID;"
    qdf.Sql = SQL1 & SQL2 & SQL3
    Set RDRS = qdf.OpenRecordset(dbOpenSnapshot)
       
       
    For i = 2 To NumOfPart + 1
        CurrCust = MT.Cells(i, CustIDCol).Value
        Criteria = "CustID = """ & CurrCust & """"
        InvRS.FindFirst Criteria
        MT.Cells(i, InvStart).Value = InvRS.Fields(3).Value     'Field(3) is BadgeName
        If Not (IsNull(InvRS.Fields(4).Value)) Then             'Field(4) is Leadership Council
            If InvRS.Fields(4).Value = 1 Then MT.Cells(i, LCCol).Value = "Leadership Council"
        End If
        RDRS.FindFirst Criteria
        MT.Cells(i, RDCol).Value = RDRS.Fields(2).Value     'Field(2) is RegDate
    Next i
End Sub
mrperfect75000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrperfect75000Author Commented:
It highlights
Set InvRS = qdf.OpenRecordset(dbOpenSnapshot)
 has the culprit. I have no clue why.
0
Éric MoreauSenior .Net ConsultantCommented:
be sure that there is a space in front of FROM and WHERE
0
mrperfect75000Author Commented:
before the qoutes?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Éric MoreauSenior .Net ConsultantCommented:
       SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Invoice.PCode, Invoice.BadgeName, [NewAttendance#1].[#conf attended]    /* HERE */  "
   SQL2 = "FROM (Invoice INNER JOIN Participants ON Invoice.InvID = Participants.InvID) INNER JOIN [NewAttendance#1] ON Invoice.CustID = [NewAttendance#1].CustID     /* HERE */ "
    SQL3 = "WHERE ((([NewAttendance#1].[#conf attended])>2));"
0
mrperfect75000Author Commented:
I did that...still gives the same error. Why r the spaces necessary?
0
Éric MoreauSenior .Net ConsultantCommented:
because when you concatenate sql1+2+3 it gives ".CustIDWHERE (..."
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrperfect75000Author Commented:
Thank you. Still getting the same error though.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.