[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 39107
  • Last Modified:

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
1
mrperfect75000
Asked:
mrperfect75000
  • 4
  • 3
1 Solution
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
É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
 
mrperfect75000Author Commented:
Thank you. Still getting the same error though.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now