Check to see whether a table exists

[John M]

Hi,
I want to create a link to another database table. I've succeeded in doing
this, and avoided creating the the link over and over again by putting in
the error statement something to the effect of if it doesn't exist then
create it. (did this be finding the error number). Unfortunately I've moved
on since then and I only know it doesn't exist when the form using it is
opened! I've then gone past me procedure. It would be anyway far neater if
I could first check whether it exits and if not create it.

Thanks

[Allen Browne]

Error handler is acceptable.

You could examine the TableDefs collection to see if the table is there.

You could test:
IsNull(DLookup("Name", "MSysObjects" _
"(Name = ""Table1"") AND (Type IN (1, 4, 6))"))
where "table1" represents the name of your table.
Type 1 is a local table. 6 is a linked table. 4 is ODBC linked.

A better idea might be to leave the table in place and just empty it instead
of creating and deleting it:
dbEngine(0)(0).Execute "DELETE FROM Table1;", dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John M" <jo**@jmawer.demon.co.uk> wrote in message
news:ci******************@news.demon.co.uk...

Hi,
I want to create a link to another database table. I've succeeded in
doing
this, and avoided creating the the link over and over again by putting in
the error statement something to the effect of if it doesn't exist then
create it. (did this be finding the error number). Unfortunately I've
moved
on since then and I only know it doesn't exist when the form using it is
opened! I've then gone past me procedure. It would be anyway far neater
if
I could first check whether it exits and if not create it.

[Jack MacDonald]

I have handled the same situation by enabling "on error resume next",
then deleting the table link, then creating the table link


On Sun, 12 Sep 2004 16:36:14 +0100, "John M" <jo**@jmawer.demon.co.uk>
wrote:

Hi,
I want to create a link to another database table. I've succeeded in doing
this, and avoided creating the the link over and over again by putting in
the error statement something to the effect of if it doesn't exist then
create it. (did this be finding the error number). Unfortunately I've moved
on since then and I only know it doesn't exist when the form using it is
opened! I've then gone past me procedure. It would be anyway far neater if
I could first check whether it exits and if not create it.

Thanks

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security