Table exists function in A2007

[gg80]

I use the function fncTableExists = (TableName = CurrentDb.TableDefs(TableName).Name) successfully in A2000 & 2003. In A2007 I get the message "not in this collection". OS is W7 on 64 bit computer. Can someone help?

[weekend00]

I always got error message when table not exist:
CurrentDb.TableDefs(TableName).Name

[DaveT]

A little longer than your code, but I use this in a code module:
<code>
Public Function IsTableInDefs(Y) As Boolean
Dim db As DAO.Database
Dim zLng As Long
Dim i as Long 'added in later edit
On Error Resume Next
IsTableInDefs = False
If Len(Nz(Y)) = 0 Then
Exit Function
End If
Set db = CurrentDb
With db
zLng = .TableDefs.Count - 1 'tabledefs is base zero
For i = 0 To zLng
If .TableDefs(i).Name = Y Then
IsTableInDefs = True
Set db = Nothing
Exit Function
End If

Next
End With
Set db = Nothing
End Function
</code>

[gg80]

I do code but am a long way from pro. I tried your code by putting in a button that acutuated the expression IsTableInDefs ("table1"), with table1 being existent. I got an error message that i was not defined, so added statemetn dim i as long. OK? It did work. Could you tell me what the "with" statement is about. It loops through it about 6 times even thought there is only one table. Thanks very much for your time..

[DaveT]

Yes, good get, you are correct the procedure (to stand on its own) needs Dim i as L ong.

As a matter of practice, I usually declare i, j, and k as public in a standard module such as: Public i As Long, j As Long, k As Long

With/End With is a short cut to refer to objects. Using With is equivalent to:
...
Set db = CurrentDb

zLng = db.TableDefs.Count - 1 'tabledefs is base zero
For i = 0 To zLng
If db.TableDefs(i).Name = Y Then
...

Even though you have one table, the routine is stepping through all tables to include the hidden system tables such as MSysObjects.

If you want to see these in 2007, use Access Options, Current Database, Navigation Options, check Show System Objects, then OK.

Since Access uses these MSys tables internally, best to leave them hidden (and alone).

[weekend00]

with statement means in the following codes, all variables/objects which are not defined should be a member of the with object. In above case, .tabledefs is an object of db (with db). if you don't use with db, you need to specify db.tabledefs every time.

the code loops through it about 6 times even thought there is only one table, because hidden system tables are also scaned.

Late post

[gg80]

Thanks much-I have a lot to learn about code.

[gg80]

Thanks for the help and for explaining things, much appreciated.

[gg80]

I am not sure that I specifically thanked you. Your code works and I will use it. Not sure why my old shorter code works in A2000 2003 but not here. Thanks much.