How can I determine if a Table exists.

[Ron]

I'm writing a module to compare values between a text file and an XML file.
I'm doing this by importing both into Access and generating SQL to select and
compare variables in the tables. In some cases a table is not defined in the
XML but does exist in the text file. How can I avoid generating SQL that
attempts to access a table that doesn't exist?

I'm also running into situations where a colunm is defined in the txt file,
but not the XML. Is there a way I determine if a column exists before
executing a query? I want to avoid the Enter Parameter Value msgbox.

Thanks,
Ron

 

[Allen Browne]

Here's a couple of approaches:

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

Function TableExists(strTable As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = CurrentDb().TableDefs(strTable)
TableExists = (Err.Number = 0&)
End Function

 

[Klatuu]

Public Function TableExists(strTableName As String) As Boolean
On Error GoTo TableExists_Error

TableExists = Not IsNull(DLookup("[Name]", "MsysObjects", _
"[Type] In (1,4,6) " & "And [Name] ='" & strTableName & "'"))

TableExists_Exit:

Exit Function

TableExists_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure TableExists of Module modUtilities"
GoTo TableExists_Exit

End Function

 

[Ron]

Thanks for the help!

Here's a couple of approaches:

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

Function TableExists(strTable As String) As Boolean
Dim varDummy As Variant
On Error Resume Next
varDummy = CurrentDb().TableDefs(strTable)
TableExists = (Err.Number = 0&)
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I'm writing a module to compare values between a text file and an XML
file.
I'm doing this by importing both into Access and generating SQL to select
and
compare variables in the tables. In some cases a table is not defined in
the
XML but does exist in the text file. How can I avoid generating SQL that
attempts to access a table that doesn't exist?

I'm also running into situations where a colunm is defined in the txt
file,
but not the XML. Is there a way I determine if a column exists before
executing a query? I want to avoid the Enter Parameter Value msgbox.

Thanks,
Ron