Checking if a table exists

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I have a command button which opens a form based on a table. The table may or may not exist as it is created using a make table query. I want to write a command which checks whether the table actually exists to determine the next action.
Any help much appreciated.

Sheila
 
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

There are several ways to define functions that determine whether an object
exists in a particular collection. The above method is probably the safest.
Brendan Reynolds has written a good article on this topic - you can find it
at his website: http://brenreyn.brinkster.net/default.asp
 
-----Original Message-----
Hi

I have a command button which opens a form based on a
Click to expand...
table. The table may or may not exist as it is created
using a make table query. I want to write a command which
checks whether the table actually exists to determine the
next action.
Any help much appreciated.

Sheila
.
Click to expand...
Please this function in a Code Module and call it as
follows:

If TblIsOK("name of table") then ...
else
end if

Function TblIsOK(strTblName As String) As Boolean

Dim TBL As Recordset, db As Database
Set db = CurrentDb
On Error Resume Next
Set TBL = db.OpenRecordset(strTblName)
If Err <> 0 Then ' Item not found.
TblIsOK = False
Else
TblIsOK = True
End If
On Error GoTo 0
Set TBL = Nothing
Set db = Nothing
End Function
 
Back
Top