Is Exist function

  • Thread starter Thread starter Nigel
  • Start date Start date
N

Nigel

I am looking for somethiong that will check to see if a
table or a field in a table exists. I am updating some
database by code and if the field or table allready exists
I want it to skip that and move on


Thanks

Nigel
 
For tables, here is a VBA function that you can use - along with sample
usage:

'Sample Usage
if tableexits("Customers") then
msgbox "There is a table named customers"
endif

'The function
Public Function TableExists(strTableName As String) As Boolean
Dim fExists As Boolean
Dim tdf As dao.TableDef
Dim db As dao.Database
Dim intI As Integer
Set db = CurrentDb()
Do Until intI = db.TableDefs.Count - 1 Or fExists
If db.TableDefs(intI).Name = strTableName Then
fExists = True
Else
intI = intI + 1
End If
Loop
TableExists = fExists
Set db = Nothing
Set tdf = Nothing
End Function

You shold be able to use the above as a model for a FieldExists function --
*or* -- take a look http://brenreyn.brinkster.net/default.asp for a more
generic ObjectExists function along with a well written article that
describes the different ways to define functions that determine whether an
object exists in a particular collection.
 
You can use code to test if a table exists:

If TableExists("SomeTable") = True Then
'it exists
Else
' it does not exist
End IF

Put this code in a module:

Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function

Bonus:
Function QueryExists(strQueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb.QueryDefs(strQueryName))
End Function

===========================================================
This may be the fastest technique. It relies on the use of the system
tables:
(Credit for this idea goes to Peter Miller.)

Function TableExists(sTblName As String) As Boolean
Dim rs As Recordset
Set rs = CurrentDb.openrecordset("Select id From msysobjects Where type=1
and name='" & sTblName & "';", dbOpenSnapshot)
If Not rs.EOF Then TableExists = True
rs.Close
Set rs = Nothing
End Function
 
You can use code to test if a table exists:
Click to expand...

Yes, you can.
If TableExists("SomeTable") = True Then
'it exists
Else
' it does not exist
End IF
Click to expand...

Good pseudocode.
Function TableExists(strTableName As String) As Boolean
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
Click to expand...

Bad code. How does that function differ from this one?

Function TableExists(strTableName As String) As Boolean
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
 
Back
Top