One of the most powerful features of the VSReport8 Designer is the ability to import reports created with Microsoft Access. This feature requires Access to be installed on the computer. Once the report is imported into the Designer, Access is no longer required.
To import reports from an Access file, click the Import button or in the File menu, select Import. A dialog box will prompt you for the name of an Access file (MDB). After you select the file, the Designer automatically scans it and converts all the reports into a new report definition file.
The import process works well and handles most elements of the source reports, with a few exceptions listed below:
-
Event handler code
Access reports can use VBA, macros and forms to format the report dynamically. VSReport8 can do the same things, but it only uses VBScript. Because of this, all report code needs to be translated manually.
-
Form-oriented field types
Access reports may include certain fields that are not handled by the Designer's import procedure. The field types not supported are Chart, CommandButton, ToggleButton, OptionButton, OptionGroup, ComboBox, ListBox, TabCtl, and CustomControl.
-
Reports that use VBScript reserved words
Because Access does not use VBScript, you may have designed reports that use VBScript reserved words as identifiers for report objects or recordset field names. This causes problems when the VBScript engine tries to evaluate the expression, and will prevent the report from rendering correctly.
Reserved words you should not use as identifiers include Date, Day, Hour, Length, Minute, Month, Second, Time, TimeValue, Value, Weekday, and Year. For a complete list, please refer to a VBScript reference.
-
Reports that sort dates by quarter (or weekday, month of the year, etc.)
VSReport8 uses the ADO recordset Sort property to sort groups. This property sorts recordsets according to field values only, and does not take expressions. (Note that you can group according to an arbitrary expression, but you cannot sort.) An Access report that sorts groups by quarter will sort them by date after it is imported. To fix this, you have two options. Either create a field that contains the value for the expression you want to sort on, or change the SQL statement that creates the recordset and perform the sorting that way.
These limitations affect a relatively small number of reports, but you should preview all reports after importing them, to make sure they still work correctly.
To illustrate how the Designer fares in a real-life example, try importing the NWind.mdb file. It contains 13 reports, listed below with some comments. (The NWind.XML file that ships with VSReport8 already contains all the modifications described below.)
-
Alphabetical List of Products
No action required.
-
Catalog
No action required.
-
Customer Labels
No action required.
-
Employee Sales by Country
This report contains code that needs to be translated manually. The following code should be assigned to the Group 1 Header Section's OnPrint event:
Example Title Copy CodeIf SalespersonTotal > 5000 Then ExceededGoalLabel.Visible = True SalespersonLine.Visible = True Else ExceededGoalLabel.Visible = False SalespersonLine.Visible = False End If
-
Invoice
No action required.
-
Products by Category
No action required.
-
Sales by Category
This report contains a Chart control that is not imported. To add a chart to your report, you could use an unbound picture field, then write a VB event handler that would create the chart and assign it to the field as a picture.
-
Sales by Category Subreport
No action required.
-
Sales by Year
This report contains code and references to a Form object that need to be translated manually. To replace the Form object, edit the RecordSource property to add a [Show Details] parameter:
Example Title Copy CodePARAMETERS [Beginning Date] DateTime 1/1/1994, [Ending Date] DateTime 1/1/2001, [Show Details] Boolean False; ...
Use the new parameter in the report's OnOpen event:
Example Title Copy CodeReport.OnOpen = "b = [Show Details]" & _ " Detail.Visible = b" & _ " [Group 0 Footer].Visible = b" & _ " DetailsLabel.Visible = b" & _ " LineNumberLabel2.Visible = b" & _ " Line15.Visible = b" & _ " SalesLabel2.Visible = b" & _ " OrdersShippedLabel2.Visible = b" & _ " ShippedDateLabel2.Visible = b" & _ " Line10.Visible = b"
Finally, two more lines of code need to be translated:
Example Title Copy CodeSections("Detail").OnPrint = _ "PageHeader.Visible = True" Sections("Group 0 Footer).OnPrint = _ "PageHeader.Visible = False"
-
Sales by Year Subreport
No action required.
-
Sales Totals by Amount
This report contains code that needs to be translated manually. The following code should be assigned to the Page Header Section's OnPrint event:
Example Title Copy CodePageTotal = 0
The following code should be assigned to the Detail Section's OnPrint event:
Example Title Copy CodePageTotal = PageTotal + SaleAmount HiddenPageBreak.Visible = (Counter = 10)
-
Summary of Sales by Quarter
This report has a group that is sorted by quarter (see item 4 above). To fix this, add a field to the source recordset that contains the value of the ShippedDate quarter, by changing the RecordSource property as follows:
Example Title Copy CodeSELECT DISTINCTROW Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal, DatePart("q",Orders.ShippedDate) As ShippedQuarter FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID WHERE (((Orders.ShippedDate) Is Not Null));Change the group's GroupBy property to use the new field, ShippedQuarter.
-
Summary of Sales by Year
No action required.
Summarizing the information on the table: out of 13 reports imported from the NorthWind database, eight did not require any editing, three required some code translation, one required changes to the SQL statement and one had a chart control that was not replaced.