Table Exist?

elansolutionsltd · January 18th, 2006, 07:20 AM

I frequently use SysCmd to check whether a form is open or not and it works very well. However, is there a variant of SysCmd that can tell me whether a table exists or not within the current (or even a remote) database? It can be done by scrolling through the tableDefs Collection in a For Each tdf in CurrentDb.TableDefs... Next and checking whether tdf.Name equals what I am searching for, but this seems a large waste of resources. There must be a better way?

Bob Bedell · January 18th, 2006, 11:26 AM

I think you're stuck with looping. While there's nothing quite as compact as SysCmd, I take the same approach and wrap my âtable existsâ functionality in a TableExists function, in the same way that I wrap SysCmd in an IsLoaded function. The following works with Jet or SQL Server (uses ADO)

Code:

Public Function TableExists(strTableName As String) As Boolean

   On Error GoTo Errorhandler:

   TableExists = False

   'g_cnn is the global, default ADO connnection my app is
   'using to any OLE DB Provider (Jet, SQL Server, etc.).
   'OpenSchema returns a read-only, static cursor of db
   'schema (catalog) information. adSchemaTables specifies a schema
   'query that returns tables and views (or Jet saved queries).
   With g_cnn.OpenSchema(adSchemaTables)
      If (False = .BOF) And (False = .EOF) Then
         .MoveFirst
         Do While Not .EOF
            'TABLE_NAME is a column-name defined in the returned
            'TABLES rowset.
            If 0 = StrComp(.Fields("TABLE_NAME"), strTableName, vbTextCompare) Then
               TableExists = True
               Exit Do
            End If
            .MoveNext
         Loop
      End If
      .Close
   End With
   Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function

Test with:

Code:

Global g_cnn As ADODB.Connection

Sub Test()

   Dim blnTableExists As Boolean          
   Set g_cnn = New ADODB.Connection

   'Open unsecured Jet connection
   With g_cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source") = "C:\Unsecured.mdb"
   End With

   'Open secured Jet connection
   'With g_cnn
   '   .Provider = "Microsoft.Jet.OLEDB.4.0"
   '   .Properties("Data Source") = "C:\SecuredDatabase.mdb"
   '   .Properties("Jet OLEDB:System Database") = "C:\WorkgroupInformationFile.mdw"
   '   .Properties("Mode") = adModeShareDenyNone
   '   .Properties("User ID") = "username"
   '   .Properties("Password") = "password"
   'End With

   'Open SQL Server connection
   'With g_cnn
   '   .Provider = "SQLOLEDB"
   '   .Properties("Data Source") = "(local)"
   '   .Properties("Initial Catalog") = "database"
   '   .Properties("User ID") = "sa"
   '   .Properties("Password") = "password"
   'End With

   g_cnn.Open

   blnTableExists = TableExists("tblRecords")


End Sub

HTH,

Bob

Bob Bedell · January 18th, 2006, 12:33 PM

For Jet tables only, if you're using CurrentProject.Connection, you can query the MSysObjects table. Not sure if you can read MSysObjects on a remote connection.

Code:

Public Function JetTableExists(strTableName As String) As Boolean

   Dim strSQL As String
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset

   On Error GoTo Errorhandler:

   JetTableExists = False

   strSQL = "SELECT COUNT(*) FROM MSYSObjects WHERE Type = 1" & _
        " AND [Name]='" & strTableName & "'"
 
    Set rst = g_cnn.Execute(strSQL)
    JetTableExists = rst(0)
    rst.Close
    Set rst = Nothing
    Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function

Bob

Bob Bedell · January 18th, 2006, 01:03 PM

I quess you could take the query approach with SQl Server too, and dispense with loopping for both Jet and SQL Server.

Public Function SQLServerTableExists(strTableName As String) As Boolean

   Dim strSQL As String
   Dim rst As ADODB.Recordset
   Set rst = New ADODB.Recordset

   On Error GoTo Errorhandler:

   SQLServerTableExists = False

   strSQL = "SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='" & strTableName & "'"

    Set rst = g_cnn.Execute(strSQL)
    If Not (rst.BOF And rst.EOF) Then SQLServerTableExists = True
    rst.Close
    Set rst = Nothing
    Exit Function

Errorhandler:
   Debug.Print "Error: " & Err.Number, Err.Description, "clsCreateTempTable.TableExists"
   Exit Function
   Resume
End Function

Bob

Bob Bedell · January 18th, 2006, 01:33 PM

Hereâs the remote MSysObjects fix:

In the remote Jet DB:

1. Tools-->Options-->View-->Show-->check System objects.

2. Tools-->Security-->User and Group Permissions-->Object Type = Tables --> Select Msysobjects from Object Name List --> Assign Read Data Permissions.

Bob

Bob Bedell · January 23rd, 2006, 03:43 AM

Yet another economical, "loopless" way to see if a table exists. Just trap the error:

Function TableExists(strTableName As String) As Boolean
    Dim dbs As DAO.Database, tdf As DAO.TableDef

    On Error Resume Next
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTableName)
    If Err = 3265 Then
        ' Table does not exist.
        TableExists = False
    Else
        ' Table exists.
        TableExists = True
    End If
    Err = 0
End Function

Bob