Retrieve report caption from another MDB

[vbajock]

I've tried a dozen ways to do it, and nothing works, perhaps some can help. I have a database that is a data dictionary application. I want to populate a table with the name of all the reports in all the .mdb's on the server, which is so far the easy part, using the handy little Sql:

sql = "SELECT MSysObjects.Name"
sql = sql + " FROM MSysObjects IN '" + mdbpath + "'"
sql = sql + " WHERE MSysObjects.Type = -32764"
sql = sql + " ORDER BY MSysObjects.Name;"
rsSource.Open sql etc

The problem is in retrieving the report caption. I've tried using the AllReports collection:

'*********************

Dim aob as AccessObject


For Each aob In CurrentProject.AllReports

strTemp = aob.Name

If strTemp = repname Then
DoCmd.OpenReport repname, acViewDesign
rs!ItemText = Reports(aob.Name).Caption
rs!ReportCaption = Reports(aob.Name).Caption

rs.Update
DoCmd.Close acReport, aob.Name
On Error Resume Next

Do

Loop Until Reports(repname).IsLoaded = False




Exit For
End If


Next

End With
'*********************

which works great of the current database, but does not work for a remote database because I cannot seem to figure out how to set

CurrentProject.AllReports

to be

SomeOtherProject.AllReports

Can anyone help?

Thanks
Kirk

 

[PWise]

Dim appAccess As Access.Application
Dim rpt
Dim mdbpath As String
mdbpath = "xxxx"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase (mdbpath)
For Each rpt In appAccess.CurrentProject.AllReports
appAccess.DoCmd.OpenReport rpt.Name, acViewDesign
Debug.Print appAccess.Reports(0).Caption
appAccess.DoCmd.Close , rpt.Name, acSaveNo

Next

 

[vbajock]

I tried something like that, but it locks up the workstation and I have to crash out in Task Manager to get out, and I had the same results with your code. I am wondering if I need to pass Acces security parameters even tho I am starting out in a database where I have logged in using credentials with rights to everything. I'll tinker with that next.

 

[vbajock]

I figured out the lock up, there is a log in box popping up behind the code window, so the problem is, how do you pass a password to the application when Work Group Security is in use?