Questions re your code

[Jim Russell]

Two questions re your function (just for my own education):
1. Why loop through all tables? If TableDefs is a collection, could you not
use
MyDB.TableDefs(strTableName) to see if it exists?
2. Since MyDB is local, is any purpose served by "Set MyDB = Nothing"? Won't
exiting the function have the same effect?

Thanks!

From: "Rick Brandt" <[email protected]>
Subject: Re: Existence of table
Date: Friday, June 27, 2003 7:50 AM

I am in the process of writing a routine that uses CreateTableDef("tblName")
but am unable to figure out how to check if a table already exists - I get
an error message, obviously, if I try to create it a second time.
Laura

Function TableExists(strTableName As String) As Boolean

Dim tbl As TableDef
Dim MyDB As Database

Set MyDB = CurrentDb

For Each tbl In MyDB.TableDefs
If tbl.Name = strTableName Then TableExists = True
Next tbl

Set MyDB = Nothing

End Function

 

[Wayne Morgan]

Somehow this reply didn't get attached to the thread. However, to answer the questions
posed,

1) While you could loop though all the tabledefs and check the Name value of them to see
if you can find a match for the name you are wanting to use, it would probably be quicker
to just try to make the table and trap the error in your error handler if the table
already exists. However, when checking the name, just posing the name of the table you
want doesn't do anything, it has to be compared to something or you have to try to do
something with it to get a result. Just the line, without a comparison (i.e. =) or a
Method (i.e. Append, Delete, Refresh), MyDB.TableDefs(strTableName) will return the error
"Invalid Use of Argument." If the comparison you choose to make is the Name property, you
don't know which table to compare it to, so you have to loop through them until you find a
match.

2) While it should be cleared when you exit the procedure, things tend not to work
perfectly. It is best to explicitly close what you open and set to nothing things you have
set.