Using QuickBooks Online Data with VBA

Creating a Form in Microsoft Access:

Note: Microsoft Access is a product with many uses, and will allow you to use your QuickBooks Online 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.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 "Form Design" link in Create Tab:

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

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

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

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

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

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

*Note: use "QuickBooks Online Data" instead of "QuickBooks Data"

Link newly created module with button click event:

Now click on "Select Customer" button, you will get Name of all customer:

By repeating above steps you can add functionality in your VBA code as per your requirement. In this example I have added functionality for Insert, Update & Delete of customer record:

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

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

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

Please refer below code which is used in this example:

Select Customer:

Public Sub exampleSelect()
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")

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")
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")
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")
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