Send a report from one access database via another access database

[Jollywg]

I have a client that has all their reports, data, and code in Access 2000...They also have a copy of Access 2007 that they want to copy or somehow trigger a report in access 2000 and email it to 150 employees since emailing in access 2007 is much easier and more robust than it was in access 2000.

Just in case this gets confusing, they are wanting to press a button in access 2007, which emails ReportA from access 2000. Let me know if I need to clarify anything, and thanks in advance!

Jollywg

[ADezii]

Here is how you can E-Mail a Report from an External Database (Access 2000) from the Current Database (Access 2007):

1. Declare a Modular Level Variable to hold an Access Application Object:
   

   Dim appAccess As Access.Application

2. Execute the Code that will actually E-Mail the Report in the External DB:
   

   Dim strDB As String
    
   'Initialize Constant to External Database Path
   Const conDB_PATH As String = "C:\Test\"
    
   'Initialize Variable to Absolute PATH to External Database
   strDB = conDB_PATH & "Phone Numbers.mdb"
    
   'Create New instance of Microsoft Access.
   Set appAccess = CreateObject("Access.Application")
    
   'Open External Database in Microsoft Access Window (will not be Visible by Default)
   appAccess.OpenCurrentDatabase strDB
    
   'E-Mail the Report named rptTest in the External Database to [e-mail], in Excel (*.xls) Format
   'Subject = 'Report Name', Message Text = 'Here is the Requested Report'. The E-Mail will be
   'sent directly bypassing the Edit Window (last False Argument)
   appAccess.DoCmd.SendObject acSendReport, "rptTest", acFormatXLS, "[e-mail]", , , "Report Name", _
                              "Here is the Requested Report", False

3. At some point, Close the External (not Current) Database, while leaving the Current Database in tact:
   

   appAccess.CloseCurrentDatabase

P.S. - This has only been tested between Access 2003 Databases, and is fully operational as such. I made the assumption that the Logic will also work between Access 2000 <==> 2007, but only you can let us know for sure.

[Jollywg]

ADezii, thank you for the quick reply! I do have one concern, will this cause a pop up box to disply when the message sends stating "A program is trying to send mail on your behalf..."?

Thanks
Jolly

[ADezii]

@Jollywg:

I do have one concern, will this cause a pop up box to disply when the message sends stating "A program is trying to send mail on your behalf..."?

If Microsoft Outlook is your Default E-Mail Client, it probably will. Is this your case? There is a work-a-around if this is your situation.

[Jollywg]

Adezii, I am running into a small hold up. The database my client has uses Workgroup Authentication. I have the username and password for an administrator, I just need to specify that when it does the appAccess.OpenCurrentDatabase strDB

[ADezii]

You may have hit a brick wall on this one, since the OpenCurrentDatabase() Method has no built-in mechanism to allow for Workgroup Authentication. Off the top of my head, I know of no other approach that would accomplish exactly what you are requesting, but I will put my Thinking Cap back on! (LOL).

[Jollywg]

Hmmmm....lets change gears a little bit. If I manually export the report as a .scp or .pdf can I email it from access 2007/10?