none
Reference an ADP database in code RRS feed

  • 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.

    Thursday, February 16, 2012 11:57 PM

Answers

  • 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


    • Marked as answer by Ray Brack Tuesday, May 22, 2012 10:52 PM
    Tuesday, February 21, 2012 3:06 AM

All replies

  • 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"


    Friday, February 17, 2012 1:16 AM
    Moderator
  • 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.

    Friday, February 17, 2012 1:22 AM
  • 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"

    Friday, February 17, 2012 2:04 AM
    Moderator
  • 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.

    Monday, February 20, 2012 9:44 AM
  • 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

    Monday, February 20, 2012 10:05 PM
  • 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


    • Marked as answer by Ray Brack Tuesday, May 22, 2012 10:52 PM
    Tuesday, February 21, 2012 3:06 AM
  • 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

    Tuesday, February 21, 2012 3:18 AM
  • 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

    Tuesday, February 21, 2012 3:36 AM