Automatically Running Reports in Five Different Databases

wellinth

Board Regular
Joined
Aug 6, 2004
Messages
58
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]
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
Aug 6, 2004
Messages
58
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

New Member
Joined
Feb 17, 2006
Messages
18
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,452
Office Version
  1. 365
  2. 2021
  3. 2016
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,181,657
Messages
5,931,265
Members
436,785
Latest member
KingGideon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top