Access and QuickBooks

[AngelSpeaks]

Has anyone connected QuickBooks files to Access? If so, what did you use. I have Access 2019 and QuickBooks Accountant 2013. It looks like I would have to purchase an ODBC driver.

Thanks

 

[The_Doc_Man]

Connect to QuickBooks Online from Microsoft Office

Work with QuickBooks Online data in your Microsoft Office applications.

www.easysoft.com

Here's a starting point. This might lead to something useful. Since I don't use QB, I can only take as far as the web search results.

 

[AngelSpeaks]

Connect to QuickBooks Online from Microsoft Office

Work with QuickBooks Online data in your Microsoft Office applications.

www.easysoft.com

Here's a starting point. This might lead to something useful. Since I don't use QB, I can only take as far as the web search results.

Thanks. That's for QBO. I don't know why it doesn't work for QBD.

 

[AngelSpeaks]

Actually they do have a QBD version. You have to search their site. It's 250 pounds per year.

 

[GPGeorge]

Has anyone connected QuickBooks files to Access? If so, what did you use. I have Access 2019 and QuickBooks Accountant 2013. It looks like I would have to purchase an ODBC driver.

Thanks

I know a few people who have connected Access to QuickBooks, although I don't use QuickBooks. The issue is going to be the driver, as you've found. I suppose you could get trial versions of one or two to try out and see which is a good fit for your needs.

If you want to hire a consultant who works with Access and QuickBooks to help you get started, that might also be a possibility. On the other hand, once you get a connector, the folks here could probably help you work through the issues.

 

[Pat Hartman]

I would never use the online version of any software for data that should always be within my control. It's been a long time but I have used the QB driver to access the desktop version. QB also offers imports/exports so you can use batch transfers for some things. I don't know if QB still offers a desktop version. If it does, there will be an ODBC driver for it also.

 

[AngelSpeaks]

I know a few people who have connected Access to QuickBooks, although I don't use QuickBooks. The issue is going to be the driver, as you've found. I suppose you could get trial versions of one or two to try out and see which is a good fit for your needs.

If you want to hire a consultant who works with Access and QuickBooks to help you get started, that might also be a possibility. On the other hand, once you get a connector, the folks here could probably help you work through the issues.

Thanks George

 

[Cotswold]

I supplied software to quarrying and waste management companies for over 30 years without a single problem, or error. I always resisted requests to directly connect into the accounts system my customers used.

In Britain they were generally SAGE and PEGASUS, plus one or two others. I didn't offer QuickBooks because it was aimed at smaller companies. One man and his dog, small builders, or car dealers. None of which I would want to deal with anyway. I would import data, usually the customer list from SAGE and export invoices in a CSV for SAGE to import. As so often is the case inexperienced book keepers used the accounting systems on a daily basis to enter data and the possibility for errors I thought was high.

I would create a file that SAGE would import and update the sales and nominal ledgers. I would also export new customers that didn't already exist in SAGE. After that, SAGE would then validate all data before importing, which meant the two systems were independent. If I linked directly into SAGE and something went wrong, SAGE I presumed would stonewall, whilst my client will be going barmy and I would end up with the bill to put it right. The import protected me from that. You just need a system to prevent, or correct a duplicate import. SAGE will happily import duplicate data!

The QuickBooks import/export file I understand to be basically standard OFX files. The same as most banks use for your exporting accounts data. So fairly easy to create your OFX import/export text file routines. If you are new to OFX, don't think that it is a series of records, as a RandomAccess text file. It is a Sequential File with one record.( I did have a discussion in the forum about that but the other poster(s) somehow didn't see that, despite my reiteration)

In summary I would never link directly into any accounting software. Some do and good luck to them. I'll present valid data to the accounting system and the accounting system will only import and update its ledgers with what it concludes to be valid data. Therefore providing me with confirmation that my export file is correct in every way. But always your choice of course, I'm not saying you shouldn't.

 

[AngelSpeaks]

I supplied software to quarrying and waste management companies for over 30 years without a single problem, or error. I always resisted requests to directly connect into the accounts system my customers used.

In Britain they were generally SAGE and PEGASUS, plus one or two others. I didn't offer QuickBooks because it was aimed at smaller companies. One man and his dog, small builders, or car dealers. None of which I would want to deal with anyway. I would import data, usually the customer list from SAGE and export invoices in a CSV for SAGE to import. As so often is the case inexperienced book keepers used the accounting systems on a daily basis to enter data and the possibility for errors I thought was high.

I would create a file that SAGE would import and update the sales and nominal ledgers. I would also export new customers that didn't already exist in SAGE. After that, SAGE would then validate all data before importing, which meant the two systems were independent. If I linked directly into SAGE and something went wrong, SAGE I presumed would stonewall, whilst my client will be going barmy and I would end up with the bill to put it right. The import protected me from that. You just need a system to prevent, or correct a duplicate import. SAGE will happily import duplicate data!

The QuickBooks import/export file I understand to be basically standard OFX files. The same as most banks use for your exporting accounts data. So fairly easy to create your OFX import/export text file routines. If you are new to OFX, don't think that it is a series of records, as a RandomAccess text file. It is a Sequential File with one record.( I did have a discussion in the forum about that but the other poster(s) somehow didn't see that, despite my reiteration)

In summary I would never link directly into any accounting software. Some do and good luck to them. I'll present valid data to the accounting system and the accounting system will only import and update its ledgers with what it concludes to be valid data. Therefore providing me with confirmation that my export file is correct in every way. But always your choice of course, I'm not saying you shouldn't.

Thanks for your thoughts. My version of QB, 2013, has limited exporting. The Vendor and Customer lists have a direct to Excel. The Transactions are a report which requires a lot of editing, etc.

I will check into OFX. I don't remember seeing it. Thanks

 

[Pat Hartman]

I will check into OFX. I don't remember seeing it. Thanks

QB has its own import file formats. As I recall my process was similar to what @Cotswold described. The ODBC was used primarily to extract from QB or link to it for live inquiries. For transaction data, I also always used the export to file and then import method because that way I didn't have to figure out how to correctly tie together the data into the necessary tables although I think the QB ODBC driver used views and took care of the double entry part of the problem for me but it was a long time ago. The only direct updates I ever did were to things like product and customer lists.

Also, look for a way to automate the imports. Either through scheduling or by a feature that allows Access to trigger the import. This would help to minimize the risk of importing any of your export files multiple times. If you can't automate this, you might be able to create a procedure in Access that each time it exports a new transaction file, it opens the previous one and verifies that it has been imported. If the import happened, then move the transaction file to the archive folder.

And finally, whenever I do import/export processing, I create log tables so I can track what I imported/exported in the Access app. this also helps to prevent the Access app from importing the same file multiple times. Also when I do an import/export, I create the log record first so I can include a BatchID in the data file which allows me to find any set of data and export it again or delete it so I can import again after correcting a mistake.

 

[Cotswold]

To prevent duplicate imports each import file would have a unique number. That number would be in a System File and incremented each time a new import file was created. The user would then import the records to the accounts system. Once complete they would run a batch file that renamed the .CSV file to .VSC so it cannot be imported again.

If they happen to import the file twice into accounts, they would run an update that created a new CSV but the destination instruction for each line that was "SI" (for sales invoice) would be replaced with "SC"(sales credit). The accounts would then have three records that month
"SI"....."Sales of Type 10055".....
"SI"....."Sales of Type 10055"....
"SC"....."Credit for Type 10055"....

And the sales and nominal ledgers are corrected. They tend not to do that twice because then their customer receives their statement and rings up without thinking pointlessly asking why there are three entries for the month for each invoice. Excuses then have to be made up as to why they messed up. Whoever did the double import was called to the phone to explain just why that was. So they tended to ensure that it wasn't repeated.
But the point is that they could correct the matter easily and without compromising the accounts. They also didn't need to be an accountant to correct it. Plus, it was achieved in a way that the accountants and auditors understood.

If something went wrong with their accounts for some unconnected reason and they had to step back in time. (a very rare and unusual occurrence) They could then rename the VSC files back to CSV and import the invoices to bring their accounting system back up to date with little effort.

Amended-------------------------
I didn't mention that each invoice line in each unique CSV file had the batch number as a note on each invoice in the batch. Say "Waste charges 10055"
They could then link and compare invoices created in my systems to the invoice charges in accounts by that unique number and the import filename. I have edited the above to show it. In my system the user could also reprint or re-email invoices by batch which makes it easier if that is required.