Changing the Query for an MS Access Report Programatically

Bigbeanpole

Member
Joined
Jul 7, 2005
Messages
15
Programming Experience
1-3
Is this possible? I'm trying to use access reports in my vb.net application. I've already determined that to show the report, you essentially need to have an instance of MS Access running, and although I don't like it, I can live with it. But I need to be able to change what the report is showing. Is there a way to do that programatically?

I know about Automation as well, and am assuming that's what I need to be utilizing, however if somebody could provide me with a snippet, that would be ideal. :)

Thanks
 
Last edited:

jglobe

Member
Joined
Aug 27, 2005
Messages
12
Programming Experience
1-3
Here is some vb6 I use using Access Automation (It should be easy to convert)...


This will create a report and make a snapshot file which I then call a shell execute on...


On Error GoTo ErrHandler

'The following segment of code is using Microsoft Access directly
'and the application must be installed for the following reports to open
Dim pAccessApp As Object 'Access.Application
pAccessApp = CreateObject("Access.Application")

Dim i As Short
Select Case pAccessApp.SysCmd(acSysCmdAccessVer)
Case "8.0" 'Access 97
Case "9.0" 'Access 2000
Case "10.0" 'Access 2002
Case "11.0" 'Access 2003
pAccessApp.AutomationSecurity = 1 'Low Security
Case Else
End Select

'Don't let the user see Access
pAccessApp.Visible = False
pAccessApp.DoCmd.Minimize()

'Open the Access Database
pAccessApp.OpenCurrentDatabase(sDatabasePath & "\" & sDatabaseName, False)

pAccessApp.DoCmd.OpenReport("myreport", acPreview, , whereClause) ', acWindowNormal

' Save the file
Dim fullname() As String
Dim FileName As String
Dim FilePath As String

Dim FSO As New FileSystemObject


' Set CancelError is True
FileDLG.CancelError = True

' Set filters
FileDLG.DialogTitle = "Save Report Snapshot As"
FileDLG.Filter = "Snapshot Document (*.snp)|*.SNP|Snapshot Document"

' Specify default filter
FileDLG.FilterIndex = 1

'Set default directory to the default report path
If FSO.FolderExists(sReportPath) Then
FileDLG.InitDir = sReportPath
End If

' Display the Open dialog box
FileDLG.ShowOpen()

fullname = Split(FileDLG.FileName, "\", -1)
FileName = Trim(fullname(UBound(fullname)))
FilePath = Left(FileDLG.FileName, (Len(FileDLG.FileName) - 1) - Len(FileName))


pAccessApp.DoCmd.OutputTo(acOutputReport,"myreprot", acFormatSNP, FilePath & "\" & FileName)


pAccessApp.CloseCurrentDatabase()
pAccessApp = Nothing

Dim success As Integer
success = ShellExecute(Me.Handle.ToInt32, vbNullString, FileName, vbNullString, FilePath, 1)

If success = SE_ERR_NOASSOC Then
Call Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " & FilePath & "\" & FileName, AppWinStyle.NormalFocus)
End If


Exit Function

ErrHandler:
'Do Nothing User Canceled
pAccessApp.CloseCurrentDatabase()
pAccessApp = Nothing


************************************
This loops through existing reports

Dim pAccessApp As Object
Dim i As Short

'Load Tab Controls With Access Reports
On Error Resume Next

pAccessApp = CreateObject("Access.Application")
If pAccessApp Is Nothing Then
MsgBox("Please Ensure Microsoft Access Is Installed. Access Reporting Will Be Disabled", MsgBoxStyle.Information + MsgBoxStyle.OKOnly, "Microsoft Access Could Not Be Referenced")
Else

Select Case pAccessApp.SysCmd(acSysCmdAccessVer)
Case "8.0" 'Access 97
MsgBox("Microsoft Access 2000 Or Later Are Supported", MsgBoxStyle.Information, "Microsoft Access 97 Is Not Supported")
GoTo NoAccess
Case "9.0" 'Access 2000
Case "10.0" 'Access 2002
Case "11.0" 'Access 2003
pAccessApp.AutomationSecurity = 1 'Low Security
Case Else
MsgBox("Unexpected Results May Occur", MsgBoxStyle.Information, "This Version Of Microsoft Access Is Not Supported")
End Select

pAccessApp.OpenCurrentDatabase(sDatabasePath & "\" & sDatabaseName, False)


For i = 0 To pAccessApp.CurrentProject.AllReports.Count - 1
If LCase(pAccessApp.CurrentProject.AllReports(i).Name) = "myreport" Then
Else
'Unsupported Report
End If
Next i

pAccessApp.CloseCurrentDatabase()
pAccessApp = Nothing
End If



jglobe
 
Top Bottom