Finding out if a table exists

[clintM]

Depending on what has happend previously in my program, a certain table may or may not exist. When it doesn't exist I get problems. How can I find out if it exists ????

This is a major headache, please be as helpful as you can as I've only been programming Access/SQL for a week and have been chucked in at the deep-end! Helllllllllllp!!!

 

[jebry]

Hi!

One way that comes to mind would be to write a public function like the following:

Public Function TableExistance(TableToFind as String) as Boolean

Dim TblDefCheck as DAO.TableDef
Dim MyDbs as DAO.Database

Set MyDbs = CurrentDb
For Each TblDefCheck in MyDbs
If TblDefCheck.Name = TableToFind Then
TableExistance = True
GoTo ExitPoint
End If
Next TblDefCheck
TableExistance = False

ExitPoint:
Set TblDefCheck = Nothing
Set Mydbs = Nothing

End Function

This fuction will return a value of true if the table is found and a value of false if it isn't found. Then you can use it for conditions statements like:

If TableExistance(MyTablesName) Then

You could also make this a function in a form, but it sounds like you may need to check for the table's existance in several places.

hth
Jeff Bridgham

 

[Guest_imported]

Even simpler is this. If you're simply checking whether the table named tname exists, no need to step through the Tabledefs collection. If you try to set a Tabledef variable to it, it will work if it exists, and raise an error if it doesn't:

Public Function DoesTableExist(tname) as boolean
Dim result
dim db as dao.database, t as dao.tabledef
on error goto errs
'use primitive exception handling to catch the error...
if tname & "" = "" then
result = false
else
set db = currentdb
set t = db.tabledefs(tname)
end if

errs:
'If table exists, error won't be thrown,
'i.e., Err.number = 0.
result = (err.number = 0)

DoesTableExist = Result

end function

 

[Aivars]

Hi!

Example
If IsTableInDB("MyTableName&quot then
docmd.close
end if

or

me.cmdOpenForm.enabled=IsTableInDB("MyTableName&quot
'-----------------------------
'Copy this codes into any module and use it when it's needed:
Public Function IsTableInDB(strTableName As String) As Boolean
'This function return True when Table exists
'or False if it don't exist
'strTableName - finding table name
Dim rst As Recordset
Dim strSQL As String
Dim strWhere As String

'MSysObjects - System table (may be hidden)
strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
'Type=1 - tables of current DB
'Type=6 - linked tables
strWhere = "WHERE MSysObjects.Name='" & strTableName & _
"' AND (MSysObjects.Type=1 Or MSysObjects.Type=6)"
strSQL = strSQL & strWhere & ";"
Set rst = CurrentDb.OpenRecordset(strSQL)
IsTableInDB = Not rst.EOF
rst.Close
Set rst = Nothing

End Function

Aivars