QODBC error 3146 ODBC--call failed

3146 ODBC--call failed.
[QODBC] Error (#10004)

Microsoft® Access may present this error message for various reasons. One reason  may be too few records in your QuickBooks® file or a too fast computer processor or a combination of the two.

For example, you may create SQL to import the QuickBooks® list of price levels into Microsoft® Access. If your file contains only a few price levels and your computer boasts a decent processor, you may receive the above message.

I have found that using an error counter to retry the SQL a few times often solves this problem but not always. Displaying a message box on the first retry; however, has virtually eliminated this issue for me.

The following code uses a Pass-through query to extract the QuickBooks® PriceLevel table into Microsoft® Access and creates an Access table from the imported data.

The Pass-through query extracts the following fields:
  1. listid
  2. name
  3. isactive
  4. timecreated
Note that limiting extraction to required fields speeds query time. Using an ampersand in your SQL statement extracts all fields including those you may not need. To construct a form that displays QuickBooks® tables and their corresponding fields, see the post:

            QuickBooks Tables in Access w/ QODBC Form Part 1            http://www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html

****************************************
Private Sub lblReloadPriceLevels_Click()
On Error GoTo lblReloadPriceLevels_Click_err
'Declare variables
100     Dim db As DAO.Database, qd As DAO.QueryDef, q As String, t As String
'give the table a name
120     t = "tblPriceLevelsForEstimates"
'the following function deletes existing tables
'find this function in this post on The Generic Database
'      http://www.thegenericdatabase.com/2011/09/function-fncdeletetable.html
130     Call fncDeleteTable(t)
140     q = "qryTemp"
'the following function deletes existing queries
'find this function in this post on VBQuick:
'       http://www.vbquick.com/2011/10/function-delete-query-microsoft-access.htm
l150     Call fncDeleteQuery(q)
'assumes you are working in the current database
160     Set db = CurrentDb
'create a new QueryDef
170     Set qd = db.CreateQueryDef(q)
'the following function puts your QODBC connection string into a function
'find this function on VBQuick:
'      http://www.vbquick.com/2011/10/vba-microsoft-access-function-qodbc.html
180     qd.Connect = QODBCConnect
'set the Pass-through query to return records
190     qd.ReturnsRecords = True
'set the timeout
200     qd.ODBCTimeout = 60
'create the Pass-through query's SQL string
210     qd.sql = "select listid,name,isactive,timecreated from pricelevel"
'you are finished with the Pass-through query so set the variable to nothing
215     Set qd = Nothing
'you are also finished with the database variable so set that to nothing
220     Set db = Nothing
'declare an integer to count the number of connection tries
230     Dim c As Integer
'set warnings to false so you do not receive the message about putting records into a table
250     DoCmd.SetWarnings False
'run SQL to select fields from the Pass-through query (q) and put them into a new table (t)
260     DoCmd.RunSQL "select * into " & t & " from " & q
'set the warnings back to true
270     DoCmd.SetWarnings True
'open the table to view the results
330     DoCmd.OpenTable t
340     Exit Sub
lblReloadPriceLevels_Click_err:
'trap for error number 3146 and set the number of tries you wish to attempt before quitting
If Err.Number = 3146 And c < 5 Then
'count the number of tries
    c = c + 1
'display a message box whose only purpose is to help aid in retrieval when too few records exist
    MsgBox "ODBC call failed. Click OK to try again."
'try again
    Resume
End If
'if the error number is not 3146 OR if the number of tries has been reached, display the error information and quit trying
'the following function can be found on The Generic Database:
'      http://www.thegenericdatabase.com/2011/09/03-function-fncwriteerror.html
Call fncWriteError(Now, Form.name, "", "Private Sub lblReloadPriceLevels_Click", Err.Number, Err.Description, Erl, "")
End Sub

************************************

To test the above code:
  1. open QuickBooks if you need to have it open to use QODBC
  2. have QODBC (at least the read version) installed on your computer
  3. you do not need to link tables or anything like that, just paste the code
  4. with the code pasted into a module or form, either click the corresponding button if you set this code to a button or type: lblReloadPriceLevels_Click into the immediate window of the Visual Basic Editor.
You will also need the following references checked in your Microsoft®  Access Database:

  1. Microsoft DAO 3.6 Library
  2. Visual Basic for Applications
  3. Microsoft Access 10.0 Object Library