How to list forms & reports from local or external accdb

[Guillem]

Hello, can someone help with this confusing(for me) things?
Is it possible to have a list of Forms, choosing both from the local database or another selected database. I tried many combinations but unsuccessfully. The code that follows works fine from local db, but fail trying with an external db.
Here is the code :
<<
Dim pLocal As Boolean
Dim DbsExt As String
Dim appAccess As Access.AccessObject
Dim db As Access.AccessObject
Dim obj As Access.AccessObject
Dim frm As Access.Form


DbsExt = "set to FilePath of Existing .accdb database"
pLocal = False 'set true for local db, false for external db

Set db = Nothing
If pLocal = True Then 'Local Forms
Set db = CurrentProject
Else 'External Forms
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase DbsTxt, True
Set db = appAccess
End If

'do Forms
For Each obj In db.AllForms 'FAILS for external DBs
Debug.Print "Form = " & obj.Name
Next
>>

it’s probably easier to do separate procedures ... but I’d like to understand these differences.
Thank you.

 

[theDBguy]

Hi. You would do us a big favor if you enclose your code snippets in code tags. It makes them easier to read and follow. Just my 2 cents...

 

[Deleted member 147267]

I'd say it's because you're mixing your objects. I wouldn't create an application object then try to set it to a particular database. They are not the same thing.
Keep them separate and ensure they follow a parent/child type of relationship.
Ditto on the code tags. Yours is just below the point where I'd pass on it.

 

[Isaac]

Specifically, these two lines:
appAccess.OpenCurrentDatabase DbsTxt, True Set db = appAccess
Should probably be changed to:

Set db = appAccess.OpenCurrentDatabase DbsTxt, True

or,

appAccess.OpenCurrentDatabase DbsTxt, True
Set db = appAccess.CurrentDB

 

[Guillem]

Hi. You would do us a big favor if you enclose your code snippets in code tags. It makes them easier to read and follow. Just my 2 cents...

Thanks!

I'd say it's because you're mixing your objects. I wouldn't create an application object then try to set it to a particular database. They are not the same thing.
Keep them separate and ensure they follow a parent/child type of relationship.
Ditto on the code tags. Yours is just below the point where I'd pass on it.

I was wondering if it was possible to use the current database in the same way as another open database.
Anyway , Thanks

 

[MajP]

I was wondering if it was possible to use the current database in the same way as another open database.

Can you clarify what you mean?

 

[Guillem]

Can you clarify what you mean?

I don’t understand that there is no similar way to manage objects in the current database than in an external database.
For example .AllForms I can't find a similar way to handle it in both cases. I apologize for my bad English. I try to do my best but it scares me to stay confused.
Thanks!!

 

[MajP]

To get an external code project

Private Function CodeProject_External(FullPath As String) As CodeProject
  Dim appAccess As Access.Application
  Dim db As dao.Database
  Set appAccess = CreateObject("Access.Application")
 ' Open database in Microsoft Access window.
  appAccess.OpenCurrentDatabase FullPath
  Set CodeProject_External = appAccess.CurrentProject
End Function

Therefore this would load the local and specified lists

Private Sub cmdForms_Click()
  Dim frms As String
  Dim frm As AccessObject
  Dim CP As CodeProject
 
  'Use the current code project
  For Each frm In CurrentProject.AllForms
    If frms = "" Then
      frms = frm.Name
    Else
      frms = frms & vbCrLf & frm.Name
    End If
   Next frm
   Me.txtDB1 = frms
    
   'Use the external Codeproject
   Set CP = CodeProject_External(GetFullPath("db2.accdb"))
   frms = ""
   For Each frm In CP.AllForms
    If frms = "" Then
      frms = frm.Name
    Else
      frms = frms & vbCrLf & frm.Name
    End If
   Next frm
   Me.txtDB2 = frms
End Sub
Private Function GetFullPath(DBname As String) As String
  'assume in same directory
   Dim db As dao.Database
  GetFullPath = CurrentProject.Path
  GetFullPath = GetFullPath & "\" & DBname
End Function

Private Function CodeProject_External(FullPath As String) As CodeProject
  Dim appAccess As Access.Application
  Dim db As dao.Database
  Set appAccess = CreateObject("Access.Application")
 ' Open database in Microsoft Access window.
  appAccess.OpenCurrentDatabase FullPath
  Set CodeProject_External = appAccess.CurrentProject
End Function

 

[MajP]

If you want to work with an external datbase, the easiest is to add it as a reference. Just click on references and browse to the external db. Now you can do things like show DB2 forms in DB1.
I assume you want to do something more important than simply list form names. What do you want to do with the external DB?

 

[Guillem]

If you want to work with an external datbase, the easiest is to add it as a reference. Just click on references and browse to the external db. Now you can do things like show DB2 forms in DB1.
I assume you want to do something more important than simply list form names. What do you want to do with the external DB?

I need to convert some huge db from access 2003 to 2019 and i want to apply themes and colors and shapes and everything that is fine to apply in an .accdb copy. I prefer to do it from an external base. Yes, including them in the references is a very good idea. Thanks!