Does anybody know if it is possible to link a Quickbook file to Access and reference its contents as if it were another database?
Does anybody know if it is possible to link a Quickbook file to Access and reference its contents as if it were another database?
I haven't done it myself but a friend who was here with me for a while (in my current job location) had to do that and I guess it isn't the easiest thing to do. It does require an ODBC driver from Intuit and, according to my friend, it is quite the pain in the rear.
So it is doable, at least. Prithee, how does one go about obtaining an ODBC driver from Intuit?
I may be wrong on that as I've been searching for this. I will try calling my friend and ask him what he was needing to do.
There is a program called Parts&Vendors (MS Access based) by Trilogy Design ( www.trilogydesign.com ) that claims to be able to transfer certain data to Quickbooks. They don't support the feature because they say Intuit could change something. They have the below statement in their Help file:
NOTE: Trilogy Design cannot provide technical or organizational support for your use of the QuickBooks software. QuickBooks is a feature-rich program and is fully supported by its publisher, Intuit. If you are not prepared to figure it out yourself, your accountant may know of a local consultant who configures QuickBooks for client companies (ours did). To optimize your use of QuickBooks' features for your particular business activity, we suggest that you take advantage of those resources.
So it would appear that the ability to transfer data to Quickbooks worked at one point. Sorry, I know that is not helpful but my point was to let you know someone else had done it.
Last edited by nicknameoscar; 07-14-2011 at 06:21 AM. Reason: spelling correction
Actually, the link is needed to make data in Quickbook available to include in Access reports (also using tables in Access), such as the orders (from Quickbook) credited to each salesperson (from Access, with the ID key as part of the Quickbook data) for a given period of time (derived from the date in Quickbook). Changing Quickbook from Access is not part of the program.
My apologies! Your requirement was right there in black and white in your first post. I must be dyslexic.
I called my friend yesterday (sorry for forgetting to post but I got busy and it slipped my mind). He said that third party tools are about the only way you are going to get this. He believes that Quickbooks now uses MySQL as the backend but that it still is not a simple matter to connect to it. So a third-party tool (do a web search) is probably the only way you are going to get what you need. My apologies for stating that it only required an ODBC driver. I had thought that was what he had said but that was over a year and a half ago so I plead forgetfulness.
But it is doable and I've got a starting point, which beats a poke in the eye with a sharp stick. Thanks.
What I have done with other accounting software packages is to output a report of the needed data to a disk file, then write an Access VBA process to read that report, line by line, and extract the data from the report and write it to an Access table, then use that data with my other Access tables to print the required final report in Access.
I have the free version of QuickBooks and I just asked it to print the summary report of invoices. On the print dialog box, there is an option to print to a disk text file. From this text file, I could write a VBA routine that would check columns 42-51 to be a date. If it is a date, that means this line is a detail line in the report. The customer info is in columns 6 - ??, the invoice number in the next set of columns, etc.
YOUR REPORT WILL BE DIFFERENT. I'm only giving these columns as a sample. But this is a relatively easy way to get the data you need out of most all accounting packages.