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:
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.ConnectionDim rst As New ADODB.RecordsetDim cmd As New ADODB.CommandDim 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.IntercompanyIDEnd 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.CloseEnd 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 = NothingEnd 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
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 = lintercompanyidEnd 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
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 WithEnd 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.
PingBack from http://blogs.msdn.com/developingfordynamicsgp/archive/2008/10/30/using-vba-with-report-writer.aspx
Developing for Dynamics GP is full of new stuff today including Using VBA with Report Writer , Using
Posting from the Dynamics GP Blogster
http://dynamicsgpblogster.blogspot.com/2008/10/developing-for-dynamics-gp-weekly_30.html
The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y
The question in more detail is whether it is possible to print "Page: Y/X" or "Page: Y of X" on a Report
When the inventory module of Microsoft Dynamics GP was first written the Item Description field was 60
Posting from The Dynamics GP Blogster
http://dynamicsgpblogster.blogspot.com/2010/01/parsing-long-string-fields-in-extender.html
PLEASE READ BEFORE POSTING
Please only post comments relating to the topic of this page.
If you wish to ask a technical question, please use the links in the links section (scroll down, on right hand side) to ask on the Newsgroups or Forums. If you ask on the Newsgroups or Forums, others in the community can respond and the answers are available for everyone in the future.