Query for existence of table

[Microsoft Newsgroups]

Hi all,

What sort of SQL query could be used in an Access database to determine the
existence of a table? I can do this in SQL server by looking at
sysobjects - but how does one do it in Access? I have an application that
needs to check to see if a table exists and, if not, create it on-the-fly.

Thanks,

-bruce

 

[Todos Menos [MSFT]]

create procedure sphappy
as
if exists (select name from sysobjects where id =
object_id('mytable'))
begin
drop table mytable
end

 

[=?windows-1250?Q?Vladim=EDr_Cvajniga?=]

bruce,
Try anything but "drop table" (see above)...
Vlado

 

[Arvin Meyer [MVP]]

MsysObjects is the system table you are looking for.

For a table named tblMyData, you might try:

Select MSysObjects.Name
From MSysObjects
Where Name = "tblMyData" And Type = 1;

CREATE TABLE is the same syntax in Access as SQL-Server, but you don't use
DROP TABLE or you'll actually delete all the data as well as the definition.