QODBC Transaction Samples.
Hi Experts. I'm new to QODBC and am ready to pull my hair out. Is there an easier way to convert to Quickbooks from an Accounting Database written in Access. I have managed to import all the list data via import from Excel -- but the transactions, at least for the current year can't be imported. I have tested dozens of samples I pulled from the internet, but none seem to be what I'm looking for. I've joined a few forums and intend to keep searching, but thought I'd post this on EE as well.
I need to import Checks and Deposits; Open Payables; Sales Orders and Invoices.
Thanks in advance for any help.
Converting from Access 2007 to Quickbooks 2010.
QODBC 2012 Version 12.00.00.287
I need to import Checks and Deposits; Open Payables; Sales Orders and Invoices.
Thanks in advance for any help.
Converting from Access 2007 to Quickbooks 2010.
QODBC 2012 Version 12.00.00.287
Annaliese Dell
Are you doing this on a single machine? I can give you some DAO examples if you are.
ASKER
I am -- once the data is converted, I will no longer need it -- as I won't be delivering it to the client (the code that is)...
Any help is GREATLY appreciated. I don't find this very intuitive. Is it just me??
Thanks!
Any help is GREATLY appreciated. I don't find this very intuitive. Is it just me??
Thanks!
I'm not clear on whether you want to import QuickBooks data into Microsoft Access or import Microsoft Access data into QuickBooks?
ASKER
I need to move Access data to Quickbooks. Transactions only. I was able to successfully pull customers, chart of accounts, and all other lists.
Okay. For that, you need to know the QODBC field names and if they are insertable to avoid error messages.
You insert checks, bills, invoices and deposits to checkexpenseline,checkitem
When you look at a QuickBooks check, the top portion (in color) is data from the check table. The information below the check, the checkstub I guess you call it, is data from the checkexpenseline table. Click on the items tab and that data is from the checkitemline table.
The following tutorial walks you step-by-step to make a simple Microsoft Access form that imports QuickBooks tables. This form will help you with the field names and their updateable, insertable and queryable properties. Also the length and data type. You cannot insert data into QuickBooks if it exceeds the allowable field length.
http://www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html
Many run into problems when trying to insert to non-insertable fields or when they exceed field length and do not understand what they are doing wrong.
If you name all your Microsoft Access field names the same as the corresponding QODBC field names, you can iterate through the table fields to build your SQL statements and write the data.
Let me know if you need more explanation on any of this.
Lastly, use this function for dates in your SQL.
Function fncqbDate(myDate As Date) As String
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function
QODBC requires this date format. Another area some people fail and wonder why.
Again, if you need further explanation, let me know and I'm happy to help. :)
There is more but this will get you started.
Gotta go...be back in a bit...
You insert checks, bills, invoices and deposits to checkexpenseline,checkitem
When you look at a QuickBooks check, the top portion (in color) is data from the check table. The information below the check, the checkstub I guess you call it, is data from the checkexpenseline table. Click on the items tab and that data is from the checkitemline table.
The following tutorial walks you step-by-step to make a simple Microsoft Access form that imports QuickBooks tables. This form will help you with the field names and their updateable, insertable and queryable properties. Also the length and data type. You cannot insert data into QuickBooks if it exceeds the allowable field length.
http://www.vbquick.com/2011/10/quickbooks-microsoft-access-table-qodbc.html
Many run into problems when trying to insert to non-insertable fields or when they exceed field length and do not understand what they are doing wrong.
If you name all your Microsoft Access field names the same as the corresponding QODBC field names, you can iterate through the table fields to build your SQL statements and write the data.
Let me know if you need more explanation on any of this.
Lastly, use this function for dates in your SQL.
Function fncqbDate(myDate As Date) As String
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function
QODBC requires this date format. Another area some people fail and wonder why.
Again, if you need further explanation, let me know and I'm happy to help. :)
There is more but this will get you started.
Gotta go...be back in a bit...
ASKER
I'm confused. I need to pull from Access not Quickbooks. I have pulled data from QB many times but have not pulled data from Access into Quickbooks.
Yes, I do understand you are pulling from Access and putting into QuickBooks.
The reason I suggested importing the QB table information is so you know what field names to use when writing your SQL to put your Access data into QuickBooks. And also the names of the tables that you want to write to in QuickBooks.
For example, if you have a check transaction in your database and want to write it to QuickBooks, you need to know that the field name for date is TxnDate, check number is RefNumber, Account is AccountRefListID or AccountRefFullName, etc. You need these field names for your SQL.
It beats constantly using VBDemo to look up field names and their insertable values when writing SQL.
Did that help?
The reason I suggested importing the QB table information is so you know what field names to use when writing your SQL to put your Access data into QuickBooks. And also the names of the tables that you want to write to in QuickBooks.
For example, if you have a check transaction in your database and want to write it to QuickBooks, you need to know that the field name for date is TxnDate, check number is RefNumber, Account is AccountRefListID or AccountRefFullName, etc. You need these field names for your SQL.
It beats constantly using VBDemo to look up field names and their insertable values when writing SQL.
Did that help?
I also assume that at least some of your checks, deposits and invoices multiple lines? That affects the SQL also.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot. Am playing with it now. Got the form developed and that works great.
ASKER
Getting ODBC Call Failed -- at qb.Execute
Copy the last SQL statement from the Immediate Window of the Visual Basic Editor and paste it into VBDemo to see if you get more information about the error.
Typical errors are:
vendor, customer or account in your database does not already exist in QuickBooks
using non-insertable fields such as Amount (must rename to ExpenseLineAmount or ItemLineAmount)
using non-nullable fields with null values from your database
not using Full account names such as using 'Liaibility Insurance' instead of 'Insurance:Liability' Insurance when subaccounts are used
making sure your database field names exactly match QuickBooks field names
This tutorial may help: http://www.vbquick.com/2012/03/migrate-quickbooks-microsoft-access-1.html
Can you post the last SQL here?
Typical errors are:
vendor, customer or account in your database does not already exist in QuickBooks
using non-insertable fields such as Amount (must rename to ExpenseLineAmount or ItemLineAmount)
using non-nullable fields with null values from your database
not using Full account names such as using 'Liaibility Insurance' instead of 'Insurance:Liability' Insurance when subaccounts are used
making sure your database field names exactly match QuickBooks field names
This tutorial may help: http://www.vbquick.com/2012/03/migrate-quickbooks-microsoft-access-1.html
Can you post the last SQL here?