mrperfect75000
asked on
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=Us ersGroup;p wd=passwor d;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(dbOpenSn apshot)
SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.tra nsdate) 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(dbOpenSn apshot)
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).Va lue)) 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=Us ersGroup;p wd=passwor d;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].[#con f attended])>2));"
qdf.Sql = SQL1 & SQL2 & SQL3
Set InvRS = qdf.OpenRecordset(dbOpenSn apshot)
SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.tra nsdate) 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(dbOpenSn apshot)
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).Va lue)) 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
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=Us
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(dbOpenSn
SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.tra
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(dbOpenSn
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).Va
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=Us
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].[#con
qdf.Sql = SQL1 & SQL2 & SQL3
Set InvRS = qdf.OpenRecordset(dbOpenSn
SQL1 = "SELECT DISTINCT Participants.CustID, Participants.InvID, Min(InvoiceTransaction.tra
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(dbOpenSn
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).Va
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
be sure that there is a space in front of FROM and WHERE
ASKER
before the qoutes?
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].[#con f attended])>2));"
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].[#con
ASKER
I did that...still gives the same error. Why r the spaces necessary?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. Still getting the same error though.
ASKER
Set InvRS = qdf.OpenRecordset(dbOpenSn
has the culprit. I have no clue why.