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

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

Read original post VBA Snippets: Adding an SQL ODBC Connection in Microsoft Dynamics GP at azurecurve|Ramblings of a Dynamics GP Consultant