VBA Snippets: Adding an SQL ODBC Connection in Microsoft Dynamics GP

This post is part of the series on VBA Snippets.

There is an ADO connection available to VBA within Microsoft Dynamics GP which you can use, but there are some steps you need to follow to use it.

The first step is to declare the variable which will hold the connection.

Private madoConn AS ADODB.Connection

Then you need to create the connection which this example does using a Connect subroutine:

Private Sub Connect()
	If madoConn.State <> adStateOpen Then 
		Set madoConn = UserInfoGet.CreateADOConnection
		madoConn.DefaultDatabase = UserInfoGet.IntercompanyID
	End If
End Sub

It checks if the connection is already open and, if not, uses the UserInfoGet object which holds the connection detail exposed in Dynamics GP; I am also using the same object to set the default database property.

Once connected you can use the connection to execute SQL queries; I’ll show some examples of this in later posts.

When you’re finished with the connection, you can close and destroy the connection:

Private Sub Disconnect()
    If madoConn.State = adStateOpen Then madoConn.Close
    Set madoConn = Nothing
End Sub