[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:
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
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
130 Call fncDeleteTable(t)
140 q = "qryTemp"
'the following function deletes existing queries
'find this function in this post on VBQuick:
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:
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
'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."
'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:
Call fncWriteError(Now, Form.name, "", "Private Sub lblReloadPriceLevels_Click", Err.Number, Err.Description, Erl, "")
To test the above code:
- open QuickBooks if you need to have it open to use QODBC
- have QODBC (at least the read version) installed on your computer
- you do not need to link tables or anything like that, just paste the code
- 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.
- Microsoft DAO 3.6 Library
- Visual Basic for Applications
- Microsoft Access 10.0 Object Library