[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

QODBC Transaction Samples.

Posted on 2012-03-20
12
Medium Priority
?
1,076 Views
Last Modified: 2012-03-22
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
0
Comment
Question by:Ei0914
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37745080
Are you doing this on a single machine? I can give you some DAO examples if you are.
0
 

Author Comment

by:Ei0914
ID: 37745164
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!
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37745196
I'm not clear on whether you want to import QuickBooks data into Microsoft Access or import Microsoft Access data into QuickBooks?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Ei0914
ID: 37745229
I need to move Access data to Quickbooks. Transactions only. I was able to successfully pull customers, chart of accounts, and all other lists.
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37745281
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,checkitemline, invoiceline and depositline.

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

Author Comment

by:Ei0914
ID: 37745295
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.
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37745353
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?
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37745421
I also assume that at least some of your checks, deposits and invoices multiple lines? That affects the SQL also.
0
 
LVL 8

Accepted Solution

by:
Annaliese Dell earned 2000 total points
ID: 37745530
You might try what I use but it requires the following:

1. DAO reference in Microsoft Access
2. your table field names must be identical to QODBC field names (this will make quick work of the whole process)
3. This only works with single line items - in other words, for checks with only one expense line or one item line, invoices with only one invoice line - if you need multiple, let me know because that requires more
4. Your table fields must be insertable - look them up with VBDemo to make sure
5. Do not include BillableStatus in your SQL because some account types do not allow it - I use a function for this but am keeping it simple
6. You must make sure the AccountRefListID, AccountRefFullName,VendorRefListID,VendorRefFullName,PayeeEntityRefListID,PayeeEntityRefFullName,CustomerRefListID,CustomerRefFullName already exist in QuickBooks. You only need the *ListID or *FullName of each, one or the other, not both.
7. Use a copy of your QuickBooks file until you are satisfied this works for you.
8. Your tables would have to contain only one transaction type per table. A table for checks, a table for invoices, etc.
9. Write to the LINE tables: checkexpenseline,invoiceline,depositline.

I hope all this isn't too confusing for you. It really is very easy once you get the hang of it. The code writes the SQL in the Visual Basic Immediate window so that should give you some examples.

I use this with QuickBooks 2009 Premier Contractor, Access 2002 and QODBC Version 9.

Function fncWriteData()
On Error GoTo fncWriteData_err
Dim db As DAO.Database, qd As DAO.QueryDef, rs As DAO.Recordset, q As String, QBtable As String
Dim strFields As String, strValues As String, t As String, x As Integer, s As String, c As Integer
t = InputBox("Enter the name of the Access table you wish to transfer to QuickBooks", "Table Name", "")

QBtable = InputBox("Enter name of the QuickBooks table you wish to write to", "Table Name", "")
Set db = CurrentDb
Set rs = db.OpenRecordset([t])
q = "qryTemp"
Set qd = db.CreateQueryDef(q)
qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
qd.ReturnsRecords = False
qd.ODBCTimeout = 60

With rs

    .MoveLast
    .MoveFirst
    Do While Not .EOF
    c = 1
    strValues = ""
    strFields = ""
        For x = 0 To .Fields.Count - 1
        If IsNull(.Fields(x).Value) = False Then
        
        If .Fields(x).Name <> "txnid" And InStr(1, .Fields(x).Name, "txnlineid", vbTextCompare) = 0 Then
            strFields = strFields & Chr(34) & .Fields(x).Name & Chr(34) & ","
        End If
        
        
            If .Fields(x).Name <> "txnid" And InStr(1, .Fields(x).Name, "txnlineid", vbTextCompare) = 0 Then
                If .Fields(x).Type = 10 Or .Fields(x).Type = 12 Then
                    strValues = strValues & "'" & Nz(.Fields(x).Value, "") & "',"
                ElseIf .Fields(x).Type = 8 Then
                    strValues = strValues & fncqbDate(.Fields(x).Value) & ","
                ElseIf .Fields(x).Type = 1 Or .Fields(x).Type = 20 Then
                    strValues = strValues & .Fields(x).Value & ","
                End If
            End If
        End If
        Next x

            strFields = "Insert into " & Chr(34) & Trim(QBtable) & Chr(34) & " (" & Left(strFields, (Len(strFields)) - 1) & ")"

        strValues = " VALUES (" & Left(strValues, (Len(strValues)) - 1) & ")"
        qd.SQL = strFields & strValues
        Debug.Print qd.SQL
        qd.Execute
    .MoveNext
    Loop
End With

fncWriteData_exit:
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
Exit Function
fncWriteData_err:
If Err.Number = 3012 Then
'Debug.Print q

    DoCmd.DeleteObject acQuery, q
    Resume
End If
MsgBox Err.Number & ": " & Err.Description
Debug.Print Erl, Err.Number, Err.Description
GoTo fncWriteData_exit
End Function
Function fncqbDate(myDate As Date) As String
fncqbDate = "{d '" & Year(myDate) & "-" & Right("00" & Month(myDate), 2) & "-" & Right("00" & Day(myDate), 2) & "'}"
End Function

Open in new window

0
 

Author Closing Comment

by:Ei0914
ID: 37754835
Thanks a lot. Am playing with it now. Got the form developed and that works great.
0
 

Author Comment

by:Ei0914
ID: 37754931
Getting ODBC Call Failed -- at qb.Execute
0
 
LVL 8

Expert Comment

by:Annaliese Dell
ID: 37755088
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?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question