|
Subject:
|
Table Exist?
|
|
Posted By:
|
elansolutionsltd
|
Post Date:
|
1/18/2006 6:20:03 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?
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/18/2006 10:26:59 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)
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:
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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/18/2006 11:33:28 AM
|
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.
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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/18/2006 12:03:10 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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/18/2006 12:33:56 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
|
|
Reply By:
|
Bob Bedell
|
Reply Date:
|
1/23/2006 2:43:32 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
|
