By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,640 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,640 IT Pros & Developers. It's quick & easy.

Check to see whether a table exists

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.

Nov 13 '05 #2

P: n/a
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
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.