Total Visual SourceBook

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 2007 and VB6

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

Separate version for Access/Office 2003, 2002, and 2000.

Separate version for:
Access 97/95


View all FMS products for Microsoft AccessAll Our Microsoft Access Products

SourceBook Info:

Why SourceBook?

 

"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

 

 

 

Microsoft Access, Office/VBA, VB6 Modules royalty-free source code libraryMicrosoft Access Modules royalty-free source code libraryMicrosoft Access VBA and VB6 Module Library Free trial of Total Visual SourceBook for Microsoft Access, Office, VB6 and VBA

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 List

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 Code for Using Module: ADP

' 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
  Else
    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"
    Else
      MsgBox "Property " & strProperty & " does not exist"
    End If
  End If

End Sub

Microsoft Access Module LibraryOverview of 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.

Additional Resources