ProgrammingMSAccess.com

The ProgrammingMSAccess.COM Site

A vba Procedure that inventories data access pages in a microsoft access database file or access project

'Create a table named dapInventory before running this procedure

'Create dapLInkName and dapFileName columns with Text, 255

'Create a dapConnectionString column with Memo

Sub inventoryDAPs()
Dim myAObject As AccessObject
Dim dap1 As DataAccessPage
Dim rst1 As ADODB.Recordset
Dim cmd1 As ADODB.Command

'Open recordset for data access page inventory
Set rst1 = New ADODB.Recordset
rst1.Open "dapInventory", CurrentProject.Connection, _
    adOpenKeyset, adLockOptimistic, adCmdTable

'Empty old inventory before repopulating table
Set cmd1 = New ADODB.Command
With cmd1
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "DELETE dapInventory.* From dapInventory"
    .CommandType = adCmdText
    .Execute
End With

'Start loop through pages
For Each myAObject In _
    Application.CurrentProject.AllDataAccessPages
With rst1
'Start to add a record to the inventory
    .AddNew
        rst1.Fields("dapLinkName") = myAObject.Name
        rst1.Fields("dapFileName") = myAObject.FullName
'Collect connect string info
'Open (and re-close) any pages that are closed
        If myAObject.IsLoaded = True Then
            Set dap1 = _
            Application.DataAccessPages(myAObject.Name)
            rst1.Fields("dapConnectionString") = _
                dap1.ConnectionString
        Else
            DoCmd.Echo False
            DoCmd.OpenDataAccessPage myAObject.Name
            Set dap1 = _
                Application.DataAccessPages(myAObject.Name)
            rst1.Fields("dapConnectionString") = _
                dap1.ConnectionString
            DoCmd.Close acDataAccessPage, _
                myAObject.Name, acSaveNo
            DoCmd.Echo True
        End If
    .Update
    .MoveNext
End With
Next myAObject


End Sub

Want to understand  Microsoft Access so that you can program it to do more tasks like this?  Get Programming Microsoft Office Access 2003 by Rick Dobson from Microsoft Press.  Learn more about the book by clicking here.

Copyright 1999 CAB, Inc. All rights reserved. Republication or redistribution of CAB, Inc. content, including by framing or similar means, is expressly prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be liable for any errors in the content, or for any actions taken in reliance thereon.