Knowledgebase: Connection
[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 work

Intuit QuickBooks SDK inside the QuickBooks application uses XML format to read and write QuickBooks data.

QODBC uses the QuickBooks SDK to talk to QuickBooks data. QODBC acts as a 'wrapper' around the Intuit SDK so customers can access 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 the same programs.

You can run QuickBooks on the desktop or run it in "hidden" mode. Either way, it is running; the only difference is whether you see it on the desktop or not.

For more details, refer How to setup QODBC to run without QuickBooks

The configuration of the DSN

Create a new DSN

     Locate the QODBC Driver for the 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 both Windows applications and services can call. 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 its folder. To use the QODBC auto-login unattended mode, 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 QODBC Test Tool to Connect QuickBooks for the First Time

     Click on the desktop's QuickBooks Enterprise Solution 18.0 icon to launch it and open your QuickBooks data file. Be sure to Login in as the Administrator user.

     Locate the QODBC Driver for the QuickBooks program group on your desktop. Click on the QODBC Test Tool icon to launch the QODBC Test Tool program supplied with QODBC.

     The following QODBC Test Tool screen will become available. To establish a connection: from the dropdown list, select your QBAutoLogin, and click on Connect.

Certificate Auto Login to QuickBooks

     Then 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.

Note:
          The application certificate needs to be accepted for each Company File.
          The application certificate needs to be accepted for each application.
          The application certificate needs to be accepted for each Windows Machine.
          The application certificate needs to be accepted by each Windows User.

This is because Intuit's LGB file, which was common earlier, is now changed to a new method, and integration information will be stored for each Windows user separately.

For example,
You have 4 Windows Machines, and each has three windows users. All those three windows users use QODBC via Microsoft Excel, Access, Crystal, or other applications.

Switch to 1st Windows Machine
Login to User-A 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.
Log off as User-A

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.
Log off as User-B

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.
Log off as User-C

Switch to 2nd Windows Machine
Perform the same steps for each of the Windows users.

Switch to 3rd Windows Machine
Perform the same steps for each of the Windows users.

Switch to 4th Windows Machine 
Perform the same steps for each of the Windows users.

 

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 the Prompt before Allowing Access. Check Allow this application to log in automatically. Select your Login as a user. (Normally "Admin") Click on the OK button.

Test the Connection

    Now that you have successfully established a connection to your QBAutoLogin, 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 Run button, and the requested query results will appear! If 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).
The log file shows about 7 seconds between starting to connect and a successful connection.
Once connected, the SQL seems to be processed very quickly.

Solution 1:

As you are using Auto Login, QuickBooks may take time to run in the background.

I want to inform you that If the company files in on the network location, there might be a possibility of a delay in opening the QuickBooks company file through a network.

Please keep the company file on the same machine & test again.

If the company file is on the same machine, please keep the QuickBooks application running no need to log in company file; it just keeps the QuickBooks window open. So you can eliminate the time 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?

 

Solution 2:

Please, set up auto-login. After you set auto-login, you can access QuickBooks without open it. Please follow the steps to set up auto-login.

When you set up Auto-login, please verify that the company file path is on QODBC and QuickBooks. These two paths should be the same. The QODBC company file path can be found via the QODBC setup screen -> General tab. You can discover the QuickBooks company file via QuickBooks UI. Press the 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 the company file.

Also, Refer: How to setup QODBC Driver to auto-connect to QuickBooks. (Video)


Tags: QuickBooks start, QuickBooks autostart, QuickBooks startup, QuickBooks connect, Hidden QuickBooksQuiet QuickBooks, Silent QuickBooks, Background QuickBooks, How to setup QODBC to run without QuickBooks, autologin, connection to data does not work unless Quickbooks is running even though I have that option turned on in the QODBC control panel, how to run without QuickBooks

(220 vote(s))
Helpful
Not helpful

Comments (8)
Garrett Curtis
28 August 2017 06:23 PM
Is it possible to have auto-login working with multi-user mode? I don't seem to be able to configure it using these instructions. Thank you!
Jack
30 August 2017 06:55 AM
Hi Garrett,

Please keep QuickBooks running (No need to log into the company file, just keep QuickBooks window open) and try to connect.

If you are still facing the issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Garrett Curtis
07 December 2017 12:59 AM
Jack,

Thank you for that tip!
leaving quickbooks open does allow for multi-user mode to automatically open quickbooks!!!

Now, my issue is that when I first open quickbooks (2016), there is a modal asking me to log into the default company file. The Qodbc VB Demo is not able to connect in this case, it tells me that:
"QB 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. For more details please visit : qodbc.com/links/1095"

Following the link shows how to disable popups from within quickbooks but this does not work on the "QuickBooks Login" modal that is displayed when QuickBooks starts up.
I tried to remove the company file from the list hoping it would not give me the auto-login modal but instead it gives me the new company modal instead with the same results!

My ultimate goal is to have Qodbc Server be able to login automatically on computer startup. With this solution someone still has to dismiss the modal after startup.

Any advice?
Thank You!
Jack
07 December 2017 11:16 AM
Hi Garrett,

You can manually manage this situation by editing QBWUSER.INI file. QBWUSER.INI file remembers information about which data file open last time.

For Example:
I have used test1.qbw last time then QBWUSER.INI contains below information:

DATAFILE=C:\Users\Public\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 16.0\test1.qbw

So you just need to remove company file name from it, in this example, we need to remove test1.qbw
DATAFILE=C:\Users\Public\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 16.0\

You can find QBWUSER.INI at:
C:\Users\"LoggedInUserName"\AppData\Local\Intuit\QuickBooks Enterprise Solutions 16.0

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Oni Anderson
03 January 2018 06:07 PM
Hello, I need a little help. I was able to set-up QBAutoLogin via QOBDC on my machine using VBDemo and Microsoft Query/Excel. My co-worker is able to set-up AutoLogin using VBDEMO but not Microsoft Query/Excel. When she tries to select From Other Sources From Microsoft Query, her datasource is not listed as an option.
Jack
04 January 2018 07:29 AM
Hi Oni,

I think your co-worker’s MS Office is of 64-bit & 64-bit application does not show 32-bit Data Source and due to this, he can’t get 32-bit DSN listed.

Please refer below mentioned link for How to Use QODBC with Microsoft Excel 2016 (64-bit):
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2772

You need to change “Remote DSN” of QRemote Client DSN with your auto login DSN which you have created/configured.

If you are still the facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Oni Anderson
11 January 2018 08:32 PM
Thanks! How do I "change “Remote DSN” of QRemote Client DSN with your auto login DSN which you have created/configured." Will I be able to set up AutoLogin with 64 Bit System?
Jack
12 January 2018 07:07 AM
Hi Oni,

If you don’t have created/configured auto login DSN on your co-worker’s machine, then you need to create/configure auto login 32-bit QODBC DSN. Once you have auto login DSN configured, then use same DSN as a “Remote DSN” in QRemote 64-bit DSN configuration.

For Example,
If your auto login DSN name is “QBAutoLogin”, then you need to select “QBAutoLogin” from “Remote DSN” drop-down in QRemote 64-bit DSN configuration.
Please refer below mentioned link & verify QRemoteDSN configuration details & check again:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2649/45/troubleshooting---qremote-error-could-not-connect-to-serverqremoteserver
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).