Using ADO with VBA with Report Writer


David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

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.

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.

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) (Not Available)

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.

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

21 thoughts on “Using ADO with VBA with Report Writer

  1. I am getting a Runtime Error 446 – Object does not support names arguments.
    The debugger lands on "cn.CursorLocation = 3" ??
    Am I missing something?

    Like

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.