How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions - Great Plains 8.0

INTRODUCTION

When you use ActiveX Data Object (ADO) to connect to Microsoft SQL Server from a Microsoft Visual Basic for Applications (VBA) form, the best practice is as follows:

Open the connection when the window is open.
Close the connection when the window is closed.

When you follow this practice, the connection is available when the window is open. Therefore, the connection is not continuously opened and closed. However, the connection is continuously opened and closed if the connection code is contained in an individual event script.

Consider the following scenario:

You click the Close box.
You receive the following message:
Do you want to save?
You click Cancel.

In this scenario, the VBA Window_AfterClose() code is still run. Also, the connection is closed even though the window is still open.

Note You will receive an error the next time that you try to use the connection that is now closed.

↑ Back to the top

More Information

To avoid this issue, use the following Boolean variable:

OKtoCloseADO

You can use this variable to make sure that the connection does not close if a user ends the closure of a window. To open and close a connection for a form, use the appropriate method to apply the example fix code.

Method 1: Microsoft Dynamics GP 10.0

Use the following example code.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String

Dim OKtoCloseADO As Boolean

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    ' ADO Connection
    Set cn = UserInfoGet.CreateADOConnection
    ' Use a client-side cursor so that a recordset count can be obtained later.
    cn.CursorLocation = 3
    ' Set the database to the currently logged-in database.
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub

Private Sub Window_BeforeClose(AbortClose As Boolean)
    OKtoCloseADO = True
End Sub

Private Sub Window_AfterClose()
    If OKtoCloseADO Then
        ' Close the ADO Connection.
        If rst.State = adStateOpen Then rst.Close
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
        Set rst = Nothing
        Set cmd = Nothing
    End If
End Sub

Private Sub Window_AfterModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
    Select Case PromptString
        ' Save the document from the Dialog box (browse or close the window).
        Case "Do you want to save or delete the document?"
            If Answer = dcButton3 Then
                OKtoCloseADO = False
            End If
     
        Case Else
     End Select
End Sub

Method 2: Microsoft Business Solutions - Great Plains 8.0

Use the RetrieveGlobals.dll file to return an ADO connection object that lets you connect to Microsoft Dynamics GP data. To download the RetrieveGlobals9.dll file together with its documentation, visit the following Microsoft Web sites.

https://mbs.microsoft.com/customersource/documentation/howtodocuments/modifiervbasamples80.htm

Use the following example code.

Dim cn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim sqlstring As String

Dim OKtoCloseADO As Boolean

Private Sub Window_BeforeOpen(OpenVisible As Boolean)
    Dim userinfo As New RetrieveGlobals.retrieveuserinfo
    Dim luserid As String
    Dim lintercompanyid As String
    Dim lsqldatasourcename As String
    Dim lsqlpassword As String
    Dim constring As String

    ' RetrieveGlobals section.
    lsqldatasourcename = userinfo.sql_datasourcename()
    luserid = userinfo.retrieve_user()
    lsqlpassword = userinfo.sql_password()
    lintercompanyid = userinfo.intercompany_id()
    'MsgBox (luserid & " " & lsqlpassword & " " & lintercompanyid & " " & lsqldatasourcename)

    ' Create a connection string.
    constring = "Provider=MSDASQL" & _
                ";Data Source=" & lsqldatasourcename & _
                ";User ID=" & luserid & _
                ";Password=" & lsqlpassword & _
                ";Initial Catalog=" & lintercompanyid
    'MsgBox constring

    ' ADO connection section.
    With cn
        .ConnectionString = constring
        .CursorLocation = 3 ' adClient
        .Open
    End With
End Sub

Private Sub Window_BeforeClose(AbortClose As Boolean)
    OKtoCloseADO = True
End Sub

Private Sub Window_AfterClose()
    If OKtoCloseADO Then
        ' Close the ADO Connection.
        If rst.State = adStateOpen Then rst.Close
        If cn.State = adStateOpen Then cn.Close
        Set cn = Nothing
        Set rst = Nothing
        Set cmd = Nothing
    End If
End Sub

Private Sub Window_AfterModalDialog(ByVal DlgType As DialogType, PromptString As String, Control1String As String, Control2String As String, Control3String As String, Answer As DialogCtrl)
    Select Case PromptString
        ' Save the document from the Dialog box (browse or close the window).
        Case "Do you want to save or delete the document?"
            If Answer = dcButton3 Then
                OKtoCloseADO = False
            End If
     
        Case Else
     End Select
End Sub

↑ Back to the top

Keywords: kbexpertiseadvanced, kbmbspartner, kbmbsmigrate, kbhowto, kbexpertiseinter, kb

↑ Back to the top

Article Info

Article ID	:	942327
Revision	:	1
Created on	:	1/7/2017
Published on	:	1/21/2013
Exists online	:	False
Views	:	130

Microsoft KB Archive Search

How to use ActiveX Data Object (ADO) with VBA on a window with Microsoft Dynamics GP and with Microsoft Business Solutions - Great Plains 8.0

INTRODUCTION

More Information

Method 1: Microsoft Dynamics GP 10.0

Method 2: Microsoft Business Solutions - Great Plains 8.0