Automatically Running Reports in Five Different Databases

[wellinth]

Hi All,

I have five reports and each one is in a different data base. I would like to create a sixth data base with a menu that can run each report at a touch of a button. Can this be done? If so, does anyone know how to do it?

Thanks,

- Tom Wellington
[E-mail removed by admin]

 

[Andrew Fergus]

Hi Tom

You may want to remove your e-mail address from your post to avoid getting hit with spam.

Yes this can be done. Create a new database, create a form with a button (MyButton) and then attach a variation of the following VBA code to that button:

Private Sub MyButton_Click()

Dim db1 As Access.Application
Dim str1 As String

str1 = "C:\MyDatabase.mdb"
Set db1 = CreateObject("Access.Application")
With db1
    .OpenCurrentDatabase str1
    .DoCmd.OpenReport "MyReport", acViewNormal
    .CloseCurrentDatabase
End With
Set db1 = Nothing
'repeat the above code for the other databases
'Use your actual database name in place of MyDatabase
'Use your actual report name in place of MyReport

MsgBox "Finished"
End Sub

HTH, Andrew

 

[wellinth]

Thanks a lot, Andrew! That worked! Can I preview the reports, rather than print them? I changed .DoCmd.OpenReport "MyReport", acViewNormal
to .DoCmd.OpenReport "MyReport", acViewPreview in your code, but, when I ran the modified code, nothing happened. Any other ideas?

Thanks again,

- Tom

 

[mew0717]

I get a Run-Time error when i try this

The Database has been placed in a state by User 'Admin' on Machine 'Machine Name' that prevents it from being opened or unlocked. What the heck is this, and how -- more importantly -- do i fix it. I created the databases and they are local on my machine. not sure why its not working.


Help would be appreciated.

 

[Andrew Fergus]

Hi Tom

The code I provided opens the other database, prints the report then closes the database. I suspect that if you try to preview the report, then the report will be opened and previewed and then the database will close, also closing that report such that you never get to see it.

If you change the code to not close the database then I suspect you will have to tab to each database to see the report - you won't see all of the reports under the one database. If this is what you want to do, you could link the necessary tables from the other databases, re-create the queries/reports in your current database and then view all of the reports in your database. Would that do instead?

Andrew

 

[Andrew Fergus]

Re: I get a Run-Time error when i try this

Hi

You may want to start your own thread for this question - it does't appear to be related to the original question.

Andrew

The Database has been placed in a state by User 'Admin' on Machine 'Machine Name' that prevents it from being opened or unlocked. What the heck is this, and how -- more importantly -- do i fix it. I created the databases and they are local on my machine. not sure why its not working.


Help would be appreciated.