Problem Description:

How to export QuickBooks Data to Google Sheets using QXL

Solutions: 

Install the Google Drive desktop application and authorize it to connect to your Google Drive account.

Start Windows Explorer and navigate to your local Google Drive folder.

Create a QXL folder in Google Drive.

Start QXL Desktop and click on Settings.

In the "General" tab, set the path to the "Google Drive" path in the "Output folder."

For Example- C:\Google Drive\QXL

Close settings

Export the QuickBooks Data

Once the export is complete, the output folder showing the list of files will open automatically.

The output folder will contain the list of exported files.

The exported files will be automatically uploaded to Google Drive.

Open your web browser and navigate to - drive.google.com.

Navigate to the QXL folder.

You will find your QuickBooks company file folder.

Double-click the QuickBooks company folder to view the list of exported files.

To preview the file, double-click on any of the files.

In preview mode, you will not be able to change/modify any records.

To edit any file, go back to the preview.

Right-click on the selected file, and in the "Open with" option, select "Google Sheets."

Tags: QXL Desktop, Google Sheets

Problem Description:

How do I determine which database it is using to set up the connection with QuickBooks?

Solutions: 

You may run the following command to determine which database you are connected to.

sp_qbfilename

This will return the database (QuickBooks company file) path with which you are connected.

Using the QuickBooks application, you may verify the database (QuickBooks company file) path.

You can find the QuickBooks company file path via QuickBooks UI. Press the F2 key (Function Key 2) you will see the file information.

You may verify the QuickBooks company you are connected to.

To verify, please execute the SQL Statement using your application.

Select * from Company

Tags: QuickBooks company file name, company file path, database, database connection

Problem Description 1:

I want to apply the Payment to a particular Invoice using Deposits.

How can I do so using the QODBC?

Solutions: 

Refer to the screenshot of the QuickBooks UI Invoice created.

You can use this 'Invoice No.' or 'RefNumber' to search for a specific Invoice.

Note the ref number of the Invoice, i.e., '7777777-124.'

You can find this Invoice created using the QODBC Test Tool using the following SQL query:

SELECT * FROM InvoiceLine where RefNumber='7777777-124.'

Note the value of the field 'TxnID' - '8DF3-1481796954' from the 'InvoiceLine' table.

Use this value of the field 'TxnID' in the 'ReceivePaymentLine' table.

SELECT * FROM ReceivePaymentLine where AppliedToTxnTxnID='8DF3-1481796954'

The value in the field 'AppliedToTxnTxnID' from the ReceivePaymentLine table and 'TxnID' in the InvoiceLine table are equal.

i.e., ReceivePaymentLine.AppliedToTxnTxnID = InvoiceLine.TxnID

Note the value of the field 'TxnID' - '8DF7-1481797010' from the 'ReceivePaymentLine' table.

Use this value of the field 'TxnID' in the 'DepositLine' table.

SELECT * FROM DepositLine where DepositLineTxnID='8DF7-1481797010'

The value in the field 'TxnID' from the ReceivePaymentLine table and 'DepositLineTxnID' in the DepositLine table are equal.

i.e., DepositLine.DepositlineTxnID = ReceivePaymentLine.TxnID

Search for the field 'DepositLineCheckNumber' in the 'DepositLine' table.

In this example, the value is '20181225-0222'.

Search for this specific reference number in QuickBooks UI in 'Deposit.'

Below is the screenshot of the QuickBooks application of 'Customer Payment' or 'ReceivePayment.'

Tags: Link invoice to deposit, Deposit, Invoice

Download Sample

Sample Code for using QODBC DSN:

$connectstring = "DSN=QuickBooks Data;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Script execution & Result:

Open Windows PowerShell (x86) from the Start menu:

Navigate to your PowerShell script folder:

Run your PowerShell script:

Result:

Sample Code for using QRemote DSN:

$connectstring = "DSN=QuickBooks Data QRemote;"

$sql = "Select TOP 10 Name from Customer"

$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)

$conn.open()

$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)

$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)

$dt = New-Object system.Data. data table

$null = $da.fill($dt)

$conn.close()

$dt

Please refer to how to configure QRemote.

Please Note: If you have a 64-bit application, you must use QRemote 64-bit DSN "QuickBooks Data 64-bit QRemote."

Also, Refer:

QODBC Reports Architecture Mismatch Problem When Connecting

First, you need to create a Linked Server in SQL Server.

For 32-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote.

For 64-bit SQL Server. Please refer to How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote.

I have already created a linked server, "QRemote."

After creating a Linked Server, you need to create a new database in SQL Server for creating the stored procedure which accepts SQL queries. The stored procedure will call the linked server and execute the query.

To create a database, you need to right-click on "Database" & click on "New Database."

Enter the database name & click "OK." In this example, I am creating the database "QODBC."

Database created.

Now, we will create a stored procedure that accepts SQL queries. The stored procedure will call the linked server and execute the query.

In Object Explorer, connect to an instance of Database Engine and then expand that instance.

Expand Databases, expand your database, and then expand Programmability.

Right-click Stored Procedures, and then click New Stored Procedure.

We will create two stored procedures first one will give us a list of all customers & second procedure will accept SQL queries through parameters & execute them on the linked server & provide the query result.

We will create the stored procedure named GetCustomer, which will query the Customer table through the linked table in SQL Server & provide a list of the customers.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE GetCustomer

AS
BEGIN
SET NOCOUNT ON;

SELECT * from QRemote...customer

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

By executing this stored procedure, we will get a list of the Customers.

We will create a second stored procedure named QODBCQuery, which will accept an SQL query through parameters & execute on the linked server & provide the query result.

Stored Procedure Created.

Stored Procedure Code:

================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create PROCEDURE QODBCQuery

@sqlQuery nvarchar( 4000)

AS
BEGIN
SET NOCOUNT ON;

DECLARE @TSQL varchar(8000)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(QRemote,''' + @sqlQuery + ''')'
EXEC (@TSQL)

END
GO
================================================================

Note: The above-stored procedure code is, for example. You can change it as per your requirement.

We must pass the query as the parameter value to execute this stored procedure.

Query results through the stored procedure.

Now, we will create a SQL Server DSN on the non-SQL Server machine to access QuickBooks Data remotely via SQL Server.

Locate the QODBC Driver for the QuickBooks program group on your desktop. Click on the Configure QODBC Data Source icon to create a SQL Server DSN.

Click on the System DSN tab to create a DSN that both Windows applications and services can call. Click on the Add button to create a new DSN.

Select SQL Server & click "Finish."

Enter the DSN name and SQL Server machine IP address & click "Next."

Select the authenticity of SQL Server & click "Next."

Select the SQL Server database where we have created the stored procedure & click "Next."

Click "Finish."

Click on "Test Data Source..." to test the connection.

Test connection success.

Note: If you are using the latest version of QODBC, you can use the "QODBC 64-Bit Test tool."

Open the "QODBC 64-Bit Test tool" from the start menu.

Click on the "Drop-down Menu" button.

Select DSN from the Drop-down list.

For example: "QuickBooks Data 64-Bit QRemote."

Click on the "Connect" button.

Write down the query in the marked field. Please refer to the image.

Click on the "Run" button.

Note: The above Query is, for example. You can change it as per your requirement.

Download Sample

Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

It would be best if you opened QuickBooks & load the company file in QuickBooks.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.

'QuickBooks Data' as an example:

The first time you used MS Excel to access QuickBooks. You will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel spreadsheet.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

Now we will add a button & we will write code for the button click event. On button click, it will open the connection, refresh the report data & close the connection.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open the connection, refresh the report data & close the connection.

Private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from QuickBooks Data"). Refresh
End Sub

Note: Query from QuickBooks Data is the name of the connection. You can get it from the Data--> Connections.

Report Data will be refreshed by clicking the Refresh Data button.

Download Sample

In this example, we will show you how to insert an Invoice in QuickBooks using the data available in an Excel spreadsheet.

We have an Excel spreadsheet that contains invoice-related information. The Excel spreadsheet has a CustomerRefFullName, RefNumber, TxnDate, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineRate, InvoiceLineQuantity, InvoiceLineSalesTaxCodeRefFullName, and FQSaveToCache information.

Using this information, we will create an Invoice in QuickBooks, which has 4 line items.

Now we will add a button & we will write code for the button click event. On button click, it inserts the Invoice in QuickBooks.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to ADD Invoice—double-click on the command button to write code.

Write the code below for inserting an invoice in QuickBooks from an Excel spreadsheet.



Private Sub CommandButton1_Click()

Dim sh As Worksheet
Dim row As Range
Dim RowCount As Integer
Dim CustomerRefFullName As String
Dim RefNumber As String
Dim TxnDate As Date
Dim InvoiceLineItemRefFullName As String
Dim InvoiceLineDesc As String
Dim InvoiceLineRate As Single
Dim InvoiceLineQuantity As Single
Dim InvoiceLineSalesTaxCodeRefFullName As String
Dim FQSaveToCache As Boolean
Dim connection As New ADODB.Connection
Dim sConnectString
Dim sSQL As String
Dim rs
Dim sMsg

RowCount = 0
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"

'' For 64-bit use this one-->sConnectString = "DSN=QuickBooks Data 64-bit QRemote;"
sSQL = "SELECT * FROM InvoiceLine"
Set rs = New ADODB.Recordset
oConnection.Open (sConnectString)
rs.Open sSQL, oConnection, adOpenDynamic, adLockOptimistic
Set sh = ActiveSheet
For Each rw In sh.Rows

If (RowCount > 0), Then
CustomerRefFullName = sh.Cells(rw.Row, 1).Value
If (CustomerRefFullName = "") Then
Exit For

End If

RefNumber = sh.Cells(rw.Row, 2).Value
TxnDate = sh.Cells(rw.Row, 3).Value
InvoiceLineItemRefFullName = sh.Cells(rw.Row, 4).Value
InvoiceLineDesc = sh.Cells(rw.Row, 5).Value
InvoiceLineRate = sh.Cells(rw.Row, 6).Value
InvoiceLineQuantity = sh.Cells(rw.Row, 7).Value
InvoiceLineSalesTaxCodeRefFullName = sh.Cells(rw.Row, 8).Value
FQSaveToCache = sh.Cells(rw.Row, 9).Value

rs.AddNew
rs("CustomerRefFullName") = CustomerRefFullName
rs("RefNumber") = RefNumber
rs("TxnDate") = TxnDate
rs("InvoiceLineItemRefFullName") = InvoiceLineItemRefFullName
rs("InvoiceLineDesc") = InvoiceLineDesc
rs("InvoiceLineRate") = InvoiceLineRate
rs("InvoiceLineQuantity") = InvoiceLineQuantity
rs("InvoiceLineSalesTaxCodeRefFullName") = InvoiceLineSalesTaxCodeRefFullName
rs("FQSaveToCache") = FQSaveToCache
rs.Update

End If

RowCount = RowCount + 1
Next rw
sMsg = sMsg & "Invoice Added!!!"
MsgBox sMsg

rs. Close
oConnection.Close

End Sub

On clicking on the ADD Invoice button, the Invoice will be added to QuickBooks.

Invoice Added.

You can see the same in the QuickBooks Invoice that is added.

Download Sample

There is a limitation with the QuickBooks Desktop SDK: it can connect only one company file to a user session at a time.

It means you cannot access multiple company files at the same time.

You need to close the connection with one company file and switch to another. ( i.e., connection closed for company A and Connection open for company B should have a gap of 15-30 seconds for switching operation.)

This applies to QuickBooks Desktop. When using QuickBooks Online, you can connect to multiple company files simultaneously.

In this article, we will connect to two different QuickBooks Desktop company files using two different QODBC DSNs with auto login configured. In this example, we will use two auto logins, DSN CompanyA & CompanyB.

Please Refer:
How to set up QODBC Desktop for Auto-login into QuickBooks Desktop
How to set up the QODBC Driver to auto-connect to QuickBooks Desktop. (Video)

Here is my DSN configuration for the First QuickBooks Desktop Company file.

It would be best if you opened QuickBooks Desktop & loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Open MS Excel & create a new spreadsheet.
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query. 

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyA' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Trial Balance report query.
sp_report TrialBalance show Debit_Title, Credit_Title, Label, Debit, Credit parameters DateMacro = 'LastMonth', ReportBasis = 'Cash'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet.

Repeating the above steps will export another report in sheet 2 using DSN CompanyB.

We need to close the first company file, which is open in QuickBooks Desktop.

Here is my DSN configuration for the Second Company file.

It would be best if you opened QuickBooks Desktop and loaded the company file that you have chosen in the auto login DSN. The company file path in QuickBooks & DSN should be matched.

Navigate to sheet2
First, please select the "Data" tab, navigate to "From Other Sources"->" From Microsoft Query."

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.)

'CompanyB' as an example:

The first time you use MS Excel to access QuickBooks Desktop, you will be asked to grant access permission for QODBC. After you grant permission for QODBC, you will be guided by the Query Wizard. Choose any table and click "Next" until you reach the Query Wizard.

Select View data or edit query in Microsoft Query & click Finish:

Write a query & click OK. In this example, I am writing a Profit & Loss report query.
sp_report ProfitAndLossStandard show Amount_Title, Text, Label, Amount parameters DateMacro = 'ThisMonthToDate', SummarizeColumnsBy = 'TotalOnly'

You will get the following message box about the SQL Query. Click OK.

Report data is available in Microsoft Query:

Click on File & Return Data to Microsoft Excel to save the export report in an Excel spreadsheet.

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

Report Data is exported to an Excel sheet 2.

After the export is complete, close the company file opened in QuickBooks Desktop.

Now we will disable auto-refresh of data from Data-->Connection-->Properties-->Uncheck Enable Background Refresh & Click OK:

We need to disable auto-refresh data for both company file connections (i.e., Query from CompanyA & Query from CompanyB ).

Now we will add a button & we will write code for the button click event. On button click, it will open the connection for CompanyA, refresh the report data & close the connection for CompanyA, sleep for 15 seconds, open the connection for CompanyB, refresh the report data & close the connection for CompanyB.

You can add a button from the Developer tab in MS Excel. If the Developer tab is not available, display it.

Display the Developer tab.
Click the File tab, click Options, and then click the Customize Ribbon category.
Select the Developer check box in the Main Tabs list, and click OK.

On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button.

Command Button is added. I have renamed it to Refresh Data. Double-click on the command button to write code.

Write the code below to open a connection for Company A, refresh the report data & close the connection for Company A, sleep for 15 seconds, open the connection for CompanyB, restore the report data & close the connection for CompanyB.

Fifteen seconds of sleep are required to close the company file and switch to another private Sub CommandButton1_Click()
ActiveWorkbook.Connections("Query from CompanyA"). Refresh
Application.Wait (Now + TimeValue("0:00:15"))
ActiveWorkbook.Connections("Query from CompanyB").Refresh
End Sub

Report Data will be refreshed by clicking the Refresh Data button.

Keywords: QuickBooks Desktop, win log with multiple companies, log in with various companies

Problem Description:

I am getting an error SOCKET Closed because of an abnormal package in the QRemote client. I am accessing QuickBooks Data from the remote machine over the internet (WAN). 

Solution:

This issue will occur when the QRemote client & QRemote Server have different network types.The 

Windows component is internally checking the connection every 4 minutes. If there is no activity between the client & server, the Windows component will close the connection after 4 minutes & due to this, when the QRemote client tries to connect to the QRemote Server, it will not be able to connect, & SOCKET is Closed because an abnormal package error occurred.

To resolve this issue, you need to set the AutoClose value to 3 minutes for closing the broken connection in QRemote Server.

You can find the QRemote Server running in the System Tray. If QRemote Server is not running, then you can run it from Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC.



Open QRemote Server. Go to File Menu -> Settings -> Advanced Tab. Set AutoClose broken connection after 3 minutes & apply the setting by clicking the Apply button.



After applying the settings. Please restart the QRemote Server & test again. To continue QRemote Server, go to File Menu -> Exit -> :

You will get a pop-up window with the message. Do you want to close the QRemote Server? Please click on OK to close QRemoteServer.

Please rerun QRemoteServer & test. You can QRemoteServer from:
Start >> All Programs >> QODBC Driver for use with QuickBooks >> QRemote Server for QODBC

Problem Description:

I am using the code below. It is returning records & giving me the correct result. But if I change the query from "SELECT Name FROM customer" to "SELECT * FROM customer," it does not return any records & giving me the incorrect result. I am getting the following error:

[QODBC] Internal error
440: BOF or EOF is True, or the current record has been deleted. The requested operation requires a current record.



Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub 

Solution:

To resolve the issue, you need to add the line below to your connection string.

OptimizerAllowDirtyReads=N

For Example:

sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\QuickBooks\Maintenance.qbw;OpenMode=F;OLE DB Services=-2;OptimizerAllowDirtyReads=N"

After changing this, you can get the correct result.

Problem Description:

We are using QRemote with SQL Linked Servers with several different QuickBooks files. How can we close the connection to a QuickBooks file after the query has run, then open it to connect to another database?  

Solution:

SQL Server Linked Server automatically handles disconnections.

SQL Server Linked Server does not have a disconnection command that can be fired/executed.

After executing the query, you will have to wait 30-45 seconds. SQL Server will automatically send the disconnect command if there is no activity on the linked server connection.

Problem Description:

I have an MS Access application using QODBC to connect to desktop QuickBooks. Can I use the QODBC Online driver to connect to QuickBooks Online without changing my MS Access code?  

Solution:

QODBC Online is a different product from the QODBC Desktop driver. So you cannot use the QODBC Desktop driver with QuickBooks Online. It would be best if you used the QODBC Online driver to connect to QuickBooks Online.

Most of the Tables, table columns, reports, report columns & report parameters in QuickBooks Desktop & Online are the same. But not all the tables, reports & fields available in QuickBooks desktop are available in QuickBooks Online. You might have to change a few areas to match QuickBooks Online. You need to refer to QODBC Online reports/tables/fields & make changes in your code.

Please refer Difference between QODBC for QuickBooks Desktop and Online & make necessary changes in your code & use the code with QODBC Online.

Tags: QuickBooks Online, QBO, code, QODBC Online, QODBC Desktop

Problem Description:

How many concurrent connections does the Server edition support?

Is the product QuickBooks version specific, requiring additional purchases with each version? AKA. Upgrading from V15 to V16? 

Solution:

There is no limit on QODBC for the concurrent connection, but the more connections, the more QuickBooks has to work. QuickBooks SDK will process all the requests in a sequence, so if you have three connections and three queries going at the same time, the SDK will execute them in sequence.

QODBC license is licensed for QuickBooks Version(Year). E.g., If you have QODBC licensed for 2015, you can use QODBC with QuickBooks 2015,2014,2013, and so on, but not with QuickBooks 2016.

You can install QODBC v15 and v16 with no upgrade cost. Upgrade cost is only applicable when your license does not match the year.

For Example:

If you have QODBC licensed for 2015 & you are using QODBC v15 & QuickBooks 2015, you can install QODBC v16 and use it with QuickBooks 2015.

Problem Description:

I am trying to export data from QXL. But when I clicked on the QXL export button, I got the following error message.

Unable to connect to QuickBooks. Popup window(s) in the QuickBooks application MUST be closed before QXL can communicate with QuickBooks.

Error Occurred: ERROR [00000] [QODBC] QuickBooks Begin Session Failed. Error = 80040414. A modal dialog box is showing in the QuickBooks user interface. Your application cannot access QuickBooks until the user dismisses the dialog box.

Solution:

Please navigate to QuickBooks & make sure that there is no modal pop-up window open in QuickBooks. If any modal pop-up window is available in QuickBooks, you will get an "Error = 80040414; A modal dialog box is showing in the QuickBooks user interface....". QuickBooks says that a modal connection is active for this part of QuickBooks, and you need to wait.

It would be best if you closed this modal popup window in QuickBooks & export data using QXL.

QXL does not have control over the modal pop-up window in QuickBooks. QXL cannot suppress or bypass this type of dialogue. It would be best if you disabled the modal pop-up from QuickBooks.

Tags: Unable to connect to QuickBooks using QXL

Intuit is reinforcing existing security features that help protect QuickBooks customers’ sensitive information.
Refer: Intuit's News: Changes to the Integrated Application Authentication for QuickBooks Desktop users


As a result of this effort, Intuit is delivering an update that impacts third-party applications that connect to QuickBooks using the QuickBooks SDK.

A new file specific to a Windows user on a given machine is created on user login that provides access to an SDK app in QuickBooks.

The implementation of this update will require customers to re-enter their login credentials & authorize the application(i.e., QODBC) at QuickBooks.

For Example, you have four Windows users. All four Windows users use QODBC via Microsoft Excel, Access, Crystal, or other applications. You will need to authorize EACH application from all 4 Windows users. That means you will need to log in to the 1st Windows user, endorse all the applications that will be using QODBC, then log in to the 2nd Windows user and authorize all the applications that will be using QODBC. This is because Intuit's LGB file, which was common earlier, has not changed to a new method, and integration information will be stored for each Windows user separately.

The above is applicable for a Single machine, Terminal Services(Remote desktop connection), or Workstation(s).

Problem Description:

Get the following errors from the QODBC driver:

ERROR [00000] [QODBC] QuickBooks Begin Session Failed. Error = 8004041d,80040418. This application cannot log into this QuickBooks company data file automatically. The QuickBooks administrator can grant permission for automatic login through the Integrated Application preferences.

Test connection to QuickBooks - FAILED: "[QODBC] QuickBooks Begin Session Failed. Error = 8004041a. This application does not have permission to access this QuickBooks company data file. The QuickBooks administrator can grant access permission through the Integrated Application preferences."

Solution 1:

Step 1: Launch QuickBooks and Open the Company File

Open QuickBooks Desktop.

Log in to the desired company file using the QuickBooks user "Admin".

Log in to the QuickBooks user "admin" is required to authorize the connection to the QuickBooks company file.

This is a one-time activity for each new application you want to connect to QuickBooks Data.

Step 2: Test connection using QODBC 32-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 32-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

QuickBooks may prompt you to authorize access: (optional)

If prompted, choose “Yes, always; allow access even if QuickBooks is not running”.

Select the Admin user from the dropdown (if applicable).

Click Continue → Done.

If the connection is successful, you can optionally run a test query (e.g., SELECT * FROM Customer).

Click "Disconnect" to close the connection.

Step 3: Test connection using QODBC 64-Bit Test Tool

Go to Start → QODBC Driver for QuickBooks → QODBC 64-Bit Test Tool.

In the QODBC Test Tool, 

Select DSN: QuickBooks Data

Click the "Connect" button.

This should successfully establish a 64-bit connection. You may not be prompted for permission, as it has been granted in Step 2.

Once confirmed, click "OK" or "Disconnect" to close the session.

Step 4: Connect Using Your Application

In your application (e.g., PHP, .NET, Excel, Access, etc.), use the DSN name "QuickBooks Data" to establish a connection.

QuickBooks may prompt you to authorize access: (optional)

If QuickBooks does not ask for authorization, your application might already be authorized. You can run a few SQL statements/queries to verify that the connection is successful.

If you are unable to connect, please ensure you have logged in to the QuickBooks company file as the QuickBooks user "admin."

Solution 2:

You need to follow the steps below to reauthorize QODBC for QuickBooks.
1. Log in to Windows, Open QuickBooks & log in to the company file as Admin.
2. Switch Company File to Single User Mode.

3. Connect using your application. It should pop up the Data Access Certificate on QuickBooks. Please accept the Certificate. QuickBooks issues a Certificate for each application, so you need to get the Certificate for all applications that you use with QuickBooks.

4. You must log in to each Windows user accessing QuickBooks Data using QODBC.
If Windows User/Work Station changes, you must reauthorize the application using the above steps.

If the company file has Personally Identifiable Information (PII) in QuickBooks, users must log in with a password; the SDK app will only be allowed to connect once the login has been completed for that user. 

Customers will be instructed to set up a strong password when logging in. Customers should use unique letters and numbers in a password, not essential words that can easily be found online or in the dictionary.

Problem Description:

I was able to use QODBC Online. Suddenly, I got the following error when I tried to connect to QuickBooks Online using QODBC Online. It was working properly—no known changes to Windows or QODBC. Begin Session Failed. S0000 00000 [QODBC] QuickBooks Begin Session Failed. Error - Unauthorized-401 - - Fault Exception of type: AUTHENTICATION has been generated.

Solution:

The authorized access token for QuickBooks Online will remain active for 180 days. A privileged access token expires after 180 days. When this occurs, responses to calls to QuickBooks Data Services and the QuickBooks Online API indicate that the access token is invalid.

So, you need to reauthorize/reconnect QODBC Online at QuickBooks Online to resolve the issue.

Please refer to How to Reconnect QODBC Online with QuickBooks Online Edition.

Please follow the steps below.

1) "Disconnect from QuickBooks" 32-Bit.

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

2) "Disconnect from QuickBooks" 64-Bit.
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 64-bit >> Click "configure" >> Switch to "General" tab >>
Click the "Disconnect from QuickBooks" button.

3) Close all the applications.

4) Connect to QuickBooks:

Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure" >> Switch to "General" tab >>
Click "Connect to QuickBooks"

Please refer to https://qodbc.com/links/2659

5) Use the QODBC Online 32-Bit Test Tool to test the connectivity.


6) Export the connection key from "QuickBooks Online Data" DSN of type 32-bit 

Please use the DSN "QuickBooks Online Data" of type 32-bit to export the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 32-bit QODBC Online Data Source>> Go To "System DSN" Tab>> Select "QuickBooks Online Data" of type 32-bit >> Click "configure">> Switch to "General" tab >> Click on "Export Connection key" button to the Export key.

7) Import the connection key from the above step to the 64-bit DSN.

Please use the DSN "QuickBooks Online Data" of type 64-bit using 64-Bit ODBC Data Source.
You can import the connection key:
Start>>All Programs>> QODBC Driver for QuickBooks Online>> Configure 64-bit QODBC Online Data Source>> Go To "System DSN" Tab>> "QuickBooks Online Data" of type 64-bit >> Click "configure">> Switch to General tab, click on "Import Connection key" button.

Refer to the knowledgebase article at https://qodbc.com/links/2658 Section "Import / Export connection key:"

8) Use the QODBC Online 64-Bit Test Tool to test the connectivity.

Tags: QuickBooks Online, QBO, Authorization, Authentication, 401, Begin Session Failed

Problem Description:

I have a pass-through query for access:

Select * from Item

The connection string is as follows:

ODBC;DRIVER={QODBC Driver for QuickBooks};SERVER=QODBC;DFQ=x:\QuickBooks\MyQBcompanyfile.qbw;OpenMode=F;OLE DB Services=-2;OptimizerOn=No;

This works on pc #1 if the QuickBooks file is open OR closed (desired behavior).

This ONLY works on pc #2 if the QuickBooks File is closed.

Why is it operating differently?? I am using the same access file on both pc.

I need this to be running on some computers in the office. Some computers are allowed to use QuickBooks, and some are not, so I need a flexible DSN connection string that will work for both circumstances.  

Solution:

Please verify the company file path from the non-working machine. The other computer might be accessing the company file mapped via network name, i.e., \\servername\folder\ (e.g., \\QuickBooksfileserver\QuickBooks\MyQuickBookscompanyfile.qbw ) when the connection string is using a mapped drive, i.e., x:\

Please start QuickBooks from the non-working machine, log in to the company file, press F2, verify if the file path is the same in the connection string & test again.

Problem Description:

I am using MS Access. I have the integrated application certificate set up correctly (see screenshot). The username for this certificate is "odbc."



QuickBooks is closed.

Ms Access file connects to QuickBooks via the connection string:

"ODBC;Driver={QODBC Driver for QuickBooks};DFQ=D:\QuickBooks\MyCompanyFile.qbw;OptimizerOn=No;IAppAutomaticLogin=n;OpenMode=F;OLE DB Services=-2;"

Connection works.

Another user tries to log in as "Admin" into QuickBooks on another computer, but it fails and says already logged in. See screenshot.



Why is QODBC using the "Admin" account?? I was expecting it to use the "odbc" account.

Solution:

You need to remove QODBC integrated application entries in QuickBooks & companyfilename. lgb and delete it to resolve the issue.

Please refer to Connection Lost Between QODBC and QuickBooks. QuickBooks Asking for Password for more details.

Problem Description:

Could someone kindly provide me with a sample, or is there a stored procedure that would allow me to only pull out back-ordered invoices from the invoice table?

If a customer orders some items, we create an invoice. Then, if some things are back-ordered, when the packing slip is made, it shows they were not sent out, and that item is back-ordered. I was looking for a query or an SP that would lead me to only invoices that have not been completely shipped, or all the items in backorder. 

Solution:

All you need to do is use the ItemInventory table to pick up the quantity on hand and compare it with the ordered quantity ordered and invoiced to date (invoiced or not), and call the column "Qty to Pick."

Something along the lines of:

SELECT SalesOrderLine.SalesOrderLIneItemRefFullName as Type, SalesOrderLine.TxnDate as Date, SalesOrderLine.DueDate, SalesOrderLine.RefNumber as Num, SalesOrderLine.CustomerRefFullName as Name, SalesOrderLine.SalesOrderLineQuantity as "Qty Ordered," SalesOrderLine.SalesOrderLineInvoiced as "Qty Invoiced," (SalesOrderLine.SalesOrderLineQuantity - SalesOrderLine.SalesOrderLineInvoiced) as "Qty to Pick," ItemInventory.QuantityOnHand as "Qty Available," ItemInventory.QuantityOnOrder as "Qty on Back Order" FROM SalesOrderLine, ItemInventory where SalesOrderLine.SalesOrderLineItemRefListID = ItemInventory.ListID and SalesOrderLine.IsFullyInvoiced = FALSE and SalesOrderLine.SalesOrderLineInvoiced < SalesOrderLine.SalesOrderLineQuantity

Problem Description:

All our products are Inventory Assemblies. I am using MS Access connected to QuickBooks via QODBC. I am using queries in MS Access to load/synchronize Inventory data from my master inventory list, which is kept outside of QuickBooks, to make sure QuickBooks has the latest inventory data.

I have successfully inserted ItemInventoryAssemblyLine entries, but now I'm trying to update the Quantity information on parts for existing assemblies.

To do the update, I need to join a local table I've created from the external inventory data, which I've loaded with the ListID, ItemInventoryAssemblyLnItemInventoryRefListID, and FQPrimaryKey values from ItemInventoryAssemblyLine, in order to facilitate joins.

Here's my SQL (from MS Access Query Designer):

UPDATE ItemInventoryAssemblyLine INNER JOIN Assemblies_with_PK ON ItemInventoryAssemblyLine.FQPrimaryKey = Assemblies_with_PK.FQPrimaryKey SET ItemInventoryAssemblyLine.ItemInventoryAssemblyLnQuantity = [Qty];

"Qty" is a column in the local table with the updated Quantity that I'd like to edit into the QuickBooks table.

When I run this query, I get the dreaded "Error parsing complete XML return string." error. I have tried alternatively joining on the combination of ListID and ItemInventoryAssemblyLnItemInventoryRefListID instead of FQPrimaryKey with the same result.

I have updated Custom Fields in the parent ItemInventoryAssembly table using the same approach with no problems.

Any help with this would be greatly appreciated! 

Solution:

Please update your QODBC to the latest QODBC version and test again.

Please click here to get the latest QODBC version.

If you are still getting the same error after updating the version, I kindly request you to please raise a support ticket to the QODBC Technical Support department by clicking here.

Problem Description:

How do I share/invite the FLEXquarters Support team to access my QBO company file? 

Solution:

Please follow the steps below to share/invite the FLEXquarters Support team to the QuickBooks Online company file.

1. You can share your QuickBooks Online company file connection key by exporting the connection key.

Please refer to the Import / Export connection key section for more details

2. Email the connection key to the FLEXquarters Support team.

3. Log in to https://qbo.intuit.com/ using your credentials.

4. Click on Company Name & Manage Users.

5. Go to the Manage Users section & click on New.

6. Choose user type, select Regular or custom user & click Next.

7. Set the user's access rights, select All & click Next.

8. Set the user's administrative rights. Select User management to "No," Company Information to "View Only," Subscription & Billing to "View subscription information," & click Next.

9. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

10. QODBC Support is listed under the Manage Users list as an Invited status.

11. Once we accept the invitation, the status will change to Active.

12. You can delete an invited user by selecting the user & click on Delete.

13. By clicking Invite Accountant, you can share/invite the FLEXquarters Support team to access the QuickBooks Online company file from the Accounting Firms section.

14. Enter the FLEXquarters Support team Email address support@qodbc.com, First Name to "QODBC," the Last Name to "Support," & click Next.

Click Finish & you're done.

15. QODBC Support is listed under the Accounting Firms list as an Invited status.

16. Once we accept the invitation, the status will change to Active.

17. You can delete an invited user by selecting the user & click on Delete.

Tags: QuickBooks Online, QBO, QODBC Online

For MS Access 64-bit, please refer to: How to Configure QODBC to Work With 64-bit MS Access

Set up QODBC to use with MS Excel 2016

Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please make sure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

Before starting, please make sure you have the 64-bit version of MS Excel:

First, please select the "Data" tab, navigate to  "From Other Sources"->" From Microsoft Query."

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.

'QuickBooks Data 64-bit QRemote' as an example:

NOTE: If you are using QODBC Online, please select "QuickBooks Online Data 64-bit" instead of "QuickBooks Data 64-bit QRemote".

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for this application:

Select the "Yes, always" option and click the "Continue" button. In the next window, click the "Done" button to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by the Query Wizard. In the Choose Columns window, select a table and the columns you want to import. Choose the table you wish to import and select the columns from the table to import by pressing the ">" button. (Here, I choose the table Customer as a sample.) You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to set any query, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, where you can view and edit these records, but the data is static. The modification will not be uploaded to QuickBooks.

You can view the detailed connection properties by clicking the "Queries & Connections" button in the Data menu:

In the Workbook Connection Window, Right-click on the connection name & click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query:

This method will lead you to the MS Query window, and you can view or edit data via SQL Statement.

Click the "SQL" button to view the SQL Statement of this query:

The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

The desired record set contains only the records with a Name having a value equaling "Abercrombie, Kristy." Select the field header name and click on it, followed by selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button, and from the list of values, select "Abercrombie, Kristy" -> OK -> Add.

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

When you finish the query, choose to save so that next time, you can directly use the same question:

Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

     Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Sample Files: 

Excel - Customer & Company Tables - Download

Excel - Majority of the Tables and Reports - Download

Also, Refer:
How to Use QODBC with MS Excel 2007
Using QuickBooks Data with Excel 2013 32-bit

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016, Microsoft Excel 2013 blank window

Please follow the steps to remove QODBC-related certificates from the Integrated Application Under QuickBooks POS:

Try removing all the integrated application entries in QuickBooks POS again via File Menu -> Preferences -> Company -> Integrated Applications Tab.

Switch to Integrated Applications

To know which application uses "QODBC POS" to communicate with QuickBooks POS.

You can check it from the Application ID & File Name.

To Remove, Select "QODBCPOS-APPLICATION," For Example, "QODBCPOS-MSACCESS.EXE," Click Remove and Save.

( If there are Multiple "QODBCPOS-APPLICATION" Listed, you may remove a single Integration at a time.)

Exit QuickBooks POS.

Please start QuickBooks POS and log in again to your Company file.

Problem Description:

I need to use Between, in a range of dates, to return new customers in one period. I try this:

SELECT FullName FROM Customer Where TimeCreated Between {d'2007-12-18'} AND {d'2007-12-19'} AND SalesRepRefFullName='PD'

Is the use of QODBC correct? 

Solution:

The use of BETWEEN here is fine. It's just TimeCreated is a TIMESTAMP, not a date. It should be:

SELECT FullName FROM Customer Where TimeCreated Between {ts'2007-12-18 00:00:00.000'} AND {ts'2007-12-19 00:00:00.000'} AND SalesRepRefFullName='PD'

Problem Description:

I am using the following SQL statement:

sp_report JobProfitabilityDetail show AmountActualCost_Title, AmountActualRevenue_Title, AmountDifferenceActual_Title, Text, Label, AmountActualCost, AmountActualRevenue, AmountDifferenceActual parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' , EntityFilterFullNameWithChildren = '{?customer}'

I then create a customer parameter in the parameter list. The customer name is pretty long, so is there a way to search by using a wildcard, i.e., *50-126*, rather than having to enter:

Customer name. location.50-126.projectdescription??

Solution:

Sorry, you can't use wildcards, and you need to use the full name path for a specific job:

sp_report JobProfitabilityDetail parameters DateMacro = 'All', EntityFilterFullNameWithChildren = 'Abercrombie, Kristy:Remodel Bathroom', SummarizeColumnsBy = 'TotalOnly'

If you wanted, for example, to compare all Remodel Bathroom (or 50-126) jobs for all customers, you would need to run the Job Profitability detail report for each customer to a data file (like an append query in MS Access) and report from the data file instead.

Problem Description:

I've explored the related fields, but cannot determine how a line on the invoice is linked to the estimate.

I'm designing a report that needs to see the item on the estimate and how much has been billed out and retained for each item (as well as other related data). 

Solution:

Please refer to How to convert a Sales Order into an Invoice for the basics of how an Estimate can be made into a Sales Order and an Invoice.

The InvoiceLinkedTxn table shows Payments, Credit Memos, and Deposit Line Items, but doesn't include reimbursements, transfers from Sales Orders, or Estimates (depending on your QuickBooks version). But in QuickBooks, you probably understand it better as the History of an Invoice. The number of InvoiceLinkedTxn lines to Invoice lines won't match where multiple payments or deposits have been made.

To better understand, refer to: How to use InvoiceLinkedTxn query using QODBC.

While the EstimateLinkedTxn table shows all the transfers to Invoice or Sales Order; but does NOT include any transfers to PO.

Problem Description:

I created a report using a custom set with the customer as the parent and InvoiceLine as the child, giving me items ordered in the past.

My problem is that I only want the last time an item was ordered and want to eliminate some things, such as shipping.

When I try to filter out items like shipping, it also takes out any invoice line items.

I'm at a complete loss when it comes to getting only the last time an item was ordered.  

Solution:

You can get items ordered by a customer, in part name order, with the last date ordered first,t by using the below query:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian: Kitchen' and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc



Or for the last time an item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance' Group by InvoiceLineItemRefFullName, TxnDate Order by InvoiceLineItemRefFullName, TxnDate desc

Problem Description:

We need to bill all payroll costs to various customers. Payroll costs are defined as all of the following:

1. Employee's gross salary
2. Company share of social security
3. Company share of Medicare
4. Federal unemployment taxes paid
5. Texas unemployment taxes paid
When a payroll run is processed, all the above costs are coded to the appropriate customer, and QuickBooks flags them as "billable" at this point.

To bill the payroll costs, we go to the Customers menu and select "Invoice for Time and Expenses." All employee costs outlined above are then brought up in a list. We check the items we want to bill (all of them) and then select "create an invoice."

The above five items are listed separately when the invoice is created, but valuable descriptions are not displayed. We need the invoice to record the employee's name and the nature of the expense associated with each cost being billed out. E.g.

John Doe Gross Salary $500
John Doe Company Social Security $25
Etcetera
We have tried using various invoice forms, and these fields are never available to select for inclusion.

We can see the fields needed in the payroll module by viewing the details of the employee check. However, we have not been successful in finding these fields in the QuickBooks tables so that we can pull them into the invoice using your software. If I can see the data in the tables, I am confident I can extract it using QODBC and SQL to format it into a suitable report.

Thanks in advance for any help.

Solution:

You can get these details from the PayrollTransactionDetail report:

For this example, PayCheck:



You can find all the details using the following:

sp_report PayrollTransactionDetail show PayrollItem, WageBase, Credit, Debit, Amount parameters DateFrom = {d'2007-12-01'}, DateTo={d'2007-12-01'}, IncludeAccounts = 'All,' ReportDetailLevelFilter = 'AllExceptSummary,' EntityFilterFullNameWithChildren='Dan T. Miller' Where PayrollItem is not NULL

Change the bold entries to the employee and paycheck date you're after

Problem Description 1:

I am using QuickBooks Enterprise with Advanced Inventory enabled.

I am trying to find where the bin location for items is stored in QuickBooks through QODBC.

Problem Description 2:

We are looking for the following tables in QuickBooks Enterprise Edition 16 via QODBC (Linked Server) SQL server, with no luck:

We can find the table for the Inventory Site, but there is no distinction for BIN. They are all listed equally in the table. (tbl = InventorySites)

Solution:

You can find the inventory site list & location below the tables:

BillItemLine, PurchaseOrderLine, InvoiceLine, SalesOrderLine, CheckItemLine, CreditCardCreditItemLine, CreditMemoLine, SalesReceiptLine, CheckItemLine, CreditCardChargeItemLine, BuildAssembly, BuildAssemblyComponentItemLine, TransferInventoryLine, InventoryAdjustment, InventoryAdjustmentLine & ItemSites.

Problem Description:

I have one QuickBooks Online login, which provides access to multiple QuickBooks Online companies.

When I initially configured the connection, I selected one of the clients to which I have access (Company A). The connection worked great, and I got the data I needed. Now I want to export data for another client of mine (Company B), but I'm unsure how to reconfigure it to pull from their database, not Company A's. 

Solution:

You can create a new DSN company-wise & authorize your company file, and use it in your configuration.

For Example, create a new DSN for Company A & use Company A's DSN for accessing Company A's data.

In the same way, you can create a DSN for Company B & use Company B's DSN to access Company B's data.

You can repeat this for multiple company files.

Please refer to the example below, which shows creating a QODBC Online DSN company-wise.

Launch QODBC Online Data Source Configuration

      Locate the QODBC Driver for the QuickBooks Online program group on your desktop. Click on the Configure QODBC Online Data Source icon to create or configure a QODBC Online Data Source Name (DSN).

Create a New DSN

      Click on the System DSN tab to create a DSN that both Windows applications and services can call. Click on the Add button to create a new DSN.

       Select the QODBC Driver for QuickBooks Online option and click on the Finish button.

Configure the Created DSN

      Enter the Data Source Name as CompanyA and click Apply.

Click Connect To QuickBooks

You need to authorize your company file to work with QODBC Online.

During the authorization, select your Company file. 

For more details, please refer to How to Connect QODBC Online with QuickBooks Online Edition.

Once connected, you can use Excel, Access, or QODBC Online Test Tool or any other application, and use "CompanyA" instead of "QuickBooks Online Data." You should be able to connect and fetch data from QuickBooks Online.

You can repeat the above steps for another company file (i.e., CompanyB, CompanyC, et.).

Tags: QuickBooks Online, QBO, Configure online DSN, QODBC Online, connect company files, multiple

Information:

QuickBooks allows users to put foreign (i.e., Chinese, Japanese, etc) characters into item descriptions, etc. Unfortunately, these characters are not appropriately returned in QBXML responses (and, therefore, QODBC responses since they're based on Intuit's QBXML SDK)

The problem is two-fold. The Intuit code wasn't written to handle Unicode characters at the SDK level, so any characters QuickBooks returns to the SDK level aren't appropriately interpreted. Secondly, the parser used by the SDK runtime in QuickBooks doesn't support some of the character sets for foreign characters, so even if the underlying SDK runtime code handled Unicode characters, the SDK parser wouldn't. That means one cannot pass foreign characters into QuickBooks through the SDK to QODBC.

Problem Description:

I want to list the subtotal of all Invoices by a customer for not only that particular customer but all jobs assigned to that customer within a specified date range.

What QODBC SQL Statement would I use?

This is what I have so far:

SELECT SUM(Subtotal) AS gt FROM Invoice WHERE CustomerRefListId='30000-1166980720' AND (TxnDate>={d'2006-12-24'} AND TxnDate<={d'2007-01-23'})

But that will only get me the subtotal of invoices for the primary customer, but it doesn't get me the combined subtotal of all the invoices for each job under that customer.

Is there a way to combine it all into one SQL Statement? Or do I have to use multiple statements and then manually combine all the totals?

Solution:

You can use the query below to get the Subtotal of Invoices Within the Date Range using QODBC.

You can run this query:

SELECT Invoice.CustomerRefFullName, Sum(Invoice.SubTotal) AS SumOfSubTotal FROM Invoice WHERE (((Invoice.TxnDate)>={d'2007-01-01'})) and (((Invoice.TxnDate)<{d'2007-12-01'})) GROUP BY Invoice.CustomerRefFullName



You can change the greater than or equal (>=) and the less than (<) dates to the date range you wish to use.


If you want to get the SubTotal of all Invoices for Customer 2 and all the Customer 2 Jobs combined, I suggest you go straight to the QuickBooks reporting engine instead, which will add up and total things for you (including any credits). QuickBooks reports can be accessed using the QODBC stored report procedures:

sp_report SalesByCustomerSummary show Text as Customer, Label as Job, Amount_1 as Total parameters DateFrom = {d'2007-01-01'}, DateTo= {d'2007-12-01'}, SummarizeColumnsBy = 'TotalOnly'

Problem Description:

I am trying to do some testing and now have access to a QuickBooks Online Sandbox company. I have tried to connect to it in two different ways.

1. If I "Connect to QuickBooks" on the QODBC Driver for QuickBooks Online Setup Screen without checking the "Sandbox Company" checkbox, I can connect to QuickBooks Online, but cannot see any data from my QuickBooks Online Sandbox company.

2. If I "Connect to QuickBooks" on the QODBC Driver for QuickBooks Online Setup Screen with the "Sandbox Company" checkbox checked, it goes through the QuickBooks Online login screen and presents the Authorize button on the next screen. When I click on Authorize, it closes that screen and opens a new one, which also requests Authorize. When I click Authorize on that screen, too, it then reports "Connection Complete," but it never loads the Access Token, and the "Connect to QuickBooks" button does not toggle to "Disconnect from QuickBooks." When I try to access my Sandbox Company tables using VBDemo, I get the error message shown in the attached file named "Error Message."

I am getting the following error:





Also, I have noticed the following entries in the QODBCOL.log files:

Begin Session Failed. S0000 00000 [QODBC] QuickBooks Begin Session Failed. 658 - Access Token Not Found. Go to [QODBC Online Setup Screen] and click [Test Connection to QuickBooks Online]. Please authorize QODBC Online to connect to your QuickBooks Online Company.

Please advise on the correct process to connect to the Sandbox Company from QODBC.

Solution:

There might be some issues with Intuit's authorization service. Please wait for 30 minutes and try again. You should be able to connect to the Sandbox company file.

Tags: QuickBooks Online, QBO, Sandbox, QODBC Online

Problem Description:

We recently upgraded from QODBC 14 to 16.00.00.313. It is functioning normally, except now the QuickBooks window is opening up when it didn't used to. In version 14, it didn't open up QuickBooks while being accessed. Now it does. Is there a way to change this behavior with a setting?

Solution:

Please disable the force start QuickBooks option from the DSN you are using.

Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" or "User DSN " Tab, whatever you are using > your DSN >> click "configure">> switch to Advanced>> disable Force Start QuickBooks option and try again.

Problem Description:

Does your enterprise driver support the advanced inventory module for QuickBooks Enterprise? Would we be able to query stock per item, per location? 

Solution:

You can access the Advanced Inventory data from the QuickBooks enterprise add-on.

Yes, the site and location field is not available in ItemInventory & other Item tables through the Intuit SDK.

You can find the inventory site list & location below the tables:

BillItemLine, PurchaseOrderLine, InvoiceLine, SalesOrderLine, CheckItemLine, CreditCardCreditItemLine, CreditMemoLine, SalesReceiptLine, CheckItemLine, CreditCardChargeItemLine, BuildAssembly, BuildAssemblyComponentItemLine, TransferInventory, TransferInventoryLine, InventoryAdjustment, InventoryAdjustmentLine, ItemSite

Problem Description:

I am using QRemote DSN to connect to QuickBooks. QODBC/QRemote tries to connect for 2 minutes over and over and says it has a connection, but does nothing. I tried the QODBC Test Tool program, but it just freezes.

Solution:

Due to this issue, there might be some issues with the QRemote configuration.

Please verify QRemote Server & QRemote Client DSN are configured correctly by referring to the link below & testing again:
QRemote Configuration

Problem Description:

I am in access and need to update the Inventory. I have made a table that contains the adjustments. (Thousands of them) Your notes about InventoryAdjustmentLine state that I must be in single-user mode to do this. What is the connection string that I must use? When I connect to QuickBooks, I currently log in as an administrator and auto-connect to access.  

Solution:

QODBC uses the QuickBooks SDK to communicate with QuickBooks Company files. QODBC can perform only those operations that are available via QBSDK.

No option/function will change QuickBooks company to switch the company file to single-user or multi-user access.

Yes, if no one is using the QuickBooks company file, you may connect to the QuickBooks Company file in "Single user mode" or "Multi-user mode."

You may either use the Connection string or specify OpenMode = F (Follow Company) / M (Multi-user) / S (Single-user), or change the Open File Mode from the QODBC Setup Screen.

For the connection string, please refer to How do I build a connection string with all the available QODBC parameters / Connection String with QODBC Parameters.

How to insert employee time information from an MS Excel time spreadsheet into QuickBooks using QODBC

Solution:

You cannot perform insert/update operations directly in MS Excel because Excel is just exporting tables in the spreadsheet. Excel does not create link tables like MS Access. If you want to insert/update using MS Excel, then you need to use MS Query, which will execute the statement you enter in the editor, or you can write VBA code in Excel.

Please refer to the article for how to write SQL statements using MS Query: Microsoft Office with MS Query and QODBC.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

You can use ADO in VB to connect to QuickBooks via VBA in MS Excel.

Please refer to the sample code for inserting records through VBA:

Sub test()
Const adopt static = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "INSERT INTO TimeTracking (EntityRefListID, DurationMinutes, TxnDate, CustomerRefFullName, ItemServiceRefFullName, PayrollItemWageRefFullName) VALUES ( '370000-933272659', 480, {d'2007-12-18'}, 'Pretell Real Estate:75 Sunset Rd.', 'Removal,' 'Salary')."
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg
Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Please note: All query values are coming from sample company files. Please change it according to your company file value before using it.

Please click here to download a sample MS Excel file.

Please refer to How to use the QODBC Test Tool for testing

Problem Description:

VB Demo\QODBC Test Tool\Test Connection was crashing when I tried to connect to QuickBooks using QODBC.

I have tried reinstalling QODBC & QuickBooks, but the QODBC Test connection (also via QODBC Test Tool) is still crashing. Also, I tried to run QODBC as an Admin/Non-Admin user, QODBC Test Tool, but with no success.

Solution:

If you are running QODBC with Detail Trace-enabled mode, please check the log file size (Detail Trace, QODBC Messages, SDK Messages).

It should not be more than 400 MB.

Please clear all the log files & Optimizer files.

Then, please try to test the Connection through the VB Demo on the sample company file.

We recommended clearing log files when the file size exceeds 100 MB for better performance.

Problem:

There are multiple company files in QuickBooks. At a time, I can access only one company file. Does QODBC allow me to access multiple company files at the same time?

Solution:

To access data from multiple QuickBooks company files, you must configure a dedicated Data Source Name (DSN) for each individual file. Each DSN should be configured to handle auto-login for its respective company file. For detailed setup steps, please refer to the guide: How to set up QODBC for auto-login into QuickBooks.

To switch between different company files, simply update your application's connection string to point to the specific DSN associated with that company file.

Please refer to How to set up QODBC for auto-login into QuickBooks.

Step 1: Granting QODBC Access Permissions

Before attempting automated data extraction, QODBC must be granted permission by each QuickBooks company file. This configuration is a one-time requirement per company file.

Follow these steps for each file:

1. Open QuickBooks and log into the target company file as a QuickBooks user Admin in Single-User Mode.

2. Connect to the company file using the QODBC Test Tool or your integrated user application.

3. A QuickBooks integrated application certificate prompt will appear.

4. Accept the certificate and grant the necessary permissions to allow QODBC to access the data.

Step 2: Extracting and Consolidating Data

Once all certificates are accepted, you can proceed with data extraction:

1. Use an application such as Microsoft Excel Or Access, or your custom application to connect to each DSN sequentially.

2. Export/dump the required data for the company.

3. Close the connection, and wait for 15 seconds before connecting to the next company file.

Important Technical Limitations

⚠️ QuickBooks SDK Connection Constraint The QuickBooks SDK can only connect to one company file at a time. It does not support concurrent connections to multiple company files.

Because simultaneous access is not supported, your data extraction workflow must strictly follow a sequential connection process. You must completely close the connection to the active company file before opening a connection to the next one.

• Required Delay: To ensure QuickBooks completely releases the previous session, implement a 15 to 30-second delay (gap) between closing Connection A and opening Connection B.

Potential Error Messages

If an application attempts to access multiple company files concurrently, or if a new connection is initiated too quickly without the required delay, the system will return the following error:

If the user tries to access multiple company files at the same time, then you will receive the error:

As a workaround, you can use multiple machines, each with QuickBooks and QODBC installed. Each machine will have a different QuickBooks company file opened in the QuickBooks application. The application machine uses QRemote Client (different DSNs) to connect to those machines and access the QuickBooks company file data simultaneously. 

For Example:
On Application Machine.
QRemote Client DSN A --> Points to the IP Address of Machine A --> Machine A will have QuickBooks company file A opened.

QRemote Client DSN B --> Points to the IP Address of Machine B --> Machine B will have QuickBooks company file B opened.

QRemote Client DSN C --> Points to the IP Address of Machine C --> Machine C will have QuickBooks company file C opened.

Also, Refer to:
How to create or configure a QRemote DSN

 Keywords: QB win log with multiple companies, log in with various companies, multi-instance, Secondary QuickBooks, multiple company files.

Problem Description 1:

My application needs to read data from QuickBooks and update data in the SQL server. Up to this, I can handle everything. But I need a solution for the task scheduler. My need is that my application needs to run every hour through the task scheduler. I'm trying this, but it is giving the error message:

QuickBooks Begin Session Failed. Error = 80040408, Could not start QuickBooks.

Problem Description 2:

I have a batch file that runs a query using the QODBC driver. It ran perfectly when I logged in, but when I ran it as a scheduled task, I got this error:

Unable to connect to QuickBooks Connection String: DSN=BPCQuickBooks _PROD_batch; DFQ=; OpenMode=F; SERVER=QODBC, OLE Automation Error. The source is Microsoft OLE DB Provider for ODBC Drivers." [QODBC] Server Edition of QODBC is required to use the DCOM Servers."

SQLConnect [QODBC] QuickBooks Begin Session Failed. Error = 8004041c, An internal QuickBooks error occurred while accessing the QuickBooks company data file. 

Problem Description 3:

There is a batch script that runs the data extraction, and it works when it is run manually, and we can generate a CSV file. However, when the batch script is scheduled to run using the task scheduler, it gives an error message 8004041d. We have followed all the solutions in the article, and we're still running into the error message when the task runs during the scheduled time in the task scheduler. We can also manually run the task in the task scheduler without an error, so it is only during the scheduled times that it gives an error, Error ID 8004041d

Solution : 

Windows Scheduler has changed from desktop-based to service-based from Vista or Windows 7 onwards, and the new OS has the Scheduler as a service-based. 

QODBC is able to access data from QuickBooks using the QuickBooks SDK, which is an embedded feature of the QuickBooks Desktop Application.

The QuickBooks SDK requires an active desktop session (someone to be logged in) to the machine because QuickBooks is a desktop-based application.

If no one logs on to the server/machine, you will get the error "Could not start QuickBooks."

Even if you have specified the username/password and have a user logged into the machine, you may notice the same error. This is because the task executed from Task Scheduler runs as a different user / new session.

The workaround is to use a Desktop-based Based Scheduler like QScheduler https://qxl1.com/qschedule,  or http://alternativeto.net/software/task-scheduler/?platform=windows. 

How to use QScheduler with QODBC for repetitive or scheduled jobs

You can download QScheduler from the mentioned link:

Download QScheduler

Please refer to the following article for how to install QScheduler - https://qxl1.com/links/3047

After downloading QScheduler, please install it on your machine.

How to use QScheduler - https://qxl1.com/links/3059

Add a new task using the "Add" button.

Enter the task title in the "Task Name", and click the "Browse" icon to select the application.

Input the start date of the task and the frequency.

Select the file that you want to schedule & click "OPEN." In this example, I am selecting the VBS script.

Review the task details and click "Close".

The task will be added to the task list and will be executed.

Windows Task Scheduler (Troubleshooting)

If your company policy requires you to use the default Windows Task Scheduler, you may try the following option to see if that helps.

Error 8004041d means QuickBooks/SDK rejected the automatic login: “This application is not allowed to log into this QuickBooks company data file automatically.” In practice, this commonly happens when a scheduled task runs in a different Windows session, under a different account, or without an interactive desktop - QuickBooks (and the QuickBooks SDK) require an interactive desktop session / approved integrated application certificate for the exact user/session that is doing the BeginSession.

QuickBooks and QuickBooks SDK are not designed to run as a service.
Someone should be logged in to the Windows user session.

Run Task only when user is logged on (Interactive session), Do not use "Run with highest privileges."

Edit the scheduled task → General tab → select Run only when user is logged on (not “Run whether user is logged on or not”).

Rationale: QuickBooks and the QuickBooks SDK require an interactive desktop. If the task is started in a non-interactive session (session 0) or as SYSTEM, it can’t establish a QuickBooks SDK BeginSession. Many users fix this by keeping a user logged into the server and setting the task to run under that same user.


Use the same Windows account that was used when authorizing QODB.C

The Integrated Application permission in QuickBooks is tied to the Windows user and the certificate. Make sure the scheduled task runs under the same Windows user account that you manually used when authorizing QODBC in QuickBooks. If they differ, re-authorize while logged in as the scheduled task user or change the task to run as the authorized user.



Confirm QODBC was allowed automatic login in QuickBooks Integrated Applications

In QuickBooks: Edit → Preferences → Integrated Applications → Company Preferences → find QODBC (or the exact application name shown during authorization) and ensure “Allow this application to log in automatically” is checked and the access level is appropriate (or explicitly select “Yes, always; allow access even if QuickBooks is not running” if available for your version). If you do not see the app, re-run the QODBC setup/support wizard and accept the certificate when QuickBooks prompts.


Make sure the QuickBooks company file is open, and QuickBooks is running in the interactive session.

For scheduled runs, quick test: log into the server interactively as the task user, open QuickBooks and the company file, then trigger the scheduled task manually (from Task Scheduler while still logged in) - if that works, but the scheduled run (when no interactive login) fails, it confirms the interactive-session issue.

Problem Description:

I have a question about setting ODBC to auto-login. I am trying to automate some tasks and wanted to auto-login. We have two different company files for accounting purposes. If I set the path in the odbc to the company file, how do I change this to access the second company?

Solutions:

There is a limitation with the QuickBooks SDK: it can connect to only one company file simultaneously on a machine. QODBC uses QuickBooks SDK & QRemoteServer uses QODBC. Thus, you can open one company file at a time on a device.

You can create multiple DSNs pointing to different company files and use this DSN in your configuration.

Please refer to the steps below for creating multiple DSNs pointing to different company files.

1) Create a new DSN.

For example, you have a Company file "A," and let us create a DSN for Company "A" with the DSN name "CompA." Please refer to the article mentioned below for creating DSN:

How to make or configure a QODBC DSN

2) Set up auto login to QuickBooks using QODBC.

Please refer to the article mentioned below for configuring auto-login to QuickBooks using QODBC.

How to set up QODBC Driver to auto-connect to QuickBooks (Video).

How to set up QODBC for auto-login into QuickBooks.

Let us repeat steps 1 & 2 for the Company file "B."

1) Create a new DSN.

Create DSN for Company "B" with DSN name "CompB." Please refer to the article mentioned below for creating DSN:

How to make or configure a QODBC DSN

2) Set up auto login to QuickBooks using QODBC.

Please refer to the article mentioned below for configuring auto-login to QuickBooks using QODBC.

How to set up QODBC Driver to auto-connect to QuickBooks (Video).

How to set up QODBC for auto-login into QuickBooks.

Use DSN in Applications:

We have created multiple DSNs. Now we use it in our application.

In this example, I am using DSN in VBA code. Please refer to the sample code for using multiple DSNs in VBA through QODBC:

Public Sub Test()

Const adopt static = 3

Const adLockOptimistic = 3

Dim oConnection

Dim sConnectString

sConnectString = "DSN=CompA;OLE DB Services=-2;"

// Write your query here as per your requirements. //

Set oConnection = CreateObject("ADODB.Connection")

oConnection.Open sConnectString

oConnection.Close

Set oConnection = Nothing

Sleep(10)

sConnectString = "DSN=CompB;OLE DB Services=-2;"

// Write your query here as per your requirements. //

Set oConnection = CreateObject("ADODB.Connection")

oConnection.Open sConnectString

oConnection.Close

Set oConnection = Nothing

Sleep(10)

End Sub

Keywords: QB Win Log with multiple companies, log in with multiple companies 

Problem Description 1:

We are adding invoice records to QuickBooks using an Access front end. When we create the records and try to insert them into QuickBooks, the line items seem to go, but when the invoice header is inserted, we get an error that the Child Record must be inserted before the Header Record in BuildXMLHeader. I have examined the data being passed, and it all looks correct.

Problem Description 2:

I upgraded my QuickBooks Premier Plus Professional Services to 2016 (from 2014). I skipped updating to 2015.

I installed the new QODBC connector, but I cannot connect to my Access database. I have tried to uninstall the MS Access Database driver, but I get the error message ODBC call failed- Error 10004.

Problem Description 3:

I just updated QuickBooks Enterprise 14 to Enterprise 23. Using an Access database 2013 32-bit. I get an error when trying to process a Packing list in Access to invoice in QuickBooks.

I'm having an issue with my database sending a packing list to QuickBooks. It's working, and then it stops.

"ODBC--call failed".
 

Problem Description 4:

I have an MS Access database containing QuickBooks Data as the linked table. Recently, I changed my Optimizer file path & after changing the Optimizer path, I am facing a slow performance issue.

How can I solve this issue? 

Solutions: 

You need to re-link the MS Access-linked table to resolve the issue. Delete the old linked table & re-link it.

After changing the optimizer file path, you need to re-link the linked table in MS Access because your MS Access Linked table contains an old Optimizer file path.

Please note: Linked Table Manager in MS Access will not update the Optimizer file path; you need to re-link the linked table in MS Access to solve this issue.

The solution to this issue is to re-create the linked table.

Open MS Access Linked Table:

Right-click on the On Your Linked Table:

Now, click on the Delete.

Now Link the Same Table. After linking the table, you will get the updated optimizer file path.

To view the Optimizer file path & other table properties, hover your mouse over the table name so that you can view table properties.

Please keep QuickBooks running, log in as Admin to your company file, and try to connect again with MS Access.

Please refer to the link below for How to Create a Linked Table in MS Access:

How to Use QODBC with Microsoft Access 2007

How to Use QODBC with Microsoft Access 2010

If the above does not resolve the issue, please download the latest QODBC version.

QODBC Download

Skip the CDKEY Step during installation. Restart the Machine after a new version is installed (if prompted)

If the Above Solutions don't work, please post a ticket to us, and we will further investigate your issue.

Tags: Re-link linked tables, Re-link table, Re-add table, Re-Add linked tables, Refresh Linked tables, ODBC-Connection to 'QuickBooks Data' failed, #Deleted,[#Deleted], ODBC--call failed, call failed, #Deleted

Problem Description:

I have a question about how to locate data found in QuickBooks by table and field name inside the QODBC system.

Solutions:

The fastest way to find data in QuickBooks via the QODBC Driver is to use the batch and sync method. Inside QuickBooks, place a unique "bait" value in the field in question, such as find me for text fields or 123321 for numeric fields, and save the data. Then start the QODBC Test Tool and connect to the QuickBooks company file. Refer to Connecting QODBC Driver to QuickBooks for the First Time (Video), then do a global search for the "bait" value. The file and field name will be shown if the data is available via QODBC.

Please follow the steps below to locate QuickBooks data by table and field name inside the QODBC system.

1) Add a sample find me to Invoice or bill records (i.e., RefNumber).

For Example, we are creating an Invoice with RefNumber (i.e., Invoice#) findme.


2) Add a sample 123321 in the CreditMemo rate.

For Example, we are creating a CreditMemo with a rate of 123321.


3) Use the SP_GLOBALSEARCH command to search data from the QuickBooks company file. (Requires v16.0.0.317 or above)

sp_globalsearch SearchValue

For Example:

sp_globalsearch 'find me.'


sp_globalsearch '123321'

To use QODBC with Jitterbit Community Edition 3.0.0, you should follow the steps below to configure your QODBC driver and Jitterbit connection. These steps work for QODBC with Jitterbit 3.0.0 on 32-bit Windows 7. Other platforms have not been tested.

First, you need to configure QRemote. To configure QRemote, please follow the steps below:

QRemote has two components: "QRemote Client Driver for QODBC" and "QRemote Server for QODBC." 

QRemote Client Driver: Allows service-based applications and 64-bita pplications to connect to the QRemote Server and send the request to the QRemote Server for processing. 

QRemote Server: Processes requested instruction from QRemote Client to QuickBooks using QODBC DSN Driver. 

Start "QRemote Server for QODBC" from the start menu. ( If QRemoteServer is not running ) 

Double-click the QRemote Server icon from the system tray

QRemote Server Main screen shows the IP address and port, allowing the QRemote Client to connect.

Note the IP address and Port number listed on the QRemote Server "Event Logs " window

Create a new DSN entry/or configure the existing one

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks, and click

Configure QODBC Data Source

"QuickBooks Data QRemote" is the default DSN created when installing QODBC.

Configure the DSN and provide the IP address and Port as listed under "QRemote Server."

Under the QRemote for QuickBooks – Client  DSN configuration screen, please fill in the following information. 

• Data Source Name: Name of your DSN entry 
• Server: IP address displayed on QRemote Server 
• Port: Port displayed on QRemote Server 
• Encryption Key: Keep default key "QODBC#QRemote$1" (In case you want to use encrypted data transfer between QRemote client and QRemoteServer, you can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

• Remote DSN: select the QODBC DSN from the list. ( Make sure QRemote Server is running) 

After configuring QODBC, you need to configure Jitterbit. To configure Jitterbit, please follow the steps below:

1. Start the Jitterbit client

2. Right-click Sources and create a New Source

3. Type QuickBooks in the Name field

4. Select the Database for the type

5. Click Options

6. Ensure the box marked "Construct connection string manually" has a tick in it

7. Enter DSN=QuickBooks Data QRemote into the field adjacent to the checkbox.

8. Click the Save icon.

Problem Description:

I'm a little concerned about the write feature and changing any current data in QuickBooks from the Excel files I want to experiment with. Are my concerns valid, or what is the best way for me to prevent that from occurring?

Solutions:

• Updating any record in the Excel sheet will not fire the update command or change anything on QuickBooks (unless using a VBA script). For better security, you can enable the "Read-Only Access" feature from the QODBC Advanced tab to avoid any application using QODBC to write to QuickBooks.
  
  

You can disable write permission by enabling the "Read-Only Access" options from:

Start>>All Programs>> QODBC Driver for use with QuickBooks>>QODBC Setup Screen>>Advanced Tab>> Enable the "Read-only access" option. & apply the changes.

• You can make a copy of the company file and test it. So your original company file will be safe.
  
  

OR

• You can take a backup of the company files & restore them whenever needed.

• QODBC does not write to the QuickBooks company file directly; QODBC writes through the QuickBooks SDK, which follows all business rules & validation.

Tags: configurations, permissions, user rights

Problem Description:

I have an inventory control system (C #, SQL Server) that needs read-only access to four tables in the accounting system. I have deployed your multiuser version and can access the local QuickBooks instance from your VB app, Excel, and my app.

1. When I run your VB app in the production RDP session, the ODBC behavior is as expected.

2. I created a WPF/c# app to duplicate your VB and ran it in the production RDP session. The ODBC's behavior is expected.

3. When I run my app in my development environment, the ODBC behavior is as expected.

4. When I run my app in the production RDP session, opening the ODBC connection takes > 6 minutes. But it opens.

Please let me know what I am doing wrong.

Solutions:

Please reset your Optimizer file, check the connection time, and log the connection time in your application. Please refer:

How to Remove Optimizer file

Please try the below .net application below for the troubleshooting of the time delay in connection opening and querying:

Sample code for Troubleshooting Connection Time Issue

If the above does not resolve the issue, please change the application, as it could eat more environmental resources.

Also, please try to move the process of reading the QuickBooks tables to a background task. It will solve the problem. I suspect that your app is consuming most of the thread pool. Creating an explicit task provides sufficient resources to resolve the issue.  

Problem Description 1:

I am following the steps below and getting the error.

1. First, we inserted the records in the Bill & BillExpenseLine table, and the papers got inserted successfully.

2. Second, we are inserting the records in BillPaymentCheckLine, and we are getting the following error

"Error Parsing Complete XML return string."

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, IsToBePrinted, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555',1, '81D4-1435326671', 88.3)

Please let me know what I am doing wrong.

Solutions 1:

Either remove RefNumber or IsToBePrinted from the insert statement. QuickBooks SDK will allow only one of the fields during insert/update & due to this issue occurring.

Your query should be as follows.

INSERT INTO BillPaymentCheckLine (PayeeEntityRefListID, APAccountRefListID, BankAccountRefListID, RefNumber, AppliedToTxnTxnID, AppliedToTxnPaymentAmount) VALUES('80000EB1-1435326666', '80000037-1409939589', '80000024-1409927427', '555555', '81D4-1435326671', 88.3) 

Problem Description 2:

I am trying to link BillItemLine with PurchaseOrder. I am using the below query to connect transactions & I am getting an XML error.

"Error Parsing Complete XML return string."

INSERT INTO BillItemLine ( ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity ) values ('271B-1071512692', '271D-1071512692',300 )

Please let me know what I am doing wrong.

Solutions 2:

If you are trying to link transactions with Bill, you should include vendor details in your query. Please refer to the sample query & test again:

Your question should be as follows.

INSERT INTO BillItemLine (VendorRefListID, ItemLineLinkToTxnTxnID, ItemLineLinkToTxnTxnLineID, ItemLineQuantity) VALUES ('10000-933272655', '271B-1071512692', '271D-1071512692',30)

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-2000',200.00000,200.00,'20000-999022286',1)

Note: QRemote is a linked server name. It would be best if you changed it to your linked server name.

Suppose you don't set up a linked server in SQL Server. Please refer to the link below for creating the linked server.

For 32-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 32-bit Using QODBC/QRemote

For 64-bit SQL Server: How to create Link Server with MS SQL 2005/2008/2012 64-bit Using QODBC/QRemote

Second Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-3000',200.00000,200.00,'20000-999022286',1)

Third Line

INSERT INTO QRemote...InvoiceLine(InvoiceLineItemRefListID,InvoiceLineDesc,InvoiceLineRate,InvoiceLineAmount,InvoiceLineSalesTaxCodeRefListID,FQSaveToCache) VALUES('240000-933272656','POWERTRAK-4000',200.00000,200.00,'20000-999022286',1)

Header Information

INSERT INTO QRemote...InvoiceLine(CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES('160000-933272658', '40000-933270541', {d'2014-10-01'}, '1', 'Brad Lamb,' '1921 AppleseedLane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '20000-933272658', {d'2014-10-31'}, {d'2014-10-01'}, '2E0000-933272656', 'Memo Test,' 0,'10000-999022286')

Results in QuickBooks

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '240000-933272656' and found like this:

     Select ListId, and FullName from QRemote...An item where FullName is like 'F%.'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '160000-933272658' and found like this:

     Select ListId, and FullName from QRemote...Customers where FullName like 'Aber%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, and FullName from QRemote...An account where a Name like 'Account%.'

ItemSalesTaxRefListID

ItemSalesTaxRefListID is '2E0000-933272656' and found like this:

     Select ListId, Name from QRemote...ItemSalesTax where Name like 'San%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from QRemote...SalesTaxCode where Name like 'T%'

Example of adding InvoiceLine items to an existing Invoice

You can add lines to existing Invoices using SQL Linked Server without any issue. Please refer to the sample query for the same:

Insert into QRemote.InvoiceLine (TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount) VALUES ('1B308-1513325705', '320000-1071525597', '11440DECA', 5,5)

OR

Insert openquery(QRemote, 'select TxnID, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount from InvoiceLine where TxnID=''0''') values ('1B308-1513325705','320000-1071525597', '88880DECA', 5,5)

Note: In the INSERT statement, a where should be TxnID=''0''. The TxnID=''0'' predicate is used to avoid retrieving data from the remote server, which can result in slower performance.

Table Differences:

Number of Tables available in

List of Tables available in QODBC Desktop

List of Tables available in QODBC Online

List of Tables available via API from QuickBooks Online

Report Differences:

Number of Reports available in

List of Reports available in QODBC Desktop

List of Reports available in QODBC Online

List of Reports available via API from QuickBooks Online 

Stored Procedure Difference:

Stored Procedure, which is not working in QODBC for QuickBooks Online:

QODBC Desktop Stored Procedures Command-List

Also, Refer

Detailed Difference between QODBC for QuickBooks Desktop and Online.

Tags: QuickBooks Online, QBO, QODBC Online, difference

Problem Description:

Trying to insert into SalesOrderLine and getting a 'Multiple-step' error.

INSERT INTO QBREMOTE...SalesOrderLine (CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, ClassRefListID, FQSaveToCache) VALUES ('8000343B-1393961058', '80000008-1377787801 ', 'R1', 1.0000, 500.00, '80000001-1377787801 ', '80000003-1393947522', 0)

OLE DB provider "MSDASQL" for linked server "QBREMOTE" returned the message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7343, Level 16, State 2, Line 1

The OLE DB provider "MSDASQL" for linked server "QBREMOTE" could not INSERT INTO table "[QBREMOTE]...[SalesOrderLine]".

Solutions:

You need to change the "Maximum Field Name Length" value from QODBC Setup Screen>> Advanced tab to resolve "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." at the time of insertion.

Please follow the instructions below to change the "Maximum Field Name Length" value.

Start>>All Programs>> QODBC Driver for use with QuickBooks>> Configure QODBC Data Source>>Go To "System DSN" (If you are using System DSN) or "User DSN" (If you are using User DSN) Tab >> select your DSN>> click "configure">> Advanced >> "Maximum Field Name Length" change to 100.  

Also, you can use openquery, which can insert records in the SalesOrderLine table:

Insert openquery(QBREMOTE,' select CustomerRefListID, TemplateRefListID, SalesOrderLineItemRefFullName, SalesOrderLineQuantity, SalesOrderLineRate, SalesOrderLineTaxCodeRefListID, FQSaveToCache from SalesOrderLine where 1=0') values ('160000-933272658', '120000-1071512690', 'Wood Door: Exterior', 1.0000, 555.00, '10000-999022286', 0)

Please refer:

How to do INSERTs, UPDATEs, and DELETEs using OPENQUERY with Linked MS SQL Servers

Step 1 - Download QXL

If you haven't already downloaded QXL, go to the download page now. Don't forget to read the "Readme" file during installation for the latest information on QXL setup, new features, and other driver information. Save the self-extracting executable "QXLSetup.exe" in a temporary directory or your "My Documents" folder to extract the installation files.

Step 2 - Running the Installer:

Click on the Install program from within Windows to install the driver. Select the destination directory to install the QXL support files. Click "OK" to continue.

The QXL - QuickBooks Export Made Easy® has been successfully installed on your computer. You can begin to use it because it will immediately run as a 30-day evaluation version.

Step 3 - Use QXL:

After installation, you may notice the following icons on the desktop.

QXL – Silent Mode - It uses the last saved settings of QXL and automatically starts executing the data export with no user interface.QXL – Direct Mode - It uses the previously saved settings of QXL and automatically starts executing the data export with the user interface, i.e., displaying progress data export.QXL – Manual execution of Data Export/Update Settings/Activation-Deactivation. You can select tables and destinations to which the files should be exported. Start QuickBooks, log in to your company file, switch back to QXL, and click Export Data.

After the export is completed. The exported data folder will open.

Exported data is placed at the below location by default: C:\Users\User Name\Documents\QXL\

QXL Features:

1. Export QuickBooks Data to XML format via QODBC.

2. Export QuickBooks Data to CSV format via QODBC.

3. Ability to choose export file(s) location.

4. Ability to export to Separate files or a single file.

5. Ability to export all selected, tabled, or excluded tables.

6. Ability to save your default settings from Options.

Also, Refer:
QXL options and settings

Problem Description:

I am trying to run a report that returns ItemInventory where LastReceived is NULL. I have tried in vain to accomplish this. What syntax do you use to select a blank/null date, or is there a special function for this?

Solution:

You can select a blank/null date using "IS NULL" in your query.

For example, the below query will return a row whose InventoryDate is blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NULL

In the same way, you can use "IS NOT NULL" in your query.

For example, the below query will return a row whose InventoryDate is not blank or null:

SELECT * FROM ItemInventory where InventoryDate IS NOT NULL 

Problem Description:

I want to write some select statements on InvoiceLine and SalesReceiptLine that return all records dated WITHIN the past 30 days relative to whatever TODAY is. I'm very familiar with Microsoft SQL syntax and would normally say... WHERE TxnDate >= getdate()-30

How can I reference "30 days ago" using the QODBC driver?

Solutions:

In QODBC, the function CURATE() – Returns the current computer system date as a date value.

For example, for Today, April 18, 2006, when the following query:-

SELECT {fn CURDATE()} as Today, ({fn CURDATE()}-30) as "30 Days Ago", TxnDate, RefNumber, InvoiceLineDesc FROM invoiceline WHERE TxnDate >= ({fn CURDATE()}-30) is run in QODBC Test Tool. The results were:

Problem Description:

I need to select only the transactions that occurred in the last 90 days. I used the Access functions Date() and DateAdd() in the Criteria to filter for those transactions, but I am getting the error message "Invalid Procedure Call." Here are the Criteria that I am trying to use:

Between Date() And DateAdd("dd",-91,Date())

What am I doing wrong? Does QODBC have different functions for this?

Solutions:

To write Pass-thru queries for reading and to write QuickBooks® data using QODBC and Microsoft® Access, you must use the proper date format.

You may use Date Macros, but you may also use specific from and to dates for more flexibility.

This function formats dates with the QODBC format: {d 'YYYY-MM-DD'}. There is no need to remember the form, just the function's name: fncqbDate.



Function:

Function fncqbDate(myDate As Date) As String
myDate = Nz(myDate, Now)
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function


Example:

You might use fncqbDate to help create an SQL string with VBA from user input dates.

mySQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly',dateFROM = " & fncqbDate(BegDate) & ", dateTO = " & fncqbDate(EndDate) & " where account like '%checking%'"



Put Some Checks into a Table:

Try this out and put some checks on a table:
1. Copy and paste fncqbDate (first function above) into a module.
2. Copy and paste fncGetChecks (function below) into a module.
3. If you need QuickBooks® to open to using QODBC, open it and ensure you have authorized QuickBooks® to communicate with QODBC. 4. Make sure the following references are checked in your Microsoft® Access database:
Visual Basic For Applications
Microsoft Access 10.0 Object Library
Microsoft DAO 3.6 Object Library


To use fncGetChecks, call it from a form or type fncGetChecks into the immediate window of the Visual Basic Editor.

Change the default connection string if necessary by entering your connection string when prompted.

The function will ask for: a name for the new query (make sure this doesn't already exist in your database)

a beginning date

an ending date

Your connection string, which may or may not be the default offered



Function fncGetChecks()
On Error GoTo fncGetChecks_err
Dim q As String, Date1 As Date, Date2 As Date
q = InputBox("Give your temporary query a name:", "Temporary Pass-Thru Query", "")
Date1 = InputBox("Enter start date:", "Start Date", FormatDateTime(Now, vbShortDate))
Date2 = InputBox("Enter end date:", "End Date", FormatDateTime(Now, vbShortDate))
Dim db As DAO.Database, qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.CreateQueryDef(q)
qd.ReturnsRecords = True
qd.Connect = InputBox("Enter connection string:", "", "ODBC;DSN=QuickBooks Data;SERVER=QODBC")
qd.SQL = "sp_report customtxnDetail show TxnType,TxnID, RefNumber, Date, Name ,Memo , Amount,account " & _
"parameters TxnFilterTypes = 'Check',SummarizeRowsBy = 'TotalOnly'," & _
"dateFROM = " & fncqbDate(Date1) & ", dateTO = " & fncqbDate(Date2) & _
" where account like '%checking%'"
DoCmd.RunSQL "select * into tbl" & q & " from " & q
Set qd = Nothing
Set db = Nothing
DoCmd.DeleteObject acQuery, q
DoCmd.OpenTable "tbl" & q
Exit Function
fncGetChecks_err:

MsgBox Erl & " " & Err.Number & ": " & Err.Description
End Function


Also, refer to the following:

How to Use Prompted Date Ranges in MS Access 2007 using Vista 

Problem Description:

Is there any way to figure out when the Optimizer cache is corrupted, or is there a way we can avoid it altogether in the future?

Solutions:

Please refer to the steps below to identify if the Optimizer file has the issue:

Method 1

1). Locate the Optimizer file. The default path is %AppData%\QODBC Driver for QuickBooks\Optimizer)

You can open the optimizer folder via Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source >> Go To the "System DSN" Tab, select "QuickBooks Data" or "Your created QODBC" DSN & click "Configure">> Navigate to Optimizer tab->Click On "Open Optimizer Folder." 

2) Check the file modified date-time; if it's not recent, there is an issue.

To verify, start QODBC Test Tool and query the table you use the most, e.g., Invoice or Bill.  

3. Check the file modified date-time; if it's not recent, there is an issue.  

Method 2

1) Check QODBC logs. The default path is %AppData%\QODBC Driver for QuickBooks. 

Search for any of the following errors. If one of the following errors is present in the log file, then the Optimizer file is corrupted.

The database disk image is malformed.

SQLOptimize_SQLite_OpenOptimizeDBHandle prepare:
SELECT "type," "tbl_name" FROM "SQLite_MASTER."
Is the file encrypted, or is it not a database?

SQLOptimize_SQLOpen attach:
C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT
cannot start a transaction within a transaction

SQLOptimize_SQLite_ExecuteNonQueryFromMemory step:
ATTACH_PASSPHRASE ................. "C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT" AS "FQBase" The 
database is locked

SQLOptimize_SQLite_ExecuteNonQueryFromMemory step:
ATTACH_PASSPHRASE ................. "C:\Users\USER-NAME\AppData\Roaming\QODBC Driver for QuickBooks\Optimizer\COMPANY-NAME.OPT" AS "FQBase"

Fixing Optimizer file/Rebuilding Optimizer file

To overcome this issue, we need to reset the optimizer file. Please refer to the steps below to reset your QODBC optimizer file:

1) Close all other applications that use QODBC.

2) Delete the Optimizer file: Please refer to: How to Remove Optimizer file

Find the file with the .opt extension at the path you had assigned to the QODBC optimizer and delete the optimizer file.
(The default path is %UserProfile%\QODBC Driver for QuickBooks\Optimizer or %AppData%\QODBC Driver for QuickBooks\Optimizer )
You can find the optimizer folder path from QODBC Setup Screen->" Optimizer" tab. Please copy this path & navigate to this path & delete the .opt file.


3) After turning on the optimizer, please run the following command in the QODBC Test Tool:

sp_optimizefullsync All

Refer: How to execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for Selected Tables
This command will bring some data to a local cache to increase query retrieval performance. I want to suggest that you please let the above command run entirely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of documents in the QuickBooks company file. Please let this command run entirely; do not kill this command. It is not advisable to kill/close the application while it's building the Optimizer file.

4) Please test again.

5) If you still face the issue, please raise a support ticket.

Also, Refer

Troubleshooting: Slow Performance / Cannot Get Full Records From QuickBooks / How to find missing data

Keywords: qodbc cache slowing down data access

Problem Description:

I constructed an Access database file (32bit Access 2003) with tables linked to QuickBooks. I can pull information from the related tables to other tables in Access using update queries with no problems. When I try to run an update query to change a value in the QuickBooks linked table, I receive the following error. I need help figuring out the error and how to fix it.

My query is:

UPDATE PriceLevelPerItem INNER JOIN ItemPricing ON PriceLevelPerItem.FQPrimaryKey <> ItemPricing.FQPrimaryKey SET PriceLevelPerItem.PriceLevelPerItemCustomPrice = [ItemPricing].[PriceLevelPerItemCustomPrice];

Solutions:

You need to include PriceLevelPerItemItemRefListID in your update statement.

You need to include the below-the-line in your update statement to resolve this issue.

SET PriceLevelPerItem.PriceLevelPerItemItemRefListID = PriceLevelPerItem.PriceLevelPerItemItemRefListID For Example:

UPDATE PriceLevelPerItem INNER JOIN ItemPricing ON PriceLevelPerItem.FQPrimaryKey <> ItemPricing.FQPrimaryKey SET PriceLevelPerItem.PriceLevelPerItemItemRefListID = PriceLevelPerItem.PriceLevelPerItemItemRefListID, PriceLevelPerItem.PriceLevelPerItemCustomPrice = [ItemPricing].[PriceLevelPerItemCustomPrice];

Problem Description:

When Ting sets QODBC POS DSN to access the company file running in QuickBooks POS (Version 10), it will fail to connect to the company file. QuickBooks POS will search for the server with no response.

Solutions:

There is a limitation of QuickBooks POS SDK 10 that it will not be able to get the currently open company file name & thus, QODBC POS is not able to connect to the now available company file.

QuickBooks POS SDK Test Plus tool also requires a connection string; It will not connect to QuickBooks POS without a connection string.

For Example:

Connection String = " ComputerName=WIN-O8RQBVCMJFC;CompanyName=SampleCompany;Version=10 "

You must provide the connection string to connect the company file as a workaround for this issue. Please follow the steps below:

1) Please change QuickBooks POS to run in multi-user mode. Click the "Switch Company File to Multi-User Mode" option in the File Menu. 

2) Build Connection String:

Click the "..." button at QODBC POS Setup Screen->General Window to enter the company file selection window: 

3) In the company file selection window, QODBC POS will give you a list of all available company files (may take a few seconds) :

QuickBooks POS will ask you to grant permission to QODBC POS: 4) Select the company files you want to access and click ok. Then you will see the connection string has been built in the "Current Company Setting" option. And then, please click the "Test Connection" button.  

5) Click the "Yes Always" button. If QODBC POS tells you the connection is successful, you can use our test tool,s VB De, mo to view QuickBooks POS records via QODBC POS.

6) Now, we will test the connection using the QODBC POS Test Tool. Please open the QODBC POS Test Tool from:
Start>>All Programs>> QODBC Driver for use with QuickBooks POS>>QODBC POS Test Tool.

7) Please select the "QuickBooks POS Data" DSN from the dropdown list & click on "Connect."

8) We can connect to DSN from the QODBC POS Test Tool. Please write your SQL statement & click on "Run."

9) We can fetch data from the QODBC POS Test Tool. 

Problem Description:

I have the invoice number (RefNumber) from my table outside QuickBooks. But if a QuickBooks user creates invoices directly in QuickBooks, then QuickBooks keeps new incrementing invoices automatically, and the next invoice I create using my outside table will be incorrect.

I need to replicate what QuickBooks does in determining the following QuickBooks invoice number. What do you recommend?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically manage and assign a RefNumber to the newly inserted invoice.

If you would like to specify the RefNumber, and the RefNumber is numeric, it should be easy to sync up. Just get the max RefNumber from the Invoice table( of the current year), add 1 to it, and specify it in the insert statement.

Problem Description:

How do I auto-increment the Ref Number for the Invoice?

Solutions:

While inserting a new invoice from QODBC, if you do not pass RefNumber, QuickBooks will automatically increment and assign a RefNumber to the newly inserted invoice.

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I get the max and insert the RefNumber for the Invoice?

Solutions:

You can get the max RefNumber for Invoice by using the below query & add 1 to the result, and can create a new invoice:

SELECT Max(RefNumber) FROM InvoiceLine  

We got 2581 as the max RefNumber. Now we are creating the new invoice with RefNumber 2582.

INSERT INTO InvoiceLine (CustomerRefListID, RefNumber, InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('620000-1071526513', '2582', '240000-933272656', 'POWER TRAK-2000', 200.00000, 200.00, '20000-999022286',0)

Problem Description:

What happens if I use the same RefNumber for Invoice creation?

Solutions:

QuickBooks is allowing duplicate RefNumber. So when you use the same RefNumber for Invoice creation. QuickBooks will create an invoice with a duplicate RefNumber/

For example, if the RefNumber of the last Invoice is 1111, then QuickBooks will automatically assign 1112 as a RefNumber for the newly created invoice.

Problem Description:

How do I Auto Increment Alphanumeric RefNumber for Invoice?

Solutions:

First, create an invoice from QuickBooks UI & assign the alphanumeric RefNumber "R0001T". Now create a new Invoice from QODBC & do not pass RefNumber. QuickBooks will automatically increment and assign a RefNumber (i.e., "R0002T") to the newly inserted invoice.

Problem Description:

Can I adjust the value of a stock item without changing the stock quantity?

Solutions:

Yes, you can adjust the value of a stock item without changing the stock quantity. Here is an example:

This item has a COGS Average Cost of $69.73429:

Using QODBC, I adjusted the opening balance equity cost of the stock item by $250.00 using the following SQL insert statement:

INSERT INTO "InventoryAdjustmentLine" ("AccountRefFullName," "TxnDate," "RefNumber," "Memo," "InventoryAdjustmentLineItemRefFullName," "InventoryAdjustmentLineValueAdjustmentNewValue," "FQSaveToCache") VALUES ('Opening Bal Equity,' {d'2007-05-23'}, '110', 'Value Adj 250', 'Wood Door: Exterior', 250.0, 0) 

This item now has a COGS Average Cost of $106.69857:

The value adjustment details can be seen in the Inventory Valuation Detail: 

Problem Description:

I am getting the following error message when trying to SELECT data fields AS Alias, the statement runs fine otherwise.

[sql syntax error] Expected lexical element not found:=

Please see the following SQL statement:

SELECT Item. Name AS SKU, Item.CustomFieldColor AS Item, Item.Description, Item.SalesPrice, Item.PurchaseCost, Item.QuantityOnHand FROM Item Item WHERE (Item.Name<>'IFR' And Item.Name<>'OTW') AND (Item.Description<>'') AND (Item.Type='ItemInventory') ORDER BY Item.Name

The above statement is working fine in QODBC Test Too and MS Access. But I am facing an issue in MS Excel.  

Solutions:

MS Excel has an issue when you alias in the query. When you try to use Microsoft Query to return data from some third-party databases into Microsoft Excel, apostrophes (') around alias names can cause the query to fail.

Please refer to the link below to resolve this issue:

Using a field alias in Query does not work with some third-party databases

You can either apply a hotfix or change registry values.

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.

Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

The result after changing registry values & execute the query again:

Problem Description:

How do I run QODBC as windows services in the background? I noticed it required QuickBooks and Windows Server 2008 to be logged on.

Solutions:

QODBC and QuickBooks are desktop-based applications that cannot run as Windows services. QODBC uses QuickBooks SDK, and QuickBooks SDK uses QuickBooks Application to get data from company files.

QuickBooks SDK requires an active desktop session (someone to be logged in) to the machine because QuickBooks is a desktop-based application.

QRemote Server is not a service-based application; a desktop-based Windows application that cannot be run as a service.

Problem Description:

I've linked all the QODBC tables into an Access 2010 DB. I have created table subset queries on the original tables and would like to do selects based on date ranges - other things, too, like JOINs - but I'm stuck with this problem. The minute I give Access a date constraint, like receive payment.TxnDate >=#11/1/2014# as stated in your "How Dates Are Formatted.." documentation for Access Date formats, Access immediately crashes. No msgs in the brief QODBC log files about the crashes, as best I can tell:

Solutions:

Please try executing the following command using QODBC Test Tool to see if it can solve the issue: sp_optimizefullsync ALL.

This command will bring some data to a local cache to increase query retrieval performance. I want to suggest that you please let me run the above command entirely. The above command may take some time, depending on your records in QuickBooks. It may take 2-24 hours or more, depending on the number of documents in the QuickBooks company file. Please let this command run entirely. Do not kill this command. It is not advisable to kill/close the application while building the Optimizer file.

After successfully executing the above command, please create a new MS Access Database file & link all tables & execute the query again. It should work without any issues.

Please refer to: How to Use QODBC with Microsoft Access 2010

Problem Description:

I am trying to use a demo file for development, but QODBC is not allowing me to do this now that the evaluation period on my development machine has expired. Are there specific settings in the QODBC setup that I need to set?

Solutions:

Please keep QuickBooks running. Log in as Admin to your Sample company files for working with the sample company file without licensing. You can work with sample files without licenses.

QODBC Read-only edition for QuickBooks Enterprise does not include the QRemote License. So after 30 days, you may not be able to use QRemote. Instead of using DSN "QuickBooks Data QRemote," try using "QuickBooks Data" DSN.

If you use Visual Studio, change the target to x86 instead of Any CPU.

Instead of running the website via IIS, run it from Visual Studio. When you run via Visual Studio, the web application is processed by the desktop-based application instead of the service.

Problem Description:

How do I access the alternate email addresses 1 and 2 and the cc email fields? I don't see them at the customer table.

Solutions:

You can get CC Email details from the "Cc" field in the Customer & Entity table.

The Alt Email 1 and 2 fields are not available through the Intuit SDK, so they are not available through QODBC.

QODBC is an ODBC driver for QuickBooks. It uses the QuickBooks SDK to communicate with QuickBooks, which means if Intuit doesn't expose one feature to the application in the SDK, QODBC cannot do it either.

Problem Description:

When I run a report query, the query works very slowly & takes about 1-5 mins per simple report like:

"sp_report ARAgingSummary show Current parameters DateFrom={d'2014-11-12'}, DateTo={d'2014-11-12'} where Blank = 'Total'"

Sometimes it freezes QuickBooks, so the user should restart it.

Solutions:

Please enable the QODBC status panel via Start >> All Programs >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source.

Go to the "System DSN" Tab, select "QuickBooks Data" DSN & click "Configure."

Navigate to the "Message" tab ->Select "Display Driver Status" and "Display Optimizer Status" options.

Then the next time you run a query, if you see "Waiting for QuickBooks," it means QuickBooks is taking time to process the request. There will be a status panel at the lower right corner of your screen, showing a window with information on what QODBC is working on. Please note the step on which QODBC spends the most time or gets stuck and share it with us.

If you are getting "Waiting for QuickBooks," it means QuickBooks is taking time to process requests. So we may not provide much help on this issue.

QuickBooks SDK provides data in XML format & QODBC displays it in tabular form. QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools, speeding development time. QODBC accepts SQL commands from applications through the ODBC interface, then converts those calls to navigational XML commands to the QuickBooks Accounting DBMS and returns record sets that qualify for the query results.

QODBC requests the Report information from the QuickBooks SDK, and QuickBooks is the one processing it and sending the output. QODBC formats that output to a data table format.   

Problem Description:

I am trying to use MS Access as a middleware with MS SQL Server. I have created an MS Access database & linked all QODBC tables through QRemote DSN. After that, I created MS Access DSN, which points to the newly created database. After that, I created the linked server pointing to MS Access DSN. The linked server is created, but when I run the query on the linked server, I get the below error: 

Please help me to resolve this error.

Solutions:

This error is not related to QODBC. You are facing this issue because MS SQL Server is logged on as a "Network Service."

You need to change this setting to "Local System."

You can change it by below steps:

Start >> Run >> type "services. msc" and click on "OK."

Find SQL Server (MSSQLSERVER), Open SQL Server (MSSQLSERVER) by double clicking on it & navigate log On tab & choose log-on as "Local System Account," and apply the changes by clicking on the ok button & test again.

Problem Description 1:

We have a customer trying to import a credit memo from our database into QuickBooks using a vb.net program. They are getting the following error: ERROR [42S00] [QODBC] Insert value must be a simple value. We have other customers that can import credit memos, and I have compared the imported data and cannot find any difference in the data format. Can you give me an idea of what to look for?

Solutions 1:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VB code generates this error. It is not a QODBC error.

Please verify your insert statement & try again. Please refer below-mentioned link to a sample VB code:

Examples of How to Use QODBC via Visual Basic

Please refer to the below-mentioned article for creating a credit memo:

How to create Credit Memos

Problem Description 2:

When I go to insert a Customer, I receive an error saying [QODBC] Insert value must be a simple value. I need to know what I am doing wrong to insert a new customer.

Please refer below code which I am using:

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim address postal code As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

"INSERT INTO customer(name, firstname, lastname, companyName, contact, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressCity, BillAddressState, BillAddressPostalCode, Phone, Fax, Email)VALUES(name, firstname, lastname, companyName, contact, billAddressAddr1, billAddressAddr2, billAddressAddr3, billAddressCity, billAddressState, billAddressPostalCode, phone, fax, email)")

LogEntry("New Customer Added to QuickBooks")

Catch e As an Exception

MsgBox(e.ToString)

End Try

' Close the database.

rs.Close()

rs = Nothing

conn.Close()

conn = Nothing

'*********************************************************************************************************************

End Sub

Solutions 2:

You are getting the error " [QODBC] Insert value must be a simple value" because you are not passing the value in the correct format. The VBA code generates this error. It is not a QODBC error.

There is a problem with your Insert statement. You cannot pass the value directly. Your insert statement should be like as below.

INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')

Please refer to the updated code & try to insert the record with this code.

Public Sub InsertNewCustomers()

Dim name As String

Dim firstName As String

Dim lastName As String

Dim companyName As String

Dim contact As String

Dim billAddressAddr1 As String

Dim billAddressAddr2 As String

Dim billAddressAddr3 As String

Dim billAddressCity As String

Dim billAddressState As String

Dim addressPostalCode As String

Dim phone As String

Dim fax As String

Dim email As String

'Testing to Insert, remove before going LIVE!!!*************************************************************************************************

name = "ABC XYZ"

firstName = "ABC"

lastName = "XYZ"

companyName = "Test Company"

contact = "Jerry"

billAddressAddr1 = "503 Test Club"

billAddressAddr2 = ""

billAddressAddr3 = ""

billAddressCity = "Test City"

billAddressState = "OK"

addressPostalCode = "11644"

phone = "111-111-1111"

fax = ""

email = ""

'*********************************************************************************************************************

Dim conn As ADODB.Connection

Dim rs As ADODB.Recordset

conn = New ADODB.Connection

conn.ConnectionString = "DSN=Quickbooks Data;OLE DB Services=-2;"

conn.Open()

Try

' Create the new record.

rs = conn.Execute( _

sSQL = "INSERT INTO customer(Name,FirstName,LastName,CompanyName,Contact,BillAddressAddr1,BillAddressAddr2,BillAddressAddr3,BillAddressCity,BillAddressState,BillAddressPostalCode,Phone,Fax,Email) " & _

" VALUES( '" + name + "','" + firstName + "','" + lastName + "','" + companyName + "','" + contact + "','" + billAddressAddr1 + "','" + billAddressAddr2 + "','" + billAddressAddr3 + "','" + billAddressCity + "','" + billAddressState + "','" + billAddressPostalCode + "','" + phone + "','" + fax + "','" + email + "')"

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

conn.Open sConnectString

' Create a new record.

rs = conn.Execute(sSQL)

sMsg = sMsg & "Record Added!!!"

MsgBox msg

' Close the database.

Set rs = Nothing

Set conn = Nothing

'*********************************************************************************************************************

End Sub

Problem Description:

I'm trying to create a new table with VB Demo or QODBC Test Tool.

Can you send me a sample query to do this?

Solutions:

QODBC is an ODBC driver. Please remember that QODBC is not a database but rather a translation tool. QODBC acts as a 'wrapper' around the Intuit SDK so customers can finally get at their QuickBooks data using standard database tools;

You cannot create a new table from VB Demo or QODBC Test Tool because QODBC is just reading and writing to the QuickBooks Company file via the QuickBooks SDK. QuickBooks SDK provides data in XML format, and QODBC converts it to a table format.

What you are trying to do is a new table in the QuickBooks Company file, which is not supported. Thus, you cannot create/drop/alter table(s) from the VB Demo or QODBC Test Tool.

Keywords: adding in a column, adding in a table, how to add a new table, how to add a new column

Problem Description 1:

I am using an MS SQL-linked server with QRemote.

I am updating a single EstimateItemLine, passing Ref Number and ItemFullName (I also tried itemtxnlineid). Every time the update statement, with OpenQuery or without openquery on the linked server, it searches all the records of EstimteLine and then updates the record. This process takes 3.5 to 4 minutes.

Ideally, it should not scan all the records as the Ref Number is provided.

I am using the below query:

Update openquery(qremote, 'select ClassRefListId from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658'

Solutions 1:

The performance of an update query with OpenQuery can be improved by including the "where" criteria outside the open query.

For Example:

The Query

Update openquery(qremote, 'select ClassRefListId from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658'

will perform faster when executing it as

Update openquery(qremote, 'select ClassRefListId,EstimateLineTxnLineID,RefNumber from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658' where RefNumber='501' and EstimateLineTxnLineID='8DB9-1071524326'

So yes, you need to include [ RefNumber='501' and EstimateLineTxnLineID='8DB9-1071524326' ] twice, inside the OpenQuery and outside too.       

Problem Description 2:

I am using an MS SQL-linked server with QRemote.

In SQL Server, when we use update openquery, without including fields that identify a single row that needs to be updated, then SQL returns the error "OLE DB provider "MSDASQL" for linked server "QREMOTE" returned the message "Key column information is insufficient or incorrect. Too many rows were affected by the update." Rows were updated, but still, it shows the error message. The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table " [MSDASQL].".

I am using the below query:

Update openquery(QREMOTE,'select customfieldtransportaddress,CustomFieldOther from salesorderwhere txnDate >= {d''2013-12-29''} and refNumber = ''8801'' ') set CustomFieldOther='1514999',customfieldtransportaddress = 'Conexão333'

Error message:

OLE DB provider "MSDASQL" for linked server "QREMOTE" returned the message "Key column information is insufficient or incorrect. Too many rows were affected by the update.".Msg 7343, Level 16, State 4, Line 1The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table "[MSDASQL]." 

Solutions 2:

To overcome this issue, you need to include an extra "where" clause outside the open query.

For Example:

Update openquery(QREMOTE,'select refNumber,customfieldtransportaddress,CustomFieldOther from salesorderwhere txnDate >= {d''2013-12-29''} and refNumber = ''8801'' ') set CustomFieldOther='1514999',customfieldtransportaddress = 'Conexão333' where refNumber = '8801'


So, you need to include the where condition twice, inside the OpenQuery and outside.       

Problem Description:

I need some guidance on extracting data from the general ledger. In particular, I need to see the FullName of GL account. Running sp_report Journal through a linked SQL Server returns errors stating that FullName does not exist.

I am using the below query to access the Account detail, but I can't access the Account Name detail from the output. Please help:

select
isnull(Account,'')Account,
isnull(Amount,0)Amount,
isnull(Date,'01/01/1900') [Date],
isnull(Credit,0) Credit,
isnull(Debit,0) Debit,
isnull(Memo,'') Memo,
isnull(Name,'') Name,
isnull(RefNumber,'') RefNumber,
isnull(RowNumber,0) RowNumber,
isnull(RowType,'')Rowtype,
isnull(SplitAccount,'')SpliAccount,
isnull(TxnID,'')TxnID,
isnull(TxnNumber,'')TxnNumber
from
openquery(QREMOTE,'sp_report Journal show Account,Amount,Date,Credit,Debit,Memo,Name,RefNumber,RowNumber,RowType,SplitAccount,TxnID,TxnNumber parameters DateFrom = {d''2017-12-01''}, DateTo = {d''2017-12-05''}') where RowType = 'DataRow'    

Solutions:

From sp_report, we would not be able to include FullName instead of the information QuickBooks provides in QuickBooks SDK.

Instead, you may use the JournalEntryLine table, which will show detailed information about the Journal, and you can apply filters to achieve the desired output.

QuickBooks SDK does not provide a full name, and we could not add it to the sp_report response.

But yes, there is a workaround, you can query the Account table and Link the result of Sp_report to Account and get the FullName.

Linking two tables would not be straightforward, as QuickBooks sends the Account name as "10020 · Account Receivable," i.e., "AccountNumber ·Name."

We need to query an Account table using the Join function to access account details. Also, we need to use substring & other SQL functions to compare data for achieving desired results because the report output contains Account Number & Account FullName in the Account column output combined using dot ".". So we need to use substring & other SQL functions to remove this dot & compare the account name with the Account table.

Please refer below query:

select
isnull(Account,'')Account,
isnull(Amount,0)Amount,
isnull(Date,'01/01/1900') [Date],
isnull(Credit,0) Credit,
isnull(Debit,0) Debit,
isnull(Memo,'') Memo,
isnull(a.Name,'') Name,
isnull(RefNumber,'') RefNumber,
isnull(RowNumber,0) RowNumber,
isnull(RowType,'')Rowtype,
isnull(SplitAccount,'')SpliAccount,
isnull(TxnID,'')TxnID,
isnull(TxnNumber,'')TxnNumber,
b.FullName as AccountFullName,
c.FullName as SplitFullName
from openquery(QREMOTE,'sp_report Journal show Account,Amount,Date,Credit,Debit,Memo,Name,RefNumber,RowNumber,RowType,SplitAccount,TxnID,TxnNumber parameters DateFrom = {d''2017-12-01''}, DateTo = {d''2017-12-05''}') a
left join
openquery(QREMOTE, 'select * from account') b on
SUBSTRING ( a.Account ,0 , CHARINDEX (' · ', a.Account) )= ISNULL(CONVERT(varchar(50),b.AccountNumber),'')
and
REPLACE(a.Account,SUBSTRING ( a.Account ,0 , CHARINDEX (' · ', a.Account) ) + ' · ','') = ISNULL(CONVERT(varchar(50),b.Name),'') left join
openquery(QREMOTE, 'select * from account') c on
SUBSTRING ( a.SplitAccount ,0 , CHARINDEX (' · ', a.SplitAccount) )= ISNULL(CONVERT(varchar(50),c.AccountNumber),'')
and
REPLACE(a.SplitAccount,SUBSTRING ( a.SplitAccount ,0 , CHARINDEX (' · ', a.SplitAccount) ) + ' · ','') = ISNULL(CONVERT(varchar(50),c.Name),'')
where RowType = 'DataRow'

Problem Description:

Is there a stored procedure that provides all deleted transactions? I.e., Voided/Deleted Transactions Detail?

For Example:

If an Invoice gets deleted from QuickBooks, does it get physically deleted? Otherwise, which field(s) would tell me that fact?

Solutions:

You can find Voided/Deleted Transactions details from QODBC "TxnDeleted" and "ListDeleted" tables. Please refer to tables "TxnDeleted" and "ListDeleted" to see the data you are looking for.

For Example, I have an Invoice in QuickBooks whose Invoice# is 1114  

Now we are verifying the same Invoice from the QODBC Invoice table. By using the below query:
SELECT * FROM Invoice where RefNumber ='1114'

Now we are deleting the Invoice from QuickBooks, whose Invoice# is 1114: 

Now we are verifying the same Invoice from the QODBC Invoice table to see if it exists in the QODBC Invoice table. By using the below query:
SELECT * FROM Invoice where RefNumber ='1114'

Now we are checking the same Invoice from the QODBC TxnDeleted table to see if it exists in the QODBC TxnDeleted table. By using the below query:
SELECT * FROM TxnDeleted where TxnDelType='Invoice' 

We can find Deleted Transactions (i.e., Invoice, SalesOrder, TimeTracking, etc) details from QODBC "TxnDeleted" tables.

We can find Deleted List (i.e., Customer, Item, etc) details from QODBC "ListDeleted" tables.

Problem Description:

We are working with a client that processes many credit cards and must be PCI compliant. Do you have any insight into what needs to be done on the QODBC side to maintain PCI compliance?

Solutions:

QODBC uses QuickBooks SDK, which implements the security standards listed in the attached articles:

https://quickbooks.intuit.com/learn-support/en-us/help-article/data-security/quickbooks-pci-service-faqs/L7ipNg7n9_US_en_US

https://security.intuit.com/compliance

https://quickbooks.intuit.com/learn-support/en-us/employees-and-payroll/re-how-do-you-prove-pci-compliance/01/1278650/highlight/true

https://quickbooks.intuit.com/learn-support/en-us/help-article/data-security/explanation-pci-dss-compliance-services/L5tbibLub_US_en_US

http://dataservices.intuit.com/support/Articles/INF12850

I want to inform you that QODBC only stores data in encrypted optimizer file(s) on the disc. QODBC can store only the information that QuickBooks SDK exposes.

If you want more security, you can disable the optimizer option. So QODBC will fetch data directly from QuickBooks SDK and will not be stored on the disk drive.

First, please create a new MS Access database & link ALL tables.
Please refer to the article below for How to Use QODBC with Microsoft Access:

How to Use QODBC with Microsoft Access

After linking the table, we need to execute the query in the MS Access query editor:
MS Access>> create tab >> Query Design >> It should pop up for choosing a table. Please click the "Close" button & click on "SQL" from the left top corner below the File menu.

Note: For multi-line invoices, some may find it easier to NOT include the header information with each line item, but rather to have separate smaller commands for the Invoice Lines and a final  INSERT for the Invoice Header.

Create an Invoice with 3 Lines

     Here we create an invoice with three lines by using 3 InvoiceLine INSERT commands (with the FQSaveToCache caching flag set true) followed by the Invoice table (header) insert, which is automatically designed to pull in all cached InvoiceLine records and save immediately.

First Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 1', 1.00000, 1.00, '20000-999022286', 1)

Second Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 2', 2.00000, 2.00, '20000-999022286', 1)

Third Line

INSERT INTO InvoiceLine (InvoiceLineItemRefListID, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, InvoiceLineSalesTaxCodeRefListID, FQSaveToCache) VALUES ('320000-1071525597', 'Building permit 3', 3.00000, 3.00, '20000-999022286', 1)

Header Information

INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES ('620000-1071526513', '40000-933270541', #9/23/2005#, '1', 'Brad Lamb,' '1921 Appleseed Lane', 'Bayshore,' 'CA,' '94326', 'USA,' 0, '10000-933272658', #10/31/2005#, #10/01/2005#, '360000-1071521681', 'Memo Test,' 0, '10000-999022286') 

Results in QuickBooks 

Related Data Location

InvoiceLineItemRefListID

InvoiceLineItemRefListID is '320000-1071525597' and found like this:

     Select ListId, FullName from Item where FullName like 'C%'

InvoiceLineSalesTaxCodeRefListID

InvoiceLineSalesTaxCodeRefListID is '20000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'N%'

CustomerRefListID

CustomerRefListID is '620000-1071526513' and found like this:

    Select ListId, FullName from Customer where FullName like 'Smi%'

ARAccountRefListID

ARAccountRefListID is '40000-933270541' and found like this:

     Select ListId, Name from Account where Name like 'Account%'

ItemSalesTaxRefListID

ItemSalesTaxRefListID field is related to ItemSalesTax, ItemSalesTaxGroup, and ItemSalesTaxGroup, Line, depending on InvoiceLineType. If InvoiceLineType is GroupItem, then it is found in the ItemSalesTaxGroupLine table.

ItemSalesTaxRefListID is '360000-1071521681' and found like this:

     Select ListId, Name from ItemSalesTaxGroup where Name like 'E%'

CustomerSalesTaxCodeRefListID

CustomerSalesTaxCodeRefListID is '10000-999022286' and found like this:

     Select ListId, Name from SalesTaxCode where Name like 'T%'

Problem Description:

How can I use the same Excel files in 64 & 32-bit Systems?

Is it possible to have a connection set up in an Excel file and be able to refresh on either a64-bit4 or 32-bit system?

I know it's possible on the same (64 or 32) as long as the DSNs have the same names, but I wasn't sure about "crossing" between systems.

Solutions:

Yes, you can create a QRemote DSN with the same name on all the machines on which you would like to use Excel.

Your Excel file needs to use a common name DSN, for example, "QODBC-Excel."

Please refer to the example below:

1) Machine A of a 64-bit OS has 64-bit Excel - create a DSN "QODBC-Excel" under 64-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source 64-bit:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

2) Machine B of a 64-bit OS has 32-bit Excel - create a DSN "QODBC-Excel" under 32-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

3) Machine C of 32-bit OS has 32-bit Excel - create a DSN "QODBC-Excel" under 32-bit ODBC Administrator/Manager:

Start >> QODBC Driver for use with QuickBooks >> Configure QODBC Data Source:

Go to the "System DSN" Tab >> Click on the Add button to create a new DSN "QODBC-Excel."

Select the QRemote for QuickBooks option and click on the Finish button.

Configure the DSN, enter the name "QODBC-Excel," and provide the IP address and Port as listed under "QRemote Server."

Now we can see the newly created DSN "QODBC-Excel" in the System DSN list.

Now you can use the same Excel file on the above machine, whether machine 32 or 64-bit machine. Also, you can use the same Excel file on different architectures (i.e., you can use a 32-bit Excel file on 64-bit Excel and vice-versa)

To apply the newly created DSN to old Excel files.

Open the Excel file. Please go to the DataTab & click on "Connections."

You can see existing connection details. Please click on "Properties."

Please select the connection name & remove it.

Now manually write the newly created DSN name for this example, "QODBC-Excel," & click "OK."

Now you can see updated connection details. Please click on "close."

By clicking on the "Refresh All" button, you will get updated data from the newly created DSN.

For a Single Execution, follow the steps below.

Click on "Start" and "Programs" and locate QODBC Driver for the QuickBooks program group. Click on the QODBC Test Tool icon to launch the program supplied with QODBC.

To establish a connection, select "QuickBooks Data" -> DSN from the dropdown list and click on "Connect."

This will connect you to the current company file opened by QuickBooks. After you click Connect, if this is the first time QODBC has used this company file, you will receive a pop-up from QuickBooks asking you to allow QODBC to run. Choose the Yes, Always option, and check the Sensitive data option.

Once you are connected, type SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC and click on "Run."

After execution, the following message will appear: "Total Number of Records 0."

For Recurring / Scheduled Execution, follow the steps below.

( ONLY IF YOU WOULD LIKE TO SCHEDULE OR CREATE A BAT TO RUN OPTIMIZER FOR SELECTED/MULTIPLE TABLES QUICKLY)

In the example below, we are achieving it by creating a VBScript.

You can create a script/exe/VBA code and execute SP_OPTIMIZEUPDATESYNC or SP_OPTIMIZEFULLSYNC for a select table.

In this example, we are creating a VBScript, saving it to e:\, and naming it OptimizeSelected.VBS

(You may change the name and drive location of your choice. Please apply the same modification to the execution statement)

Below is the source of the OptimizeSelected.VBS

'*****************************************
Const adopt static = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
sSQL = "SP_OPTIMIZEUPDATESYNC Invoice"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

sSQL = "SP_OPTIMIZEUPDATESYNC Customer"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

sSQL = "SP_OPTIMIZEUPDATESYNC Bill"
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing

'*****************************************

How to execute

For a 64-bit machine, you can execute this using the command below

C:\Windows\SysWOW64\cmd.exe /c cscript e:\OptimizeSelected.vbs

For a 32-bit machine, you can execute this using the command below

C:\Windows\System32\cmd.exe /c cscript e:\OptimizeSelected.vbs

Also, Refer

VB Demo Missing

TroubleShooting: Cannot Get Full Records From QuickBooks

How to switch OFF or RESET the QODBC Optimizer

How to create a Link Server with MS SQL 2005/2008/2012/2016 64-bit Using QODBC/QRemote

 For 32-bit Refer: How to create Link Server with MS SQL 2005/2008/2012/2016 32-bit Using QODBC/QRemote

Start your "SQL Server Management Studio"

Log in to your SQL Server.

On the left-hand side panel, you will find the Serve. Right-click on the "SQL Server" and Select Properties. 

Please Verify that your SQL Server is 64-bit. 

64 and 32-bit words are kept separate in the Microsoft Windows Operating System. QuickBooks 2021 and older versions are 32-bit applications, so a 32-bit ODBC driver is required to communicate with them.

QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-bit Web Servers and 64-bit applications to talk to QuickBooks Live for the first time via the QODBC Driver.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect. We call it QRemote.

QRemote has two components: "QRemote Client Driver for QODBC" and "QRemote Server for QODBC." 

QRemote Client Driver: Allows service-based applications and 64-bit applications to connect to the QRemote Server and send the request to the QRemote Server for processing.   

QRemote Server: Processes requested instructions from QRemote Client to QuickBooks using QODBC DSN Driver.  

Start "QRemote Server for QODBC" from the start menu. ( If QRemoteServer is not running )  

Double-click the QRemote Server icon from the system tray 

QRemote Server Main screen shows the IP address and port, allowing the QRemote Client to connect.

Note the IP address and Port number listed on the QRemote Server "Event Logs " window.

Create a new DSN entry/or configure the existing one

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks, and click

Configure QODBC Data Source 64-bit  

"QuickBooks Data 64-bit QRemote" is the default DSN created when installing QODBC. (Earlier, it was "QRemote Data 64-bit")

Configure the DSN and provide the IP address and Port as listed under "QRemote Server." 

Under the QRemote for QuickBooks – Client  DSN configuration screen, please fill in the following information 

·         Data Source Name: Name of your DSN entry 

·         Server: IP address displayed on QRemote Server 

·         Port: Port displayed on QRemote Server 

·         Encryption Key/Password: (By Default, Encryption Key/Password is set to "QODBC#QRemote$1". In case you want to change your Encryption Key/Password, you can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN) 

·         Encryption: (By Default, Encryption is set to Off. In case you want to use encrypted data transfer between QRemote client and QRemoteServer, you can configure encryption on QRemoteServer and use the same on QRemote Client DSN) 

·         Remote DSN: select "QuickBooks Data" DSN from the list. ( Make sure QRemote Server is running)  

Start Your QuickBooks.

Run MS SQL 2005 Dev / 2008 / 2012 / 2016 (64-bit) and Login

Run SQL Server Management Studio. You'd better choose SQL Server Authentication and input the right name and password.

Under SQL Server Management Studio, Navigate to "Server Objects" -> Linked Server  -> New Linked Server.

 OR

Provide the "New Linked Server" information: Insert your desired Linked Server Name, select the Microsoft OLE DB Provider for ODBC Drivers from the provider list & insert "QODBC" as the Product name & "QuickBooks Data 64-bit QRemote" as the Data Source & click the "OK" button. 

Clicking OK will start establishing a connection to QuickBooks. Please accept the certificate to allow data access to QuickBooks.

After you have accepted the certificate, the Linked Server will be shown under the "Linked Server" Category.

 Configure MSDASQL (Microsoft OLE DB Provider for ODBC Drivers)

   The OLE DB provider options for managing linked queries can be set in SQL Server Management Studio. In Object Explorer, right-click the provider name and select Properties for MSDASQL.

The dynamic parameter should be checked to allow parameter queries. Level zero should only be checked to allow four-part naming to work. Allow InProcess should be checked to help performance. Non-transacted updates should be reviewed if you want to be able to update QuickBooks data. The index as an access path should be checked to help performance. Disallow Adhoc accesses should be unchecked to allow OPENQUERY to function.

QODBC/QRemote is ready to use with SQL Server/Linked Server

Execute below query

Select * from QRemote…Company

 On each query, the QRemote Server logs the events. If you are facing any issues or delays in response, please refer to the QRemote Server Event Logs for details on which stage is taking more time.

Another Test

Select * from QRemote…Account

or

Select * from OpenQuery (QRemote, 'select * from Account')

Note: If your SQL statement contains a WHERE clause, use OpenQuery. When using the OpenQuery 'where' clause, filters will be sent to QODBC, and data retrieval will be faster.

On each query, the QRemote Server logs the events. If you are facing any issues or delays in response, please refer to the QRemote Server Event Logs for details on which stage is taking more time. 

Errors and Solutions 

"[QODBC] QODBC  Server Edition License Required."

The QODBC CDKEY License you have activated is not of the Server edition. Please get in touch with our Sales or Support team. http://support.flexquarters.com/esupport/

"[QODBC] Evaluation Expired" OR "Evaluation Expired. QRemote requires a QODBC license. Please purchase a QODBC or QRemote license."  

You have run out of the 30-day trial/evaluation version or are using the QODBC Read-Only Edition for QuickBooks Enterprise. If you still wish to evaluate QODBC, please get in touch with our Sales team.

"QRemote" or "QODBC" contains multiple tables that match the table "TableName."

OR

An invalid schema or catalog was specified for the provider "MSDASQL" for the linked server "QRemote."

This means you have not configured the "MSDASQL" provider. Please refer to the above steps and configure the "MSDASQL" provider.

Problem Description:

I have the read-only version included in the enterprise product. I can connect from the VB demo and Excel, but not from the SQL server (I cannot always open Reckon Accounts). All I want to do is export data to the SQL server.

I have made the SQL server process log on as the same Windows user as Reckon Accounts and QODBC. I am not running RA, QODBC, or SQL Server as a Windows administrator, and I have UAC turned on. Often, during the query, a box pops up trying to start:
c:\Program Files (x86)\intuit\Reckon Accounts 2015\DBManagerExe.exe StartServer 64

It pops up twice, and I choose yes. I have also tried to run it manually.
c:\Program Files (x86)\intuit\Reckon Accounts 2015\DBManagerExe.exe StartServer 32. 

Solution:

Please restart your machine & please run Reckon, log in to your company file with Admin user & try to connect using your application.

To use DCOM Server & SQL Server with QODBC, you need a QODBC Server Edition license.

Please use the QRemote Server instead of the DCOM Server to connect to the SQL Server using QODBC. Please get in touch with your QODBC dealer to purchase the QODBC Server Edition license.

For licensing details, please refer to QODBC with QRemote Licensing Information.

Please Refer:

Using QuickBooks data with MS SQL Server using QODBC Driver and QRemote (Video)

QODBC Reports Architecture Mismatch Problem When connecting

Keywords: SQL Server 2008 Management Studio, SQL Server 2016 Management Studio, SQL Server 2012 Management Studio, SQL Server Management Studio, SSMS