Want to determine the most accurate and fastest VBA coding to determine if a table exists?

If you have many tables, fastest way to do this will be to build an external script or function to do this...

1) Lookup all database + tables.

2) Arrange these into a hash.

3) When every you have to check for table existing, check the hash.

I do this will a script db-ls, which produces a list of all databases or db-ls --tables which produces a list of all db.table entries.

This is highly useful when working with 100s of databases, each of which might have 1000s of tables.

General way to consider how to approach this is each database lookup takes roughly the same amount of time.

So if you do a lookup once for all db.table entries, this will likely be much faster than 1000s of individual db.table lookups.

David, could you or someone else please provide a sample database that arranges such in a hash when you have the time?  Currently I have the latest Microsoft Access version and only dealing with 2016.  I can keep this question open for awhile (like till Monday or Tuesday) but that's what I was hoping for.  One reason for this I intend to build a function to identify forms, queries, etc., and any other list that is large.  I never heard of a hash before and using such in VBA.  I post things like this on EE while I'm working on other VBA and don't have time to get into yet because of other deadlines.

I definitely will give you partial credit either way - thank you for your response.

That's really what the DCount() is doing.   It's just a matter of you or Access doing it.   In either case, you are executing an SQL statement, which has overhead to it.

Going through the collection is faster.

Jim.

Jim

Oops, I didn't see that part of the code/question - only saw the part looping through table definitions.

create a loop and call those functions 1000 times passing random strings
compare both...

I guess it does not matter which one you use, since probably you will use this function a couple of times...

if your app is slow, then ficus somewhere else, for example check one of the slow queries and re-write it
or add some indexes to somewhere else
or eliminate some loops and try to do that in one sql dml statement...
you will gain much more than this function

I don't want to use a function that causes an error, Jim.

I don't want to use a function that causes an error

why this function is giving error?
it should return true or false!

Public Function TableExists(tblName As String) As Boolean
    TableExists = DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1
End Function

Select allOpen in new window

I've not tested but I would have thought the function that doesn't involve looping would be the fastest.

Kurt

I think this is the code being referred to.

 On Error Resume Next
 strTableName =  CurrentDB().tabledefs(sTName).Name

  If strTable = "" then
   TableExists  = False
  Else 
   TableExists  = True
  End If

Select allOpen in new window

yes that is correct.

that code looks wrong...
maybe this...

Dim strTableName as string

On Error Resume Next
strTableName =  CurrentDB().tabledefs(sTName).Name

If strTableName = "" then
  TableExists  = False
Else
  TableExists  = True
End If

Select allOpen in new window

or, simply

Dim strTableName as string

On Error Resume Next
strTableName =  CurrentDB().tabledefs(sTName).Name
TableExists = strTableName <> ""

Select allOpen in new window

but the code I posted above is the simplest and should work fine, ie

Public Function TableExists(tblName As String) As Boolean
    TableExists = DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1
End Function

Select allOpen in new window

<<I don't want to use a function that causes an error, Jim. >>

 You'll find that there are a couple of situations where that is the best course of action; attempt an operation and see if it fails.   This is one of them.

Another is where you want to delete a file, but don't know if it exists.   It's a bit silly to take a disk hit to figure out if it's there, only to turn around and hit the disk again to delete it.  So you do:

On Error Resume Next
Kill strMyFile
On Error Goto Error_myErrorHandler

and forget about it.  

With On Error Resume Next, there is no issue.

Jim.

BTW, if you really are trying to cross all the T's and dot the I's, you can do:

Err = 0
On Error Resume Next
Kill strMyFile

If Err<>xx then
   ' Something happened (like no permissions) that I wasn't expecting.
End If

On Error Goto Error_myErrorHandler 

Am I missing something here?

There's a built-in table, MSysObjects, that holds information on all the objects in the database, why not query that to find out the existence, or otherwise, of the object of interest?

But in the case of checking for a table, the way I showed you is the fastest way.

Jim.

<<
Am I missing something here?

There's a built-in table, MSysObjects, that holds information on all the objects in the database, why not query that to find out the existence, or otherwise, of the object of interest? 
>>

 He was looking for the fastest way.

 DCount(), DFindFirst(), opening a recordset, or looping the tabledef's will all take more time.

Jim.

Jim

So are you saying it might be quicker to query the MSysObjects table?

quicker?

probably we are not in a race to find the quickest!
any code that works will do the job
will we call this function millions of time in a loop?
and that's why we are looking for the quickest way?

Why would a function that checks if a table, or other object, exists based on the name of that object be called multiple times in a loop?

Why would a function that checks if a table, or other object, exists based on the name of that object be called multiple times in a loop?

not same object maybe...
think of a table with tablenames inside
and we want to know which one is in current db or not...
so we open table, loop and call that function for each row to find :)

I tested this function

it successfully returns TRUE or FALSE
no error/no exception!

@HainKurt,

<<no error/no exception! >>

  He was talking about my code not yours, and the fact that it relies on generating an error or not to test if the table exists.

  DCount(), Dlookup(), and DFirst() will all be roughly equivalent.   They have the overhead of executing a SQL statement though (Access is doing it for you, so it's faster than what you can do in VBA, but it's still executing a SQL statement).

  Looping the TableDefs() collection can take time depending on the number of tables, but even if hundreds, I suspect it would be a fraction of a second and it would be hard to tell the difference over what I posted.   But it's still faster than anything SQL based as there's no recordset involved and no costing plan needs to be generated.

 Using the code I showed relying on an error is the fastest way.   But as you said, It should have been:

Dim strTableName as string

On Error Resume Next
strTableName =  CurrentDB().tabledefs(sTName).Name

If strTableName = "" then
  TableExists  = False
Else
  TableExists  = True
End If

Select allOpen in new window

 as I used strTable instead of strTableName on the If check when I originally posted.

That aside, that technique is the fastest method.

Jim.

Option Compare Database

Public Function TableExists1(tblName As String) As Boolean
Dim strTableName As String

On Error Resume Next
strTableName = CurrentDb().TableDefs(sTName).Name

If strTableName = "" Then
  TableExists1 = False
Else
  TableExists1 = True
End If
End Function

Public Function TableExists2(tblName As String) As Boolean
    TableExists2 = DCount("[Name]", "MSysObjects", "[Name] = '" & tblName & "'") = 1
End Function

Public Sub test()
Dim PauseTime, Start, Finish, TotalTime
Dim t As Boolean

Start = Timer
For i = 1 To 1000
  t = TableExists1("MyTable" & i)
Next
Finish = Timer
TotalTime = Finish - Start  ' Calculate total time.
MsgBox "TableExists1: " & TotalTime & " seconds"
    
Start = Timer
For i = 1 To 1000
  t = TableExists2("MyTable" & i)
Next
Finish = Timer
TotalTime = Finish - Start  ' Calculate total time.
MsgBox "TableExists2: " & TotalTime & " seconds"

End Sub

Select allOpen in new window

for 1000 runs

1000 run difference is 78 ms, less than 1/10 of a second!
234 ms vs 312 ms
for each run, the difference is 0.078 ms!

Anybody know how do that hash David was initially talking about in MS Access?

Anybody know how do that hash David was initially talking about in MS Access?

why you are trying to the difficult ways, instead of using one line function?
how many times you will call this function?

1 call only? > 0.3 ms (3/1000000 of a second)
1000 at a time? > 312 ms (312/1000 of a second)
1 Million? > 3 second...

I just dont get what are you trying to achieve here!

Jim's method - letting it fail - is, by far, the fastest method if you have a decent count of tables.
Looping is much slower:

Fail: 0.007 milliseconds
Loop: 0.492 milliseconds

Select allOpen in new window

I must admit, however, that I have always used the loop method and will continue to do so.
No one can notice a delay of 0.5ms.

<<Here's my article on the subject: >>

  This is where EE always falls flat on it's face...no one but the author knows if an article exists.   We do a terrible job of leveraging our own content.

Jim.

This is where a question helper would really be useful.

Appreciate everyone's answers especially aikimark's article which includes the Dictionary object- I have a project that I'm using for that.

I may come back for some support after adjusting some code but won't be able to get to it till later in the week.
Thanks again.