Knowledgebase
[QODBC-ALL] Using QuickBooks Data with VBA
Posted by Rajendra Dewani (QODBC Support) on 17 June 2014 07:37 AM

Using QuickBooks Data with VBA

For 64 Bit, Please refer: How to Configure QODBC to Work With 64-bit MS Access.

Problem Description:

Everything worked perfectly with the installation and linking to MS Access 2010 and QuickBooks Pro11. The tables I needed were related to my database in access. However, how do I update (or can I update) QuickBooks using a form (linked to my tables downloaded to Access Form using QODBC )in MS Access?

 

Solution:

You can insert/update/delete records by accessing local table. You need to write a VBA script to perform the SQL operation or Can, execute existing Queries Or can link the form to the existing table.

Please refer below steps which contains a sample tutorial for How QODBC can work with VBA. The tutorial is for sample reference only. For further requirements you need to develop a form or write scripts that will fetch data from a local access table and perform the insert operation in QuickBooks using QODBC.

 

Creating a Form in Microsoft Access:

Note: Microsoft Access is a product with many uses and will allow you to use your QuickBooks Data files in the same fashion as Access databases.

Open MS Access. You can find it via Windows Start->All Programs->Microsoft Office:

A default database file, name Database1 appears in the box. You can edit this to a more relevant name, such as QB Link. acid. Save the file as type "Microsoft Office Access Databases."

Download Sample

Then Click "Create" to create a blank database:

 

 

After  the new database is created, Create a New Form via the "Form Design" link in Create Tab:

 

After  the new form is created, Insert a button in the form:

Rename the button as per your requirement (In this example button name is "Select Customer"):

Now go to the button property window and set the property for the "On Click" event. Select "Event Procedure" from the dropdown list, then click on the "..." button:

The new window is open, which contains VBA code (In this example, it contains code button click event):

Now Insert the module in the form by Right-clicking on the form:

Write code in the module (In this example, I am writing code for the selection of customer name):

Link newly created module with button click event:

Now click on the "Select Customer" button, and you will get the Name of all customers:

Repeating the above steps can add functionality to your VBA code as per your requirement. In this example, I have added functionality for Insert, Update & Delete of customer records:

By clicking on the "Insert Customer" button, the New customer is added with the name "Testing VB":

By clicking on the "Update Customer" button, Newly added customer name is updated with the name "Updated Testing VB":

By clicking on the "Delete Customer" button, Newly added customer is deleted:

Please refer below code which is used in this example:

Select Customer:

Public Sub example select()
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")

oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic
sMsg = "**********************" & Chr(100)
Do While (Not oRecordset.EOF)
sMsg = sMsg & oRecordset.Fields("Name") & Chr(100)
oRecordset.MoveNext
Loop
sMsg = sMsg & "**********************"
MsgBox sMsg

oRecordset.Close
Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Insert Customer:

Public Sub exampleInsert()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Insert into customer (Name) values ('Testing VB')"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Added!!!"
MsgBox sMsg

Set oRecordset = Nothing
Set oConnection = Nothing
End Sub

Update Customer:

Public Sub exampleUpdate()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL

sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "Update customer set Name='Updated Testing VB' where name='Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)

sMsg = sMsg & "Record Updated!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Delete Customer:

Public Sub exampleDelete()
Const adOpenStatic = 3
Const adLockOptimistic = 3

Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"
sSQL = "delete from customer where name='Updated Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Deleted!!!"
MsgBox sMsg

Set oRecordset = Nothing
oConnection.Close
Set oConnection = Nothing
End Sub

Download Sample


Also, Refer:
How to Use QODBC with Microsoft Access 2007
Using QuickBooks Data with Access 2010 (Import Tables) 32-bit
Using QuickBooks Data with Access 2010(Linked Tables) 32-bit
How to Configure QODBC to Work With 64-bit MS Access
Microsoft Access 2003 and QODBC

(0 vote(s))
Helpful
Not helpful

Comments (10)
Dan Hesse
12 April 2016 08:04 PM
Hi, is there a way to get the "write" feature to work with Excel without using MS Access?

Thanks Dan
Jack
13 April 2016 06:46 AM
Hi,

You can create a macro in MS Excel. Please refer:

http://www.excel-easy.com/vba/create-a-macro.html
Liam
13 April 2016 02:04 AM
Is there any way to use this VBA in excel to write to serverside quickbook tables?
Jack
13 April 2016 06:50 AM
Hi,

You can create a macro in MS Excel. Please refer:

http://www.excel-easy.com/vba/create-a-macro.html

You can access QuickBooks Data remotely by installing QODBC on both machine (i.e. On QuickBooks application server & on your machine).

If your QuickBooks application is installed on another machine, then you can connect to QuickBooks from a remote machine using QRemote Server. You need to install QODBC on both machine.

You need to use QRemote DSN in VBA for accessing QuickBooks data remotely.

Please refer below mentioned article for Accessing QuickBooks Data Remotely using QODBC &QRemote. (Video):
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2517/

You need to run QRemote Server on QuickBooks machine & try to connect from your workstation using QRemote Client. QRemote Client should point to QuickBooks Server IP & Port.


If you are still facing issue, I kindly request you to please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:

http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need following information, I kindly request you to attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Colton
30 August 2017 06:11 PM
Using the above code, how can I use this to insert the data (from QuickBooks) into a local table in access rather than a table in QB, only if the data is different. I've been trying to get this to work but can't seem to get it to work in conjunction with QODBC
Jack
31 August 2017 07:03 AM
Hi Colton,

There is no code needed for importing QuickBooks Data into MS Access local table. You can import table directly.

Refer Using QuickBooks Data with Access 2016 / 365 / 2013 32-bit (Import tables):
https://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2471/57/using-quickbooks-data-with-access-2013import-tables-32-bit
don meehan
30 September 2017 12:23 AM
how would i connect in vba using DAO? It used to work with:

qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

now with 64-bit, QB 2016 - would i have to update Server, as i'm using the REMOTE for 64-bit?
Jack
03 October 2017 06:29 AM
Hi Don,

There is no need to change server value in the connection string. You need to use QRemote 64-Bit DSN in connection string as below & check again:

qd.Connect = "ODBC;DSN= QuickBooks Data 64-Bit QRemote;SERVER=QODBC"

If you are still facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Al
10 November 2017 02:06 PM
After insert a customer or invoice, how can I get the ListId of that customer or TxnId of that Invoice?
Jack
15 November 2017 07:30 AM
Hi,

You need to run below command using the VB demo to retrieve the last transaction created from the table.

sp_lastinsertid "Your tablename"

For example:
sp_lastinsertid invoice

SP_ LASTINSERTID command Returns a recordset with one row or multiple rows and two columns containing the last ListID or last TxnID from the last insert done and it error status on the current connection. The value is obtained from the return value of the last insert performed on the same connection for that table.

Please make sure that you are executing SP_LASTINSERTID command in current connection if the connection is reset you will not get the last inserted id.

Refer:
http://support.flexquarters.com/esupport/index.php?/Default/Knowledgebase/Article/View/2342/77/qodbc-stored-procedures-command-list
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).