Open Access Report in Excel (2002 - SP2)

[jstegman]

Is there a way to open an Access database form in Excel and pass a value to it so that a text box is populated with that value (similar to form openArgs argument in the form Open process)?

[HansV]

The subject of your post mentions "Access Report", but the body is about an "Access database form". Which one do you mean?

[jstegman]

Hans,

The database report I'm trying to run is in Access and is presently produced by entering the account number you wish to report in a text box and the from triggers the report. I'm trying to bypass that step by having Excel pass the value to the form.

So, what I really am trying to do is have Excel start up Access and pass a value to a form. Once I get the data to the form, I'll have the from trigger the report.

John

[HansV]

You can use Automation to start Access and open the report. Here is code to open a database named Test.mdb, open a report rptCustomers in preview mode, and set a where-condition using a cell value:

Sub Test()
Dim objAcc As Object

On Error GoTo ErrHandler

Set objAcc = CreateObject("Access.Application")
With objAcc
.OpenCurrentDatabase "Test.mdb"
.DoCmd.OpenReport "rptCustomers", 2, , "strCountry=" & _
Chr(34) & ActiveSheet.Range("A1") & Chr(34)
.DoCmd.Maximize
.Visible = True
End With

ExitHandler:
Set objAcc = Nothing
Exit Sub

ErrHandler:
If Not Err = 2501 Then
MsgBox Err.Description, vbExclamation
End If
Resume ExitHandler
End Sub

[jstegman]

Han,

Thanks for that code. When I try to run it in Excel, the program seems to pause and then I get a message box with the message "Microsoft Excel is waiting for another application to complete an OLE Action."

Clicking OK causes Excel to repeat this message again.

I Appreciate the help.

[HansV]

Perhaps the record source of the report has a parameter?

[LegareColeman]

I think that is what he ment when he said he wanted to open an Access form and fill in a text box. Filling in the text box was to pass the parameter required by the report.

[HansV]

Yes, but there are various ways to do that in Access - using the text box as a parameter in a query, or passing a where-condition to the report.

[LegareColeman]

The way I read the original message is that he has an Access data base that has a form with a text box. You enter a parameter in the text box, and probably push a button to get the report based on that parameter that he wants to pop up from VBA in Excel.

[jstegman]

Hans,

I am stepping through the code line by line and the error "Microsoft Excel is waiting for another application to complete an OLE Action" appears while Excel is trying to execute the line about opening the database. The database never opens.

[HansV]

So the error occurs on the line .OpenCurrentDatabase "Test.mdb" (with the appropriate path/filename substituted)?

Is your database password protected?
Does your database have something in its startup options (Tools | Startup... in Access), or an AutoExec macro?

[jstegman]

Hans,

Yes, the error occurs on the line .OpenCurrentDatabase "Test.mdb" .

You were correct in assuming password and security parameters were hindering the opening of the db.

I created a Test..mdb in the generic workgroup and it opened and worked fine.

So now the question is how do I pass user and password information to the db while I'm in the secure workgroup? I didn't see anything about that in the docs for the CreateObject command.

Thanks for the help.

[HansV]

MSKB article ACC2000: Using Microsoft Access as an Automation Server (valid for Access 2002 too) explains how to use Shell in combination with GetObject instead of CreateObject to open a secured database. See the section Using a Secured Workgroup (it's quite a way down from the top.)

[jstegman]

Hans,

Read the MSKB article: Using the code below the db opens fine but now I can't figure out how to pass parameters to it - my original need.
I'll send two examples of code I've tried - the first from the article and the second similar to your first example.

Public Sub Test(Optional varUser As Variant, Optional varPW As Variant)
Dim cmd As String
On Error Resume Next
Set objAccess = GetObject(, "Access.Application")

If Err <> 0 Then
If IsMissing(varUser) Then varUser = "TestAdmin"
cmd = "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" & " C:Test.mdb"
cmd = cmd & " /nostartup /user " & varUser
If IsMissing(varPW) Then
cmd = cmd & " /pwd " & "TestPW"
End If
Shell pathname:=cmd, windowstyle:=3

Do
Err = 0
Set objAccess = GetObject(, "Access.Application")
Loop While Err <> 0
End If

End Sub

Using this code - similar to the one you gave me previously, I'm able to run a report but I am required to enter the password.
Dim objAccess As Object
On Error GoTo Test2_ErrorHandler
Err = 0
Set objAccess = CreateObject("Access.Application")
With objAccess
.opencurrentdatabase filepath:="C:Test.mdb"
.docmd.openreport reportname:="Test"
End With
Set objAccess = Nothing
Exit Sub
Test2_ErrorHandler:
Stop

============

Thanks for the help,

[jstegman]

Hans,

I further modified the code to have it run a macro on opening the db.

This allows the report to run while bypassing the password login dialog. I'm almost there!

Public Sub Test(Optional varUser As Variant, Optional varPW As Variant)
Dim cmd As String, intCycle As Integer
On Error Resume Next
Set objAccess = GetObject(, "Access.Application")
Err = 1
If Err <> 0 Then
If IsMissing(varUser) Then varUser = "TestAdmin"
cmd = "C:Program FilesMicrosoft OfficeOffice10MSACCESS.EXE" & " C:Test.mdb"
cmd = cmd & " /nostartup /user " & varUser
If IsMissing(varPW) Then
cmd = cmd & " /pwd " & "TestPW"
End If
cmd = cmd & " /x PrintAccount" ' Passing Macro Name
Shell pathname:=cmd, windowstyle:=3
Do
Err = 0
Set objAccess = GetObject(, "Access.Application")
Loop While Err <> 0
End If

End Sub

Now, I only need to know how to pass a "where" value with the Shell command.