ADO Connections through VBA in Dynamics GP v9

This has come up time and time again on message boards I frequent on the web. People are always asking how they can access SQL Tables and other objects through VBA to extend GP. It's pretty simple to add a field to a window with Modifier or create a user form in VBA but many seem to wonder how to access the database to really make things happen.

You can find all you need to know on Customer/PartnerSource. I'm only disclosing the same information here and a little of what I have learned to complement that information.

When Microsoft Dynamics GP 9.0 was released, a change was made in the password policy. This change required changes in how Microsoft Dynamics GP Dexterity works with passwords and in how the RetrieveGlobals.dll file works with passwords. The RetrieveGlobals.dll file was available for all earlier versions but does not work with Microsoft Dynamics GP 9.0. Therefore, you must replace the RetrieveGlobals.dll file with the new RetrieveGlobals9.dll file. The new RetrieveGlobals9.dll file is an ActiveX file. It returns the following information:

•The current user ID
•The current company to which you are logged in
•The current SQL data source
•The current user date in Microsoft Dynamics GP 9.0

The RetrieveGlobals9.dll file also returns an ActiveX Data Objects (ADO) connection object that lets you connect to Microsoft Dynamics GP data. The RetrieveGlobals9.dll file works only with version 9.0 of Microsoft Dynamics GP. Additionally, this file works only if you have one session of Microsoft Dynamics GP running and if you are logged into this session. The RetrieveGlobals9.dll file is for use only in Modifier with VBA or in Integration Manager. Modifier with VBA and Integration Manager both require that Microsoft Dynamics GP is open and running. To download the RetrieveGlobals9.dll file together with its documentation, visit one of the Microsoft Web sites.

After upgrading all of our GP v7 VBA code to Dynamics GP v9 I found that I might have something valuable to share on this topic that you cannot readily find on CustomerSource. This is how I have organized my code to get best results from the RetrieveGlobals9.dll.

1. Create a Get_NewConnection SubRoutine behind each window for which ADO connections will be required:

Public Sub Get_NewConnection()
'This code clears all variables used and closes the connections.

If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close

'Initialize the connection string variables
Set userinfo = Nothing
Set userinfo = CreateObject("RetrieveGlobals9.retrieveuserinfo")
luserid = userinfo.retrieve_user()
lintercompanyid = userinfo.intercompany_id()
lsqldatasourcename = userinfo.sql_datasourcename()

'Use the connection property to get a connection object.
Set pconnection = userinfo.Connection

'Create an ADO command object.
Set cmd = CreateObject("ADODB.Command")

'set the database to the currently logged in db.
pconnection.DefaultDatabase = lintercompanyid

cmd.ActiveConnection = pconnection

'adCmdText.
cmd.CommandType = 1

End Sub

2. Create a CloseConnection SubRoutine behind each window you create a Get_NewConnection:

Public Sub CloseConnection()

'This code clears all variables used and closes the connections.
If precordset.State = adStateOpen Then precordset.Close
If pconnection.State = adStateOpen Then pconnection.Close
Set cmd = Nothing
Set pconnection = Nothing

End Sub

3. Call the Get_NewConnection to create new connections and CloseConnection to close them. I found that on some windows you can put these calls on the After Open and After Close events. On others you will have to reinitialize the connections and close them for every new call you make to the database.

4. Here's an example of how we populate a checkbox placed on EFT Customer Maintenance with Modifier based on whether or not the customer's pre-note has been confirmed which is stored in a table called "Dealer" outside of Dynamics GP.

Private Sub CustomerID_Changed()

On Error GoTo Proc_Error:

Get_NewConnection

cmd.CommandText = "SELECT PreNoteStatus FROM " & lintercompanyid & ".dbo.Dealer where AccountNumber = '" & CustomerID & "'"

Set precordset = cmd.Execute

If precordset.EOF = False Then
If RTrim(precordset.Fields("PreNoteStatus")) = 1 Then
PreNoteConfirmed = 1
Changed = False
Else
PreNoteConfirmed = 0
Changed = False
End If
Else
PreNoteConfirmed = 0
Changed = False
End If

GoTo Procedure_Exit:

Proc_Error:

MsgBox Error$ & " " & Error, vbOKOnly, "CustomerID_Changed"

Procedure_Exit:

CloseConnection

End Sub