Reports in listbox to reside in external db

[tuxalot]

I have reports visible in a listbox on a tabbed form. The Row Source for the listbox is:

SELECT [TblReports].ReportID, [TblReports].ReportName, [TblReports].ReportCaption, [TblReports].ReportCriteriaFlags FROM [TblReports] ORDER BY [TblReports].ReportCaption;

QUESTION:

Some of the reports currently presented in the listbox are State-specific (i.e. California and Arizona have different forms for example). I would like to have these State-specific reports exist in a separate external database, and deploy the "State" database only to businesses in the appropriate State. The listbox then, in the main database would need to include reports from (2) locations, one, the main database, and two, the State-specific reports which reside in the external (but local) database.

I really appreciate the help you have all provided thus far.

[tuxalot]

Further explanation on why (I think) I need to do this: I foresee updating the reports as an issue I wish to avoid if possible. Each U.S. State updates their forms (which are the basis of my reports) annually. Using an external db to house the reports would allow me to simply email the updated reports to the 150+ locations, and eliminate any data migration that would be necessary if I updated the State-specific reports housed in their main db.

Does this make any sense? Being very new to Access, maybe I'm missing something. Ok, so I exported TblReports to an external db. From the main db, I setup a link to the exported table. I kept the same name "TblReports" (don't know if this will cause issues). Again here is the row source for the listbox:

SELECT [TblReports].ReportID, [TblReports].ReportName, [TblReports].ReportCaption, [TblReports].ReportCriteriaFlags FROM [TblReports] ORDER BY [TblReports].ReportCaption;

Can I just reference the linked TblReports by inserting in the code above?

I found a bit of code that might work (if I had a clue how to use it)

Access Example 1
 
 
    Private Sub UserForm_Initialize()
Dim myDataBase As Database
Dim myActiveRecord As Recordset
Dim i As Long
'Open the database to retrieve data
Set myDataBase = OpenDatabase("E:\Junk\sourceAccess.mdb")
'Define the first recordset
Set myActiveRecord = myDataBase.OpenRecordset("Table1", dbOpenForwardOnly)
'Set the listbox column count
ListBox1.ColumnCount = myActiveRecord.Fields.Count
i = 0
'Loop through all the records in the table until the EOF
'marker is reached. Use AddItem to add a new row for each record.
'Use List to populate the ListBox column and row field.
Do While Not myActiveRecord.EOF
    ListBox1.AddItem
    ListBox1.List(i, 0) = myActiveRecord.Fields("Employee Name")
    ListBox1.List(i, 1) = myActiveRecord.Fields("Employee DOB")
    ListBox1.List(i, 2) = myActiveRecord.Fields("Employee ID")
    i = i + 1
'Get the next record
    myActiveRecord.MoveNext
Loop
'Close the database and clean-up.
myActiveRecord.Close
myDataBase.Close
Set myActiveRecord = Nothing
Set myDataBase = Nothing
End Sub
 
Access Example 2
 
 
    Private Sub UserForm_Initialize()
'You need to set a reference in your project to the “Microsoft DAO 3.51 (or 3.6) Object Library”.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long
'Open the database to retrieve data
Set db = OpenDatabase("E:\Junk\sourceAccess.mdb")
'Define the first recordset
Set rs = db.OpenRecordset("SELECT * FROM Table1")
'Determine the number of records in the recordset
With rs
    .MoveLast
    NoOfRecords = .RecordCount
    .MoveFirst
End With
'Set the number of ListBox columns = number of fields in the recordset
ListBox1.ColumnCount = rs.Fields.Count
'Load the ListBox with the retrieved records
ListBox1.Column = rs.GetRows(NoOfRecords)
'Cleanup
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

These alternatives seem way too complex. I hope there is an easier way. Thoughts? Thanks in advance.

[ADezii]

I haven't actually tried this, but you can 'Link' to the Table in the External Database, then perform a Union Query to populate your List Box.

[tuxalot]

Thanks ADezii, that's exactly what I did and it works well. I've now created a form to allow a user to select the path to the linked mdb and it's working. However, I would like a listbox on the form that would show the full path to the current linked mdb. I've tried

SELECT MSysObjects.Name, MSysObjects.Database FROM MSysObjects WHERE (((Left([Name],1))<>"~") AND ((MSysObjects.Type) In (4,6))) ORDER BY MSysObjects.Name;

which gives me the linked table name, but what I would like to show is the full path to the mdb instead. Any ideas?

[tuxalot]

solved this bit. I created a function

Public Function GetLinkedDBName(TableName As String)
   Dim db As DAO.Database, Ret
   On Error GoTo DBNameErr
   Set db = CurrentDb()
   Ret = db.TableDefs(TableName).Connect
   GetLinkedDBName = Right(Ret, Len(Ret) - (InStr _
      (1, Ret, "DATABASE=") + 8))
   Exit Function
DBNameErr:
   GetLinkedDBName = "  Unable to find linked database.  Does it exist?"
 
End Function

And set this as the control source in the textbox where I wanted the path to show:

=GetLinkedDBName("TblReportsState")

where TblRecordState is the table in the linked db.

[tuxalot]

Now I'm stuck. Using the union query, the listbox properly shows reports from the local and external databases. The button on the reports tab has as it's code:

On Error GoTo Err_cmdGlobalReportOpen_Click
 
    Dim stDocName As String
 
    stDocName = [Forms]![FrmMain]![lstReportName].Column(1)
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdGlobalReportOpen_Click:
    Exit Sub
 
Err_cmdGlobalReportOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdGlobalReportOpen_Click

Of course, this code does not allow me to open remote reports. The external reports have as their record source a query which resides in my main db. So the task at hand is to modify the code above to retrieve local and external reports, and somehow have the local query pass data to the external reports.

Any help is very much appreciated as I've no idea where to begin with this one.

[ADezii]

@tuxalot
You can probably use Automation Code to accomplish what you have requested but it may be a little tricky. I've written some code for you that will:

1. Check for the existence of a Report in an External Database to see if it also exists Locally.
2. If it exists Locally, Delete it since it may have been modified within the External Database, and we need the most recent Version.
3. Import the Report from the External Database and Open (Print) it. This can be done because its Record Source exists Locally.
4. Delete it when finished, since there is no longer any need to store it.
5. This code if for a single Report only, but I'm sure you can easily adapt it for multiple Reports.
6. This is probably not the Optimal solution, so I would be patient and wait and see what some of the other Members have to say on this Topic.
   

   Dim rptCounter As Integer
   Const conPATH_TO_EXTERNAL_DB As String = "C:\Test\External.mdb"
   Const conREPORT_NAME As String = "rptCustomers"
    
   For rptCounter = 0 To CurrentDb.Containers("Reports").Documents.Count - 1
     If CurrentDb.Containers("Reports").Documents(rptCounter).Name = conREPORT_NAME Then
       'DELETE the Report if it currently exists in the DB, since it may have changed
       DoCmd.DeleteObject acReport, conREPORT_NAME
         Exit For
     End If
   Next
    
   'Import the Report (bring in a 'Fresh' Version
   DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_EXTERNAL_DB, acReport, _
                                     conREPORT_NAME, conREPORT_NAME, False
    
   'Print the External/now Internal Report
   DoCmd.OpenReport conREPORT_NAME, acViewNormal
    
   'All done, let's DELETE the Report once again
   DoCmd.DeleteObject acReport, conREPORT_NAME

P.S. - You would need some kind of mechanism to differentiate between 'External' and 'Local' Reports.

[tuxalot]

ADezii, thanks for the code. Some comments:

• The external reports will not exist locally.
• At most, there will only be 4-5 reports in the external db.
• The external db will not be accessed as it will simply provide a home for the reports.
• A button on the reports tab of the main form selects the report for printing based on the report previously selected in the listbox of the main form.
• The path to the external db is selected by the user.

If you could provide direction on how to modify the code you provided based on these comments that would be much appreciated.

Other options or possible alternatives:

• If this is too tricky to accomplish I could have all the reports stored in the external db. This would result in only about 15 reports total stored externally.
• Each external report only require as their record source data from a single record (one employee). These data are stored in (2) tables, TblEmployeeInjury and TblHotelInformation. Based on this, could I create links to these local tables from the external db, and use a filter on the external reports? I would still need a mechanism to open them from within the local db.

Just throwing that out there, perhaps this could be part of the solution.

Thanks all,

Tux

[tuxalot]

I added columns in my local and linked tables called RemoteReport with values 0=local and 1=remote. My button code now looks like this, to check to see if the report is local or remote:

Private Sub cmdGlobalReportOpen_Click()
 
'On Error GoTo Err_cmdGlobalReportOpen_Click
 
' determine if the report is local or external (i.e. for lstReportName column(4) remote = 1)
 
Dim stDocRemote As String
 
stDocRemote = [Forms]![FrmMain]![lstReportName].Column(4)
 
If stDocRemote = "1" Then
 
Call OpenExternalRpt
 
Else
 
    Dim stDocName As String
 
    stDocName = [Forms]![FrmMain]![lstReportName].Column(1)
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdGlobalReportOpen_Click:
    Exit Sub
 
Err_cmdGlobalReportOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdGlobalReportOpen_Click
 
End If
 
End Sub

Local reports print fine. External reports (stDocRemote = "1") still do not print. I modified the code ADezii provided earlier to this:

Public Function OpenExternalRpt()
 
'Dim rptCounter As Integer
'Dim PathToExternalDb As String
Dim conPATH_TO_EXTERNAL_DB As String
Dim conREPORT_NAME As String
 
conPATH_TO_EXTERNAL_DB = PathToExternalDb ' this is the field in TblPathToExternalDb which stores the path selected by the user
conREPORT_NAME = [Forms]![FrmMain]![lstReportName].Column(1) ' this is the name of the report
 
'Import the Report (bring in a 'Fresh' Version
'DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_EXTERNAL_DB, acReport, conREPORT_NAME, conPATH_TO_EXTERNAL_DB, False
DoCmd.TransferDatabase acImport, "Microsoft Access", conREPORT_NAME, acReport, conPATH_TO_EXTERNAL_DB, conREPORT_NAME, False
'
' 'Print the External/now Internal Report
DoCmd.OpenReport conREPORT_NAME, acPreview
'
' 'All done, let's DELETE the Report once again
DoCmd.DeleteObject acReport, conREPORT_NAME
 
End Function

I am getting a run time error 3024 at this line of code:

DoCmd.TransferDatabase acImport, "Microsoft Access", conREPORT_NAME, acReport, conPATH_TO_EXTERNAL_DB, conREPORT_NAME, False

stating "Could not find file c:\Documents and Settings\Mikey\My Documents\(report name in the remote db)".

So the path is incorrect to the remote db. The correct path to the external db as stored in my table is:

C:\Documents and Settings\mikey\Desktop\Hyatt\StateReports\StateRep orts.mdb

and is the same as the file shown in the Access link manager.

Any ideas?

[ADezii]

Your Arguments to the TransferDatabase Method are out of whack:

DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_EXTERNAL_DB, acReport, conREPORT_NAME, conREPORT_NAME, False

[tuxalot]

Hi ADezzi, thanks for the reply. Problem is, that TransferDatabase Method produces a run-time error 3001 "invalid argument". Any idea why?

I've zipped and attached my main database, as well as the one that houses the state reports. If you have a chance to have a look at it, just set the path to the external db from the utilities tab and you should see the external reports on the reports tab.

Thanks so much for your help.

[tuxalot]

Now it's working. I set a hidden subform to pull the path to the external db (I'm sure there is a cleaner way to do this) but I couldn't figure it out. So the final code is:

Public Function OpenExternalRpt()
 
Dim rptCounter As Integer
Dim conPATH_TO_EXTERNAL_DB As String
Dim conREPORT_NAME As String
Dim conEXTERNAL_DB_NAME As String
 
' get path to external db from hidden subform located on the utilities tab of FrmMain
conPATH_TO_EXTERNAL_DB = Forms!FrmMain!fsubPathToExternalDb.Controls!txtPathToExternalDb
 
conREPORT_NAME = [Forms]![FrmMain]![lstReportName].Column(1)
conEXTERNAL_DB_NAME = ExternalDbName
 
 
' Delete the local copy of the report if it exists
For rptCounter = 0 To CurrentDb.Containers("Reports").Documents.Count - 1
If CurrentDb.Containers("Reports").Documents(rptCounter).Name = conREPORT_NAME Then
DoCmd.DeleteObject acReport, conREPORT_NAME
Exit For
End If
Next
 
 
'Import the external report based on the report selected in the listbox of FrmMain
DoCmd.TransferDatabase acImport, "Microsoft Access", conPATH_TO_EXTERNAL_DB, acReport, conREPORT_NAME, conREPORT_NAME, False
 
' 'Print preview the external/now Internal Report
DoCmd.OpenReport conREPORT_NAME, acPreview
 
End Function

I chose to leave the local copy of the report as is until it is opened again, after which the local copy is deleted.

The button code is:

Dim stDocRemote As String
 
stDocRemote = [Forms]![FrmMain]![lstReportName].Column(4)
 
If stDocRemote = "1" Then
 
Call OpenExternalRpt
 
Else
 
    Dim stDocName As String
 
    stDocName = [Forms]![FrmMain]![lstReportName].Column(1)
    DoCmd.OpenReport stDocName, acPreview
 
Exit_cmdGlobalReportOpen_Click:
    Exit Sub
 
Err_cmdGlobalReportOpen_Click:
    MsgBox Err.Description
    Resume Exit_cmdGlobalReportOpen_Click
 
End If
 

Thanks ADezii for all your help. For now, this will work but I'd be interested if anyone has a cleaner way to get from A => B.