18

Possible Duplicate:
Check if access table exists

I'm new to vba macros. Any idea how to check if a table exists or not? I have searched for previous posts but did not get a clear solution for this.

l-l
  • 3,558
  • 6
  • 32
  • 38
Karthik
  • 3,065
  • 5
  • 26
  • 37
  • While it implicitly is related, since checking if a table exists will require looking at the members of a collection, it's hardly a duplicate. – David-W-Fenton Jul 28 '10 at 19:06

5 Answers5

36

Setting a reference to the Microsoft Access 12.0 Object Library allows us to test if a table exists using DCount.

Public Function ifTableExists(tblName As String) As Boolean

    If DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1 Then

        ifTableExists = True

    End If

End Function
Karthik
  • 3,065
  • 5
  • 26
  • 37
  • The problem with this method is that it will return True for linked tables with a non working back-end. Using DCount would be much slower, but more reliable. – Patrick Honorez Jan 15 '13 at 10:46
  • 2
    Another problem is that it also seems to find forms and reports... ifTableExists("myFormName") returns true – Peter Jul 29 '15 at 21:25
  • Thank you for your answer. I knew about the dCount function, but didn't know how to sent a **variable** (tableName) to it. Thanks. One small problem that can cause bugs: You changed the returned value directly each time. what you should do is `dim b as Boolean b=False If...b=True End If ifTableExists = b End Function` – MJH Nov 29 '15 at 11:02
12

I know the question is already answered, but I find that the existing answers are not valid:
they will return True for linked tables with a non working back-end.
Using DCount can be much slower, but is more reliable. 

Function IsTable(sTblName As String) As Boolean
    'does table exists and work ?
    'note: finding the name in the TableDefs collection is not enough,
    '      since the backend might be invalid or missing

    On Error GoTo hell
    Dim x
    x = DCount("*", sTblName)
    IsTable = True
    Exit Function
hell:
    Debug.Print Now, sTblName, Err.Number, Err.Description
    IsTable = False

End Function
aaronsnoswell
  • 5,652
  • 5
  • 41
  • 65
Patrick Honorez
  • 23,092
  • 8
  • 78
  • 133
10
Exists = IsObject(CurrentDb.TableDefs(tablename))
Tobiasopdenbrouw
  • 12,649
  • 1
  • 19
  • 27
  • 1
    how can i use this? I used like this, Exists = IsObject(CurrentDb.TableDefs("& ConctNmeforDB &")) MsgBox Exists After that, i got run time error as 3265. Item Not Found in this collection. – Karthik Jul 28 '10 at 10:36
  • 1
    Practically speaking, you can't use it by itself. It has to be wrapped in an error handler so that it returns false when it encounters error 3265. See http://stackoverflow.com/questions/2985513/check-if-access-table-exists/2992743#2992743 for 3 different implementations of a TableExists() function. – David-W-Fenton Jul 30 '10 at 18:28
1

Access has some sort of system tables You can read about it a little here you can fire the folowing query to see if it exists ( 1 = it exists, 0 = it doesnt ;))

SELECT Count([MSysObjects].[Name]) AS [Count]
FROM MSysObjects
WHERE (((MSysObjects.Name)="TblObject") AND ((MSysObjects.Type)=1));
Sjuul Janssen
  • 1,714
  • 1
  • 13
  • 27
1

This is not a new question. I addresed it in comments in one SO post, and posted my alternative implementations in another post. The comments in the first post actually elucidate the performance differences between the different implementations.

Basically, which works fastest depends on what database object you use with it.

Community
  • 1
  • 1
David-W-Fenton
  • 22,262
  • 3
  • 41
  • 55