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. Example scripts follow:
Method 1: Microsoft Dynamics GP 10.0
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
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
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.
