Using ADO with VBA with Report Writer

As a follow on to the Using VBA with Report Writer post, I would like to discuss using ActiveX Data Objects to access data from SQL Server while using the Report Writer.

Sometimes it is not possible to use Report Writer to create a table relationship to access the data that you want to add to a report. In these situations, you can use Visual Basic for Applications (VBA) to access field in any Microsoft SQL Server table by using ActiveX Data Object (ADO) to connect.

The best practice is as follows:

• Open the connection when the report starts.
• Access the desired data using the before section events.
• Close the connection when the report ends.

When you follow this practice, the connection is opened once when the report is opened, and is not continuously opened and closed as the report prints each section.

Assuming that the report fields (or fields in tables already linked to the report) can be used to uniquely identify the desired row in the SQL table, you can create a SQL Select statement with a where clause based on the values from the report.  You can then create a blank calculated field to return the data from VBA to the report.

Note: Placeholder names have been used in the example code.  They are surrounded by braces {}.

Sample Code 

The methods of opening the connection to SQL Server are different depending on the version of Microsoft Dynamics GP being used.

Note: For the code below to work you will need to make sure that you have a Reference added to Microsoft ActiveX Data Objects X.X Library (I normally select version 2.8).  For v8.0 you will need a reference to the RetriveGlobals.dll and for v9.0 you will need a reference to the RetrieveGlobals9.dll.  Reference can be added from the Visual Basic Editor, by selecting Tools >> References from the menus.

Example scripts follow:

Method 1: Microsoft Dynamics GP 10.0

Option Explicit

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

Private Sub Report_Start()
    ' 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 db
    cn.DefaultDatabase = UserInfoGet.IntercompanyID
End Sub

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    sqlstring = "SELECT {SQLFIELD} FROM {SQLTABLE} WHERE {KEYFIELD} = '" & RTrim({ReportKeyField}) & "'"
   
    ' ADO Command
    cmd.ActiveConnection = cn
    ' adCmdText
    cmd.CommandType = 1
    ' Command
    cmd.CommandText = sqlstring
       
   ' Pass through SQL
    Set rst = cmd.Execute
    If Not (rst.EOF And rst.BOF) Then
        {ReportCalcField}.Value = RTrim(rst!{SQLFIELD})
    End If
    rst.Close
End Sub

Private Sub Report_End()
    ' Close 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 Sub

Method 2: Microsoft Dynamics GP 9.0

Use the RetrieveGlobals9.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 one of the following Microsoft Web sites, depending on whether you are a customer or a partner:

Customer: Modifier/VBA Samples for Microsoft Dynamics GP 9.0 

Partner: Modifier/VBA Samples for Microsoft Dynamics GP 9.0 

Option Explicit

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

Private Sub Report_Start()
    Dim userinfo As New RetrieveGlobals9.retrieveuserinfo
    Dim luserid As String
    Dim lintercompanyid As String
    Dim lsqldatasourcename As String
    Dim ldate As Date

    ' RetrieveGlobals
    lsqldatasourcename = userinfo.sql_datasourcename()
    luserid = userinfo.retrieve_user()
    lintercompanyid = userinfo.intercompany_id()
    ldate = CStr(userinfo.user_date())

    ' ADO Connection
    Set cn = userinfo.Connection
    '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 db
    cn.DefaultDatabase = lintercompanyid
End Sub

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    sqlstring = "SELECT {SQLFIELD} FROM {SQLTABLE} WHERE {KEYFIELD} = '" & RTrim({ReportKeyField}) & "'"
   
    ' ADO Command
    cmd.ActiveConnection = cn
    ' adCmdText
    cmd.CommandType = 1
    ' Command
    cmd.CommandText = sqlstring
       
   ' Pass through SQL
    Set rst = cmd.Execute
    If Not (rst.EOF And rst.BOF) Then
        {ReportCalcField}.Value = RTrim(rst!{SQLFIELD})
    End If
    rst.Close
End Sub

Private Sub Report_End()
    ' Close 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 Sub

Method 3: 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 RetrieveGlobals.dll file together with its documentation, visit one of the following Microsoft Web sites, depending on whether you are a customer or a partner.

Customer: Modifier/VBA Samples for Great Plains 8.0 

Partner: Modifier/VBA Samples for Great Plains 8.0 

Option Explicit

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

Private Sub Report_Start()
    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
    lsqldatasourcename = userinfo.sql_datasourcename()
    luserid = userinfo.retrieve_user()
    lsqlpassword = userinfo.sql_password()
    lintercompanyid = userinfo.intercompany_id()
    'MsgBox (luserid & " " & lsqlpassword & " " & lintercompanyid & " " & lsqldatasourcename)

    ' Create Connection String
    constring = "Provider=MSDASQL" & _
                ";Data Source=" & lsqldatasourcename & _
                ";User ID=" & luserid & _
                ";Password=" & lsqlpassword & _
                ";Initial Catalog=" & lintercompanyid
    'MsgBox constring

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

Private Sub Report_BeforeBody(SuppressBand As Boolean)
    sqlstring = "SELECT {SQLFIELD} FROM {SQLTABLE} WHERE {KEYFIELD} = '" & RTrim({ReportKeyField}) & "'"
   
    ' ADO Command
    cmd.ActiveConnection = cn
    ' adCmdText
    cmd.CommandType = 1
    ' Command
    cmd.CommandText = sqlstring
       
   ' Pass through SQL
    Set rst = cmd.Execute
    If Not (rst.EOF And rst.BOF) Then
        {ReportCalcField}.Value = RTrim(rst!{SQLFIELD})
    End If
    rst.Close
End Sub

Private Sub Report_End()
    ' Close 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 Sub

For more information on how to use ADO on reports and in a window, these Knowledge Base (KB) articles are a good reference:

How to use an ActiveX Data Object (ADO) with VBA on a report in Microsoft Dynamics GP (KB 954619) 

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 (KB 942327)

You can also look at the example code in the postsbelow:

Modifier - Adding a field to a scrolling window using ADO Example

RW - Accessing any SQL data from a Report Example 

Hope you find this information useful. 

David

23-Feb-2009: Add Link to KB 954619.

08-Aug-2010: Add note about creating References.