Check if Table Exists in Back End

[Guest]

I current use this to test if a table exists in the front end:

If DCount("*", "MsysObjects", "[Name]='tblTempXLS'") > 0 Then
'Table Exists

How Would I amend this to test for a table in the back end?

Many thanks.

 

[Amy Blankenship]

On Error Resume Table_Missing
Dummy = DCount("*", "tblTempXLS")
Exit Function
Table_Missing:
'Table is missing
Err.Clear
Resume Next

 

[Guest]

IF DCount("*", "MsysObjects", "[Name]='tblTempXLS' and [TYPE] = 6") > 0 Then
'Table Exists'

 

[Dirk Goldgar]

I current use this to test if a table exists in the front end:

If DCount("*", "MsysObjects", "[Name]='tblTempXLS'") > 0 Then
'Table Exists

How Would I amend this to test for a table in the back end?

You'd need to open a connection to the back-end database, and check
there. First you'd need to determine the path to the back-end, and then
you could open a DAO Database object on that path and check the
database's TableDefs collection. See the following two functions:

'----- start of code -----
Function fncDataDBName() As String

' Return the name (including path) of the "back-end" database
' containing the data this application is working with. If the
' data tables are local, not linked, return the name of the current
' database.

'*** NOTE: This code presumes that the front-end database contains
' a linked table named "tblProfile". Change that as needed for
' your application.

Static strDBFileName As String

On Error Resume Next

If Len(strDBFileName) = 0 Then
strDBFileName = CurrentDb.TableDefs("tblProfile").Connect
If Left$(strDBFileName, 10) = ";DATABASE=" Then
strDBFileName = Mid$(strDBFileName, 11)
Else
strDBFileName = CurrentDb.Name
End If
End If

fncDataDBName = strDBFileName

End Function


Function fncTableExists( _
TableName As String, _
Optional DBPath As String) _
As Boolean

Dim db As DAO.Database

If Len(DBPath) > 0 Then
Set db = DBEngine.OpenDatabase(DBPath)
Else
Set db = CurrentDb
End If

If Len(TableName) = 0 Then Err.Raise 5

On Error Resume Next
fncTableExists = IsObject(db.TableDefs(TableName))

If Not db Is Nothing Then
db.Close
Set db = Nothing
End If

End Function
'----- end of code -----

Given such functions (after substituting one of your linked table names
in fncDataDBName as indicated), you can check for table "TableX" in the
back-end using code like this:

If fncTableExists("TableX", fncDataDBName()) Then
' ... the table exists in the back-end
Else
' ... it doesn't.
End If

 

[Allen Browne]

You could OpenDatabase and look in the MSysObjects there.

Or you could try opening a recordset using the table name and file name:

Function TableExists(strTable As String, Optional strFile As String) As
Boolean
Dim rs As DAO.Recordset
Dim strSql As String

strSql = "SELECT * FROM [" & strTable & "]"
If strFile <> vbNullString Then
strSql = strSql & " IN """ & strFile & """"
End If
strSql = strSql & " WHERE (False);"

On Error Resume Next
Set rs = DBEngine(0)(0).OpenRecordset(strSql)
TableExists = (Err.Number = 0&)
rs.Close
Set rs = Nothing
End Function

 

[Guest]

Put a function like this in a standard module in the front end:

Public Function TableExists(strDb, strTable) As Boolean

On Error GoTo Err_Handler

Const TABLENOTFOUND = 3265

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & strDb & ";"
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables(strTable)
Set cat.ActiveConnection = Nothing

TableExists = True

Exit_Here:
Exit Function

Err_Handler:
Select Case Err.Number
Case TABLENOTFOUND
Case Else
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
End Select
TableExists = False
Resume Exit_Here

End Function

Note that this function requires a reference to exist to the Microsoft ADO
Extensions for DDL and Security library, as well as the usual Microsoft
ActiveX Data Objects Library (Tools|Referencs on the VBA menu bar). Call it
by passing the path to the back end file and the name of the table into the
function, e.g.

Dim strMessage As String

If TableExists("F:\SomeFolder\SomeSubfolder\SomeDb.mdb", "SomeTable") Then
strMessage = "SomeTable exists."
Else
strMessage = "SomeTable does not exist."
End If

MsgBox strMessage, vbInformation, "Find Table"

Note that this finds a table in the specified file, but that does not mean
there is a link to that table in the current database.

Incidentally using the system objects is not best programming practice as
these are undocumented. To find if table exists in the front end you can
use something like this:

Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim strMessage As String
Dim strTable As String

strTable = <get table name from somewhere>

Set cat = New Catalog
cat.ActiveConnection = CurrentProject.Connection

On Error Resume Next
Set tbl = cat(strTable)
If Err = 0 Then
strMessage = strTable & " exists."
Else
strMessage = strTable & " does not exist."
End If

MsgBox strMessage, vbInformation, "Find Table"
On Error GoTo 0

Ken Sheridan
Stafford, England

 

[Guest]

Thanks all for the replies - much appreciated.

I went with a variation of Dirks reply using IsObject.