Passing values to MS Access Reports via COM interop and vb.net

[Andrew Carlson]

I need to create a .net application that will allow the user to point to an
access .mdb file, consume a list of reports within that file, allow the user
to select one or more of those reports, pass the user's name, the user's
selected date, and perhaps other values to the reports, and print the number
of reports requested.

Thus far, I have found excellent samples and documentation on how to hook
into the mdb file however it is limited to a simple "Print the report that is
named at design time". I have had extreme difficulty in finding either books
or resources that give me more detail on passing variables to a report.

Two problems I have encountered thus far is with consuming a list of reports
in the reports collection of the file. Running this query "SELECT
MSysObjects.Name FROM MSysObjects " returns an error 'No Read permission on
MSysObjects'. Selecting the database in access and specifically setting read
permissions on all system tables does not fix the issue and no workgroup
information file has ever been used on this network.

The other problem I have had is not being able to get the list of report
names from a collection through ADO commands or DAO commands.

I am using .net framework 1.1, Visual Studio .NET 2003 Enterprise, Access
2003, and I am referencing Microsoft.Office.Core, Access, ADODB, DAO in COM.
I have imported Microsoft.Office.Core to the form class.

I could really use some direction to some documentation or books that will
help me overcome these challenges. Also, if I have left something out please
let me know. I have done a lot of .net programming, however, my Office
Automation and VBA are pretty limited. Mostly, limited to Excel Automation.

Thanks you for considering this request.

 

[Cindy M -WordMVP-]

Hi Andrew,

Two problems I have encountered thus far is with consuming a list of reports
in the reports collection of the file. Running this query "SELECT
MSysObjects.Name FROM MSysObjects " returns an error 'No Read permission on
MSysObjects'. Selecting the database in access and specifically setting read
permissions on all system tables does not fix the issue and no workgroup
information file has ever been used on this network.

The other problem I have had is not being able to get the list of report
names from a collection through ADO commands or DAO commands.

DAO/ADO supply only the data, not the Access structures available in a
database. As far as I know, the only way to get a list of reports is to
automate the Access database (open the *.mdb file) then (extract from Help):

"Note To list all reports in the database, whether open or closed, enumerate
the AllReports collection of the CurrentProject object. You can then use the
Name property of each individual AccessObject object to return the name of a
report."

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Andrew Carlson]

Well I figured out how to get a Reports collection by the following code

oAccess = New Access.ApplicationClass

' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
Dim obj1 As AccessObject, app1 As Object

'Create a reference to the current project instance
app1 = oAccess.CurrentProject

'List each report in the application
For Each obj1 In app1.AllReports
If obj1.IsLoaded = True Then
ReportsList.Items.Add(obj1.Name)
Else
ReportsList.Items.Add(obj1.Name)
End If
Next obj1
NextButton.Enabled = True
oAccess.CloseCurrentDatabase()
oAccess.Quit()

The problem I now have however, is setting the text property of a text box
or label control on the report. It seems to be an ambiguous namespace error.

I would like to do the following:

Dim ctl as Control
For Each ctl In Reports(reportName).Controls
If ctl.Name = "myControl" then
ctl.Value = myUserDefinedValue
End If
Next ctl

If I do this, I do not have the control properties of .ControlType or .Name
exposed through Intellisense. This makes me think that this is a problem with
the namespace. Any ideas?

Thanks, Andrew

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?QW5kcmV3IENhcmxzb24=?=,

You really need to go to an Access group with this. I expect you have to
activate a Design mode, but that's not something I've ever tried to automate
(that I can recall). My main area of expertise is actually Word, not Access

Well I figured out how to get a Reports collection by the following code

oAccess = New Access.ApplicationClass

' Open Northwind.mdb in shared mode:
oAccess.OpenCurrentDatabase(filepath:=sDBPath, Exclusive:=False)
Dim obj1 As AccessObject, app1 As Object

'Create a reference to the current project instance
app1 = oAccess.CurrentProject

'List each report in the application
For Each obj1 In app1.AllReports
If obj1.IsLoaded = True Then
ReportsList.Items.Add(obj1.Name)
Else
ReportsList.Items.Add(obj1.Name)
End If
Next obj1
NextButton.Enabled = True
oAccess.CloseCurrentDatabase()
oAccess.Quit()

The problem I now have however, is setting the text property of a text box
or label control on the report. It seems to be an ambiguous namespace error.

I would like to do the following:

Dim ctl as Control
For Each ctl In Reports(reportName).Controls
If ctl.Name = "myControl" then
ctl.Value = myUserDefinedValue
End If
Next ctl

If I do this, I do not have the control properties of .ControlType or .Name
exposed through Intellisense. This makes me think that this is a problem with
the namespace. Any ideas?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail