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.

 

[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

 

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