Using QuickBooks Online Data with VBA

Creating a Form in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks Online Data files similarly to Access databases.

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

A default database file, named Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. 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 the Create Tab:

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

Rename the button as per your requirement (In this example, the 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 the VBA code (In this example, it has a 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 the customer name):

*Note: Use "QuickBooks Online Data" instead of "QuickBooks Data."

Link the newly created module with the button click event:

Now click on the "Select Customer" button, and you will get the names 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 inserting, 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, the newly added customer name is updated with the name "Updated Testing VB":

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

Please refer to the code 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 Online Data;OLE DB Services=-2;"
sSQL = "SELECT Name FROM customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
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 Online Data;OLE DB Services=-2;"
sSQL = "Insert into customer (Name) values ('Testing VB')"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
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 Online 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")
oRecordset.CursorLocation = adUseClient
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 Online Data;OLE DB Services=-2;"
sSQL = "delete from customer where name='Updated Testing VB'"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
oRecordset.CursorLocation = adUseClient
oConnection.Open sConnectString
oConnection.Execute (sSQL)
sMsg = sMsg & "Record Deleted!!!"
MsgBox sMsg

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

Download Sample

Tags: QuickBooks Online, QBO, Access, MS Access, QODBC Online VBA, VBA script, sample VBA online