How to list forms & reports from local or external accdb (1 Viewer)

Guillem

Member
Local time
Today, 09:57
Joined
Mar 28, 2020
Messages
32
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.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 01:57
Joined
Oct 29, 2018
Messages
21,357
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...
 

Micron

AWF VIP
Local time
Today, 04:57
Joined
Oct 20, 2018
Messages
3,476
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

Lifelong Learner
Local time
Today, 01:57
Joined
Mar 14, 2017
Messages
8,738
Specifically, these two lines:

appAccess.OpenCurrentDatabase DbsTxt, True
Set db = appAccess

Should probably be changed to:
Code:
Set db = appAccess.OpenCurrentDatabase DbsTxt, True
or,
Code:
appAccess.OpenCurrentDatabase DbsTxt, True
Set db = appAccess.CurrentDB
 

Guillem

Member
Local time
Today, 09:57
Joined
Mar 28, 2020
Messages
32
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

You've got your good things, and you've got mine.
Local time
Today, 04:57
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 09:57
Joined
Mar 28, 2020
Messages
32
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

You've got your good things, and you've got mine.
Local time
Today, 04:57
Joined
May 21, 2018
Messages
8,463
To get an external code project
Code:
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
Code:
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

You've got your good things, and you've got mine.
Local time
Today, 04:57
Joined
May 21, 2018
Messages
8,463
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

Member
Local time
Today, 09:57
Joined
Mar 28, 2020
Messages
32
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!
 

Users who are viewing this thread

Top Bottom