Knowledgebase
[QODBC-Desktop] QODBC and Microsoft Access
Posted by Juliet (QODBC Support) on 05 January 2010 07:09 AM

QODBC and Microsoft Access

For MS Access 64-bit, Please refer: To how to Configure QODBC to Work With 64-bit MS Access.

Creating a Table in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks data files in the same fashion as Access databases.

Open MS Access 2010, and make sure you are using the 32-bit version of MS Access:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QB Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then Click "Create" to create a blank database:

In the newly created database, you will find a default blank table. Please close this default table:

You could directly extract QuickBooks records to this table. But in this article, we will show you how to build a linked table to access QuickBooks.

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button at the External Data menu:

Under the Get External Data window, you will see two options:

Import the source data into a new table in the current database. This option will directly extract tables into Access Database, you can view and edit these tables, but all modifications will not be uploaded unless you build another query.

Link to the data source by creating a linked table: this option will create a table just like the first option but will keep its connection to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in complete sync with QuickBooks.

This article will show you how to build the linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, then you will get a Select Data Source window:

 

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as the example.)

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please, select the tables you want to extract. Multiple Selection is available.

After the Linking table is successful, you will see linked tables been created in your MS Access file under "Tables":

You can view the property of these linked tables by right-clicking the linked table->select linked table manager:

If you want to view QuickBooks records in this linked table, double-click the linked server, then you will see the records:

FYI, the linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table. QODBC will automatically update the modification to QuickBooks.

 


Note: When you run across an application that is not entirely ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

Also, Refer :
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2013(Import Tables) 32-bit
Using QuickBooks Data with Access 2013 (Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access
Microsoft Access 2003 and QODBC

(430 vote(s))
Helpful
Not helpful

Comments (8)
Ranveer
20 September 2012 07:41 PM
It's great to find an expert who can explain thngis so well
Nicolas Jacob
15 April 2013 06:25 PM
I'm trying to connect QB with Access. When QB opens the application integration windows, it's telling me that I only have the read access. I can't write. I have the full read/write version, why is that?
connie mcaluney
02 June 2014 08:37 PM
I installed the qodbc driver and linked two tables from my .qbw file to an Acess 2010 DB. The link process took several minutes and then took about 5 minutes to open the table (the Vendor table with 153 records).

I tried the vbDemo program. It took about 1 minute to load the table list and the program stopped responding when I executed the query to show the customer table. Any helped would be appreciated.
Jack
07 July 2014 12:40 PM
Hi Connie,

I would suggest you to enable QODBC status panel via QODBC Setup Screen->Message Window->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, will be shown a window with information on what QODBC is working at. Please note the step on which QODBC spends most time or get stuck and share it with us.

I also suggest you to try executing below command on VB Demo and see it can resolve the issue:

SP_OPTIMIZEFULLSYNC ALL

Refer:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2459/0/how-to-execute-sp_optimizeupdatesync-or-sp_optimizefullsync-for-selected-tables

You can simply find VB Demo at Windows Start->All Programs->QODBC Driver for use with QuickBooks, but if you could not find it, please refer below article:

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2416/0/troubleshooting-vb-demo-is-missing

If you are not familiar with VB Demo, please refer below article first:

http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2364/0/how-to-use-the-vb-demo-for-testing

If you are still facing issue, I kindly request you to 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

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
Sheilene Creecy
04 February 2016 05:35 PM
I have the free version of QODBC with QB Enterprise. I access QB through Rights Network. I want to use Microsoft Access to connect via QODBC to multiple QB company files. Can I do this? How do I create new DSNs?
Jack
05 February 2016 02:21 PM
Hi Sheilene,

I would like to inform you that there is a limitation with the QuickBooks SDK that it can connect only 1 company file at a time on a machine. QODBC using QuickBooks SDK & QRemoteServer uses QODBC thus you will able to open one company file at a time on a machine.


Yes, you can create multiple DSN that is pointing to different company files and use this DSN in your configuration. You can access multiple company file one by one using multiple DSN.


I kindly request you to please refer below mentioned article for creating DSN:
http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/1929/48/how-to-create-or-configure-a-qodbc-dsn

Or

You can use multiple machines, each machine having QuickBooks Installed and logged in to different company file. Application machine uses QRemote Client (different DSNs) to connect to those machines and accessing QuickBooks company file data simultaneously.

Please refer below-mentioned link for How to use QRemote:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2483/0/using-quickbooks-data-remotely-via-qodbc

For more details, 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

Brandon Hurst
12 May 2016 02:59 PM
I try to update a record and get an error that qodbc doesn't support changes in linked tables. I've set my connection to not be read only.
Jack
13 May 2016 10:47 AM
Hi Brandon,

Please make sure that updating updateable field. There are few columns, values for which automatically generated by QuickBooks SDK and are not allowed during Insert/Update. Example ListID or TxnID. When you try to update non updateable field in your query, QODBC will show error "[QODBC]Field not allowed in update "

Please refer below mentioned link for more details:

http://support.flexquarters.com/esupport/index.php?/Knowledgebase/Article/View/2526

If you are still facing issue, I kindly request you to 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, I kindly request you to 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
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).