Access Automation

[mouse88]

Here is my code which is supposed to open an access report for previewing rather than printing. For some reason when the code runs it kinda looks like it doing something for a few seconds butr nothing is displayed.

Can anyone advise why this is. This is the first time ive tried any automation of application so im not sure whats going wrong.

  
            Dim FileName As String = LoginForm.Filename
            Dim oAccess As Access.Application

            ' Start a new instance of Access for Automation:
            oAccess = New Access.ApplicationClass()

            Dim sDBPassword As String
            Dim oDBEngine As DAO.DBEngine
            Dim oDB As DAO.Database

            sDBPassword = "A5B7C5D8E4" 'database password 
            oDBEngine = oAccess.DBEngine
            oDB = oDBEngine.OpenDatabase(Name:=FileName, _
               Options:=False, _
               ReadOnly:=False, _
               Connect:=";PWD=" & sDBPassword)
            oAccess.OpenCurrentDatabase(filepath:=FileName, _
               Exclusive:=False)

            Dim ReportName As String = "Autoglass Totals Report"
            oAccess.DoCmd.OpenReport(ReportName:=ReportName, View:=Access.AcView.acViewPreview)

            oDB.Close()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oDB)
            oDB = Nothing
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oDBEngine)
            oDBEngine = Nothing

[brucevde]

When automating any Office application you need to set the Application.Visible property to True if you want to see anything. When using Automation you need to ensure there is a way to respond (or ignore) any dialogs/messageboxes that the Application might display.

The code that opens the database using DAO doesn't seem to belong there, unless you haven't posted all your code. Typically you use one method or the other (Automation or DAO).

This is VB6 code but its pretty much the same for .NET.

    
    Dim FileName As String
    Dim ReportName As String
            
    Dim oAccess As Access.Application
            
    ReportName = "Sales Totals by Amount"
    FileName = "c:\testing\nwind2000.mdb"
    
    Set oAccess = New Access.Application
    oAccess.Visible = True
    
    Call oAccess.OpenCurrentDatabase(filepath:=FileName, Exclusive:=False, bstrPassword:="dude")

    Call oAccess.DoCmd.OpenReport(ReportName:=ReportName, View:=Access.AcView.acViewPreview)

    Set oAccess = Nothing

[mouse88]

Ok cool problem solved. Is there a way of opening the database as read only so the user cannot modify anything in the database?

[mouse88]

Can anyone help with this? Have tried googling it but havent had any luck.

[RobDog888]

You could add some security like run a macro that removes toolbars and menus but they can easily bypass that if they hold the Shift key down when opening the db. Better would be to use Access Workgroup Security. See Access Security in my Office FAQ for more on it.

[mouse88]

Ok thanks for the advice

[felipebj182]

Hi there, I have one question... I also want to automate an access that generates an .xls report. I have an file .mdb that is used to generate this report. I REALLY can't change this file. I'm using an vbs script that manipulates the access in order to generate my reports. Here's my vbs code:

'Criação de objeto Access.
Set oAccess = CreateObject("ACCESS.Application")

'Seta a visibilidade do programa em execução.
oAccess.Visible = True

'Seta o aviso de segurança para o nível mais baixo.
oAccess.automationsecurity=1

'Abre o arquivo de batimento (.mdb)
oAccess.OpenCurrentDatabase("D:\Intelig\Sistemas\Interconnect\Relatorios\Interface_Batimento_v11.mdb")

'Abre o Formulário
oAccess.DoCmd.OpenForm "FrmRelat", , , stLinkCriteria

Dim a 

a = "C:\BATIMENTO\REL_BAT_125_DIV_DET_20090520_111111.xls"

Call sCriaExcell(125, a , 1)

My problem Is: I need to call the function sCriaExcell (This function is on my mdb file)

here's the code for sCriaExcell:

Public Function sCriaExcell(pBat_Code As Integer, _
                       pArqSaida As String, _
                       pTipoRelat As Integer)
    On Error GoTo Error_Handle
    Dim iCount As Integer
    Dim i As Integer

    If StrComp(Dir(pArqSaida), "") <> 0 Then
       Kill pArqSaida
    End If

    DoCmd.Hourglass True

    ' Create the Excel App Object
    Set xlApp = CreateObject("Excel.Application")
    ' Create the Excel Workbook Object.
    Set xlBook = xlApp.Workbooks.Add
        
    ' Excluir as Sheets
    iCount = xlApp.Worksheets.Count
    
    wSheet = 1
        
    xlApp.Worksheets(wSheet).Activate
    xlApp.Worksheets(wSheet).Name = "Relatório"
    xlApp.Worksheets(wSheet).Visible = True
    xlApp.ActiveWindow.DisplayGridlines = False
    
    Call sGerar_Rel_Analise(pBat_Code, pTipoRelat)

    xlApp.Worksheets(wSheet).Cells.Select
    xlApp.Worksheets(wSheet).Cells.EntireColumn.AutoFit
    
    xlBook.SaveAs pArqSaida
    xlBook.Close savechanges:=True
    xlApp.Quit
    MsgBox "Arquivo " & pArqSaida & " foi Gerado"

Exit_Proc:
    On Error Resume Next
    
    DoCmd.Hourglass False
    Set xlBook = Nothing
    Set xlApp = Nothing
    
    On Error GoTo 0
    Err.Number = 0
    Exit Function

Error_Handle:
    MsgBox "Cod.: " & Err.Number & vbCr & _
    "Descr.: " & Err.DESCRIPTION & vbCr & _
    "Contexto.:" & Err.HelpContext

    Resume Exit_Proc
End Function

so, how can I call sCriaExcell from my vbs script? Everytime I try I get "mismatch exception" or "can't use () when calling a sub" things like that..

thanks for the help!