Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Navigation

Google Ads

Table, Query, Form, Report, Module Exists

From Wiki

Jump to: navigation, search

These two functions allow you to determine whether or not a table, query, form, report or module exists. ObjectExists uses MSysObjects, which is a system table, so it comes with a couple of warnings: 1. Tampering with system tables can grievously damage the health of your database. 2. System tables are an 'unsupported feature', however, they have existed in the last several releases.

Object Exists

  1. Public Function GetObjType(typeID As Integer)
  2.     Select Case typeID
  3.         Case Is = 1
  4.             GetobjType = "Table"
  5.         Case Is = 4
  6.             GetobjType = "ODBC Attached Table" 'Linked
  7.         Case Is = 5
  8.             GetobjType = "Query" 'NB ~SQL
  9.         Case Is = 6
  10.             GetobjType = "Attached Table" 'Linked
  11.         Case Is = -32768
  12.             GetobjType = "Form"
  13.         Case Is = -32764
  14.             GetobjType = "Report"
  15.         Case Is = -32761
  16.             GetobjType = "Module"
  17.     End Select
  18. End Function
  19.    
  20. Function ObjectExists(ObjectName As String, ObjectType As String) As Boolean
  21. Dim strWhere As String
  22.  
  23.     Select Case ObjectType
  24.         Case Is = "Table"
  25.             'Tables are 1,
  26.             'Linked tables are 6,
  27.             'ODBC-linked tables are 4.
  28.             strWhere = "(Type = 1 Or Type = 4 Or Type = 6)"
  29.         Case Is = "Query"
  30.             strWhere = "Type= 5 "
  31.         Case Is = "Form"
  32.             strWhere = "Type= - 32768"
  33.         Case Is = "Report"
  34.             strWhere = "Type= - 32764"
  35.         Case Is = "Module"
  36.             strWhere = "Type= - 32761"
  37.     End Select
  38.        
  39.     strWhere = "[Name]='" & ObjectName & "' AND " & strWhere
  40.     ObjectExists = Not IsNull(DLookup("Name", "MSysObjects", strWhere))
  41.  
  42. End Function

It is probably easiest to use schemas for external tables. You can use this example like so: Externaltableexists("tblExample", "C:\Docs\LTD.mdb")

Example: External Table Exists

  1. Function ExternalTableExists(TableName, DBFullPath)
  2. Dim cn As New ADODB.Connection
  3. Dim rs As ADODB.Recordset
  4.  
  5.     Set cn = CreateObject("ADODB.Connection")
  6.     Set rs = CreateObject("ADODB.Recordset")
  7.    
  8.     cn.Open "Provider  =Microsoft.Jet.OLEDB.4.0;" & _
  9.     "Data Source=" & DBFullPath & ";User Id=admin;Password=;"
  10.    
  11.      Set rs = cn.OpenSchema( _
  12.         adSchemaTables, Array(Empty, Empty, TableName))
  13.     ExternalTableExists = Not rs.EOF
  14.    
  15.     rs.Close
  16.     Set rs = Nothing
  17.     cn.Close
  18.     Set cn = Nothing
  19. End Function

However, here is a DAO example as well. This will find linked tables (Type 6) in an external database.

Example: External Linked Table Exists Using DAO

  1. Sub ExternalTableExists2(TableName, DBFullPath)
  2. Dim rs As DAO.Recordset
  3.    
  4.     'See above for a list of types
  5.     strSQL = "SELECT ForeignName, [Name] FROM MSysObjects " _
  6.         & "IN '" & DBFullPath & "' " _
  7.         & "WHERE (ForeignName='" & TableName _
  8.         & "' Or [Name]='" & TableName & "') " _
  9.         & "AND (Type=4 Or Type=6)"
  10.        
  11.     Set rs = CurrentDb.OpenRecordset(strSQL)
  12.    
  13.     Do While Not rs.EOF
  14.         Debug.Print rs.Fields(0), rs.Fields(1)
  15.         rs.MoveNext
  16.     Loop
  17.    
  18.     rs.Close
  19.     Set rs = Nothing
  20. End Sub


Further Information http://forum.dev.lessthandot.com/viewtopic.php?f=95&t=420

I may have 'borrowed' some of this code, if so, please let me know.

271 Rating: 2.3/5 (15 votes cast)