Module: ADP in Category Microsoft Access : Environment from Total Visual SourceBook

Routines for running Access Data Projects (ADPs) using VBA.

Microsoft Access 2013 no longer supports ADPs. This code only runs in Access 2000, 2002, 2003, 2007 and 2010.

Procedure Name Type Description
(Declarations) Declarations Declarations and private variables for the modADP module.
GetSysCommentsText Procedure Get TSQL or other text from syscomments for the specified object.
GetSysObjectID Procedure Get the id of the specified object from the SQL Server/MSDE sysobjects table.
IsADPSystemObject Procedure Determine if the specified object is a system object (for ADPs only).
IsCurrentProjectADE Procedure Determine if the current project is an ADE.
IsSystemTable_ADP Procedure Determine if the specified table is a system object.
ObjectNameHasOwner Procedure Determine if the specified object name has an owner name prepended.
ParseADPObjectName Procedure Breaks the specified object name into owner and object components.
PopulateSysUsersArray Procedure Populate a dynamic array with a list of users from SQL Server sysusers table.
StripADPOwnerName Procedure Strips the owner name from the beginning of an SQL Server object name.
TableViewNamesToArray Procedure Loads an array with the tables or views in an ADP with options to include owner and exclude system objects.
ClearFormServerFilter Procedure Clears any server filters that may have been saved with the form.
ClearReportServerFilter Procedure Clears any server filters that may have been saved with the report.
' Example of the modADP module
' Microsoft Access 2013 no longer supports ADPs. This code only runs in Access 2000, 2002, 2003, 2007 and 2010.
' To use this example:
' 1. Create a new ADP file that is connected to the Sample database on any SQL Server
' 2. Create a new form
' 3. Add a command button named cmdTest
' 4. Add a command button named cmdDBPropExists
' 4. Paste the entire contents of this module into the new form's module.
' 5. Add modADP to the ADP project

Private Sub cmdTest_Click()

  Const cstrTable As String = "Categories"
  Const cstrSysTable As String = "sysObjects"
  Const cstrSeparator As String = "------------------"

  Dim strOwner As String
  Dim strObj As String
  Dim astrObjs() As String
  Dim astrUsers() As String
  Dim lngUsers As Long
  Dim lngCounter As Long
  Dim intObjects As Integer

  ' Test GetSysCommentsText
  Debug.Print cstrSeparator & "Test GetSysCommentsText" & cstrSeparator
  Debug.Print GetSysCommentsText(1) & vbCrLf

  ' Test GetSysObjectID
  Debug.Print cstrSeparator & "Test GetSysObjectID" & cstrSeparator
  Debug.Print GetSysObjectID(cstrTable, "Table") & vbCrLf

  ' Test IsADPSystemObject
  Debug.Print cstrSeparator & "Test IsADPSystemObject" & cstrSeparator
  Debug.Print cstrTable & " is a system object? " & IsADPSystemObject(cstrTable, "Table")
  Debug.Print cstrSysTable & " is a system object? " & IsADPSystemObject(cstrSysTable, "Table") & vbCrLf

  ' Test IsCurrentProjectADE
  Debug.Print cstrSeparator & "Test IsCurrentProjectADE" & cstrSeparator
  Debug.Print "Current project is a ADE? " & IsCurrentProjectADE() & vbCrLf

  ' Test IsSystemTable_ADP
  Debug.Print cstrSeparator & "Test IsSystemTable_ADP" & cstrSeparator
  Debug.Print cstrTable & " is a system table? " & IsSystemTable_ADP(cstrTable)
  Debug.Print cstrSysTable & " is a system table? " & IsSystemTable_ADP(cstrSysTable) & vbCrLf

  ' Test ObjectNameHasOwner
  Debug.Print cstrSeparator & "Test ObjectNameHasOwner" & cstrSeparator
  Debug.Print cstrTable & " has owner? " & ObjectNameHasOwner(cstrTable) & vbCrLf

  ' Test ParseADPObjectName
  Debug.Print cstrSeparator & "Test ParseADPObjectName" & cstrSeparator
  If ParseADPObjectName("dbo." & cstrSysTable, strOwner, strObj) Then
    Debug.Print "Owner: " & strOwner & "; Object: " & strObj & vbCrLf
    Debug.Print "ParseADPObjectName Failed" & vbCrLf
  End If

  ' Test PopulateSysUsersArray
  Debug.Print cstrSeparator & "Test PopulateSysUsersArray" & cstrSeparator
  lngUsers = PopulateSysUsersArray(astrUsers)
  Debug.Print lngUsers & " users: "
  For lngCounter = 0 To lngUsers - 1
    Debug.Print "   " & astrUsers(lngCounter)
  Next lngCounter

  ' Test StripADPOwnerName
  Debug.Print cstrSeparator & "Test StripADPOwnerName" & cstrSeparator
  Debug.Print "dbo." & cstrSysTable & " name without Owner is " & StripADPOwnerName("dbo." & cstrSysTable)

  ' Test ClearReportServerFilter and ClearFormServerFilter
  Debug.Print cstrSeparator & "Test ClearReportServerFilter and ClearFormServerFilter" & cstrSeparator
  If MsgBox("Do you want to clear the server filter from all forms and reports?", vbYesNo) = vbYes Then
    Debug.Print "Server filter cleared from " & ClearReportServerFilter() & " reports."
    Debug.Print "Server filter cleared from " & ClearFormServerFilter() & " forms."
  End If

  ' Example of TableViewNamesToArray
  intObjects = TableViewNamesToArray(acTable, True, True, astrObjs)
  Debug.Print intObjects & " ADP tables: "
  For lngCounter = 0 To intObjects - 1
    Debug.Print astrObjs(lngCounter)
  Next lngCounter

End Sub

Private Sub Form_Load()
  Me.cmdTest.Caption = "Test modADP"
  Me.cmdDBPropExists.Caption = "Test Property"
End Sub

Private Sub cmdDBPropExists_Click()
  ' See if a database property name exists

  Dim strProperty As String

  strProperty = InputBox("Enter property name to test")
  If strProperty <> "" Then
    If ProjectPropertyExists(strProperty) Then
      MsgBox "Property " & strProperty & " exists"
      MsgBox "Property " & strProperty & " does not exist"
    End If
  End If

End Sub

Total Visual SourceBook The source code in Total Visual Sourcebook includes modules and classes for Microsoft Access, Visual Basic 6 (VB6), and Visual Basic for Applications (VBA) developers. Easily add this professionally written, tested, and documented royalty-free code into your applications to simplify your application development efforts.

Total Visual SourceBook is written for the needs of a developer using a source code library covering the many challenges you face. Countless developers over the years have told us they learned some or much of their development skills and tricks from our code. You can too!

Additional Resources

Total Visual SourceBook CD and Printed Manual

Microsoft Access/ Office 2016, 2013, 2010, and 2007 Version
is Shipping!

New features in Total Visual SourceBook for Access, Office and VB6

Supports Access/Office 2016, 2013, 2010 and 2007, and Visual Basic 6.0!

View all FMS products for Microsoft Access All Our Microsoft Access Products


Reader Choice Award for MS Access Source Code Library
Reader Choice

"The code is exactly how I would like to write code and the algorithms used are very efficient and well-documented."

Van T. Dinh, Microsoft MVP

SourceBook Info

Additional Info




Free Product Catalog from FMS