January 9, 2009 01:22am pst

1. LSMCons... 2,500
2. RobSamp... 2,000
3. Dabas 500

1. leonstryker 663,946
2. angelIII 320,840
3. emoreau 302,208
4. acperkins 265,882
5. Dhaest 197,159
6. TimCottee 162,377
7. Arthur_... 153,935
8. MYLim 135,434
9. Sethi 112,456
10. Dabas 110,481
11. mdougan 101,810
12. ryancys 98,083
13. leclairm 93,623
14. mlmcc 93,143
15. LSMCons... 91,568
16. jimhorn 91,332
17. capricorn1 86,338
18. aikimark 83,779
19. rockiroads 71,034
20. harfang 64,790
21. spongie 60,226
22. jkaios 46,350
23. GrahamSkan 43,758
24. Michael_D 42,870
25. CarlWarner 41,949

11.18.2004 at 10:42AM PST, ID: 21212090

	[x] Attachment Details

Run-Time error 3146 ODBC - Call failed

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

7.4

Zone:

VB Database Programming

Tags:

3146, error, odbc, failed, call

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

#	Title
1	ODBC Call Failed with MySQL Linked T…
2	ODBC timeout
3	ODBC connection to QUSRSYS
4	ODBC connection failing after sitting idl…
5	ODBC - Call Fail??
6	ODBC Driver Error