Does a table exist - True/False or yes/no

  • Thread starter Thread starter Baz
  • Start date Start date
B

Baz

Hiya,

Is there a method I can use that will give me a yes/no
result or whatever to tell me if a table exists or not?

As an example, I import a text file
called 'transactions.txt' - I then want to do different
actions if a table called 'transactions_ImportErrors' has
been created. So basically I need to find out if this file
exists or not within the VBA script.

I'd really apreciate this - sorry if this is answered
elsewhere, I have had a look!

Baz
 
Tell me if what I did is rubbish please:

Public Function TableExists(strTableName As String) As
Boolean

TableExists = Nz(DLookup
("[Id]", "[MSysObjects]", "[Type]=1 AND [Name]='" &
strTableName & "'"))

End Function
 
'----------------------------------------------------------
' Table Exist
' This sub will use the SelectObject Action to try and
' locate the table. If the table exist the varable
' TableExist will set to Yes. If the tables does not
' exist, the action will error be sent to the error
' handler and the varable will set to No.
'----------------------------------------------------------
Sub Table_Exist()
Dim TableExist As String

On Error GoTo Table_Exist_Err

TableExist = ""
DoCmd.SelectObject
acTable, "transactions_ImportErrors", True
TableExist = "Yes"

Table_Exist_Exit:
Exit Sub

Table_Exist_Err:
TableExist = "No"
Exit Sub

End Sub
 
Here is a general purpose function for checking to see if
a table exists. With updating, I've been using it since
Access 2.0. Just call the function and pass the name of
the table. It will return True or False. For example:

If TableExists("MyTable") Then

''' Do Something
Else
''' Do Something Else
End If

Function TableExists(strTableName As String) As Integer
Dim aob As AccessObject
Dim I As Integer
Dim intMarker As Integer
On Error GoTo Err_TableExists
' Default value is Table does not exist
intMarker = False
With CurrentData
' Go thru the Alltables collection and try to find
' strTableName
For Each aob In .AllTables
If aob.Name = strTableName Then
' If found set to True and get out
intMarker = True
Exit For
End If
Next aob
End With
' Assign functions return value
TableExists = intMarker

Exit_TableExists:
Exit Function

Err_TableExists:
MsgBox Err.Number & " " & Err.Description
Resume Exit_TableExists

End Function
 
Back
Top