Reference an ADP database in code

Question

I am attempting to document the objects in an ADP database from Access VBA code.  I can accomplish this using "Set db = Application.DBEngine.OpenDatabase(<filename>, True)" for an MDB file but can't find an equivalent method for ADPs. 

I don't want to use the "OpenAccessProject" or "OpenCurrentDatabase" methods as these open the database in an Access window.

Answer 1

Hi Ray,

Try this Function below to retrieve all the Objects in your ADP database:

Note: the data will be in Immediate Window. With some enhancement you can write the data to a Text file instead.

Option Compare Database
Option Explicit

Function ListAllObject()

    Dim ao As AccessObject
    Dim db As Object
     
   On Error GoTo Error_Handler
        
    ' run the list in current project data
    Set db = Application.CurrentData
    
    ' list all Tables
    For Each ao In db.AllTables
       Debug.Print "Table: " & ao.Name
    Next ao
     
    ' list all Queries
      For Each ao In db.AllQueries
         Debug.Print "Query: " & ao.Name
      Next ao

    ' run the list in current project

    Set db = Application.CurrentProject
    
    ' list all Forms
      For Each ao In db.AllForms
         Debug.Print "Forms: " & ao.Name
      Next ao
    
    ' list all Reports
      For Each ao In db.AllReports
         Debug.Print "Reports: " & ao.Name
      Next ao
      
    ' list all Modules
      For Each ao In db.AllModules
         Debug.Print "Modules: " & ao.Name
      Next ao

    ' list all Macros
      For Each ao In db.AllMacros
         Debug.Print "Macros: " & ao.Name
      Next ao
     
Exit_Handler:

' clean up
Set ao = Nothing
Set db = Nothing

Exit Function

Error_Handler:
   
  MsgBox Err.Number & " " & Err.Description
   
   Resume Exit_Handler

End Function

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Answer 2

Thanks Daniel,

I should have made it clearer that I am try to document a database other than the current database.  Essentially I am writing a utility to scan all of the folders in a specified path, locate the ADP files, and document each.

Answer 3

Hi Ray,

I understand, I believe you have no other choice to retrieve info from an external database to use the OpenDatabase or OpenAccessProject.

However there is also a limit of information you can retrieve. I believe you can only retrieve the TableDef and QueryDef, so only tables and queries you can list. The other Objects are not part of the OpenDatabase object, and therefor not in reach to use.

I will dig into this later and see if there is any other way to retrieve the info.

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Answer 4

Hi Ray

I may missunderstand your question, but maybe it helps:

ADP is storing it's data in SQL Server database. If you want to open the datastore of an ADP project you will have to grab the connection in the ADP file (for example using Access in OLE mode) and then use this connection to access the database on the SQL Server. ADP files aren't databases.

HTH
Henry

Ray Brack wrote:

I am attempting to document the objects in an ADP database from Access
VBA code. I can accomplish this using "Set db =
Application.DBEngine.OpenDatabase(<filename>, True)" for an MDB file but
can't find an equivalent method for ADPs.

I don't want to use the "OpenAccessProject" or "OpenCurrentDatabase"
methods as these open the database in an Access window.

Answer 5

Hi Henry,

Thanks for your reply.

I'm not attempting to access the data, I'm trying to parse all of the objects in the ADP such as forms, reports, macros, etc and document their properties.

Regards,

Ray

Answer 6

Ok, now I understand. And you are probably not running within the ADP that you want to list it's context.

You can achieve this by OLE automation of Access, opening the ADP in there and list it's context. Here a code example:

Public Function listADPObjects()
  Dim appAccess As Access.Application
  Dim I As Long
  Set appAccess = CreateObject("Access.Application")
  appAccess.OpenAccessProject "U:\Access DBs und Tools\TestADP.adp", False
  Debug.Print "Tables:"
  For I = 0 To appAccess.CurrentData.AllTables.Count - 1
    Debug.Print appAccess.CurrentData.AllTables(I).Name
  Next
  Debug.Print "Forms:"
  For I = 0 To appAccess.CurrentProject.AllForms.Count - 1
    Debug.Print appAccess.CurrentProject.AllForms(I).Name
  Next
  Debug.Print "Reports:"
  For I = 0 To appAccess.CurrentProject.AllReports.Count - 1
    Debug.Assert appAccess.CurrentProject.AllReports(I).Name
  Next
End Function

Don't forget to close and distroy the Project and appAccess at the end

HTH

Henry

Answer 7

Hi Henry,

Thanks again for your help however I'm trying to avoid the OpenAccessProject method for two reasons;

1) This opens another Access window (not a big issue but would rather the application was not visible).

2) This runs the "autoexec" macro if it exists in the database which in many instances in my organisation will close the database if the user running the documentation process has not been added to the "user" table.

Regards,

Ray

Answer 8

Hi Ray

I don't know another way to do it, sorry. To your points:

1) The Access.Application is loaded already. Opening another instance will not take much ressources and as you stated correctly, the application isn't started visible, but in OLE automation mode.

2) You can prevent this by adding a few line of code in the application. I agree this is an issue if you don't have control over these applications. Code would look as follows:

  If Application.UserControl = False Then
    Cancel = True
    Exit Sub
  End If

You can use this code in the FormOpen event of your start form. If you are using an Autoexec Makro you may call similar code from there to stop the processing of the Autoexec Makro.

HTH

Henry