[QODBC-Desktop] How to setup QODBC for Auto-login into QuickBooks
Posted by Juliet (QODBC Support) on 22 February 2010 06:02 AM
How QODBC and QuickBooks SDK works
Intuit QuickBooks SDK inside of the QuickBooks application uses XML format for reading and writing QuickBooks data.
QODBC uses the QuickBooks SDK to talk to QuickBooks data. QODBC acts as a 'wrapper' around the Intuit SDK so customers can get at their QuickBooks data using standard database tools; speeding development time.
QODBC converts this XML communication into the easier to use SQL/ODBC format.
You cannot use the QuickBooks SDK without running QuickBooks as they are one and the same programs.
The configuration of the DSN
Create a new DSN
Locate the QODBC Driver for QuickBooks program group on your desktop. Click on the Configure QODBC Data Source icon to launch the configure data source program supplied with QODBC.
On Windows 7 Or Vista
Create a New DSN
Click on the "System DSN" tab to create a DSN that can be called by both Windows applications and services. Click on the "Add" button to create a new DSN.
Select the QODBC Driver for QuickBooks option and click on the Finish button.
Configure the DSN
The following general setup screen will become available. Check the "Locate a company file:" option. This allows you to set up the full path to where your QuickBooks data file resides. For example:
“C:\Users\Public\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 18.0\sample_product-based business.qbw”.
The Browse button will let you choose your QuickBooks company file from the folder it is in. To use the QODBC auto-login unattended mode, you must specify the full path of the QuickBooks company file you wish to connect to.
Choose the Advanced tab in QODBC Setup. Use these settings to allow "auto-login" with or without a visible QuickBooks User Interface.
And then click on the OK button to create a new data source named "QBAutoLogin".
Setup Auto-Login in QuickBooks
Use VB Demo to Connect QuickBooks for the First Time
Click on the QuickBooks Enterprise Solution 18.0 icon on the desktop to launch it and open your QuickBooks data file. Be sure to login in as the Administrator user.
Locate the QODBC Driver for QuickBooks program group on your desktop. Click on the VB Demo icon to launch the VB Demo program supplied with QODBC.
The following VB Demo screen will become available. To establish a connection: from the menu bar select Connections -> Add a new connection, select the Machine Data Source tab and QBAutoLogin System DSN, Click OK.
Certificate Auto Login to QuickBooks
Then the QuickBooks will ask your permission for this driver to access your QuickBooks data. Check the Yes, always; allow access even if QuickBooks is not running option and then click on the Continue… button to confirm.
Confirm that you want to allow this access by clicking on Yes and then Done. This will allow applications to integrate with QuickBooks data when QuickBooks is running on the desktop.
This is because Intuit's LGB file which was common earlier is now changed to new a method and integration information will be stored for each Windows user separately.
Switch to 1st Windows Machine
Login as User-B to windows OS. Login to your Company Files as Admin. Test the connectivity with QODBC using each application, One by one. Perform connection to your QODBC DSN from each application & Authorize/Accept application certificate for EACH application.
Login as User-C to windows OS. Login to your Company Files as Admin. Test the connectivity with QODBC using each application, One by one. Perform connection to your QODBC DSN from each application & Authorize/Accept application certificate for EACH application.
Switch to 2nd Windows Machine
Switch to 3rd Windows Machine
Switch to 4th Windows Machine
From the QuickBooks Edit menu, select Preferences, and then select Integrated Applications. There are often instances in which you want an integrated application designed to share data with QuickBooks to access QuickBooks files automatically, without user intervention. This might permit, for example, a particular application to run during the night when there is no other activity in the company file. Also, QuickBooks doesn't need to be running in the foreground for ODBC compliant applications to access the data files using QODBC while setting in this mode.
Click the Company Preferences tab and make sure that the Don't allow any applications to access this company file checkbox isn't checked. The Notify the user before running any applications whose certificate has expired checkbox can be checked. If it is, however, automatic access will be suspended if the application's certificate expires.
Select the FLEXquarters QODBC driver you want to run automatically and click the Properties… button.
Check Allow this application to access this company file. Uncheck Prompt before Allowing Access. Check Allow this application to log in automatically. Select your Login as user. (Normally "Admin") Click on the OK button.
Test the Connection
Now that you have successfully established a connection to your QuickBooks data, it's time to type in your desired SQL query, for example, you can use the default "SELECT * FROM Customer" or type in another table like stock by entering "SELECT * FROM ItemInventory". Click on the Query button and the requested query results will appear! In case you're wondering, the * in the select statement means all columns in that table.
Possible problem & solution:
Problem Description 1:
When our application queries the QB database, the connection time is at least 7 seconds (usually 12 seconds or more).
As you are using Auto Login QuickBooks may take time in running in the background.
I would like to inform you that If company file in on the network location, then there might be a possibility of delay in opening QuickBooks company file through a network.
If the company file on the same machine, please keep the QuickBooks application running no need to login company file just keeps QuickBooks window open. So you can eliminate the time which is required to start QBW32.exe (QuickBooks) in the background.
Problem Description 2:
I have an MS Access 2013 database connecting to Quickbooks Enterprise using QODBC. This works fine when I manually open Quickbooks. However, if I don't have Quickbooks open I receive an ODBC connection failure. The Quickbooks Enterprise file is quite large and is normally slow to load.......probably 10 seconds or so, even on the server with plenty of RAM and Fast Processors. So I am assuming that I am just having some sort of timeout. I see some settings in the QODBS driver but did not know which ones might help.
Can someone please furnish a little direction on this issue?
Please, set up auto-login, after you set auto-login, you can access QuickBooks without open it. Please follow steps to setup auto login.
When you setup Auto-login, please verify that the company file path on QODBC and QuickBooks, these two paths should be the same. You can find QODBC company file path via QODBC setup screen -> General tab. You could find the QuickBooks company file via QuickBooks UI, press F2 key you will see the file information.
Please note, you can connect to only one company file at a time if you want to connect to another company file you need to disconnect the connection from your application and connect to another company file.