• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• A Better Way to Test For Tables (great for begginners well commented)

A Better Way to Test For Tables (great for begginners well commented)

This is the better way to find out whether your particular table, any table, exists in your database. Sequential is NOT the way. Check this out and let me know if you have any questions.

Please give me a vote if you like this code :)

Rate A Better Way to Test For Tables (great for begginners well commented)

• 1
• 2
• 3
• 4
• 5

(25(25 Vote))

IF TableExists(strTableName) then MsgBox strTableName & " found." else MsgBox strTableName & " not found."
Private Function TableExists(TableName) As Boolean
'I ususally use a global Database object, however' you can just as easily pass it into the function if you'd prefer
Dim strTableName$ 'string
On Error GoTo NotFound
If TableName <> "" Then strTableName = dbMyDatabase.TableDefs(strTableName).Name
'If the table exists, the string will be filled, 'otherwise it will err out and TableExists will remain false.
TableExists = True
NotFound:
End Function
'I have VERY often seen people use the standard routine of
'going through EACH and EVERY table comparing each one till
'they get the the end, as in
 
 'For Each MyTable in DB.TableDefs
 ' if MyTable.Name = strNameImLookingFor then
 'TableExists = true
 'Exit For
 'end if
 'Next
'This is NOT the way to do this. You will unecesesarily use up
'yours as well as your users' very valuable time.
'Use this function. Make it private. When you pass the name
'of the table you need to check for into this routine, the
'recordset will either retrieve it, with a quickness, or it
'will error out, which is even quicker. If you have this in
'a private function, the erroring out will equate to it
'returning a negative response for the table search.
'I might add that this technique works superbly with field searches
'as well (such as Serial No, credit cards, socials, phone numbers, etc).
'And, there you have it.