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