Check if access table exists

sql ms-access ms-access-2007
HasanG picture HasanG · Jun 6, 2010 · Viewed 57.4k times · Source

I want to log web site visits' IP, datetime, client and refferer data to access database but I'm planning to log every days log data in separate tables in example logs for 06.06.2010 will be logged in 2010_06_06 named table. When date is changed I'll create a table named 2010_06_07. But the problem is if this table is already created.

Any suggestions how to check if table exists in Access?

Answer

Fionnuala picture Fionnuala · Jun 7, 2010

You can use the hidden system table MSysObjects to check if a table exists:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName'")) Then
    'Table Exists

However, I agree that it is a very bad idea to create a new table every day.

EDIT: I should add that tables have a type 1, 4 or 6 and it is possible for other objects of a different type to have the same name as a table, so it would be better to say:

If Not IsNull(DlookUp("Name","MSysObjects","Name='TableName' And Type In (1,4,6)")) Then
    'Table Exists

However, it is not possible to create a table with the same name as a query, so if you need a look up to test for a name, it may be best to add 5, that is query, to the Type list.

Related questions

A select query selecting a select statement

I don't even know if I am doing this query the right way. There is a Sandwiches table that has some 7 fields and 2 of them are comboboxes (Type and Bread). So I made a query that combines all of the …

Read More
How do I correctly use "Not Equal" in MS Access?

Objective: The intent of this query is to select all of the distinct values in one column that don't exist in a similar column in a different table. Current Query: SELECT DISTINCT Table1.Column1 FROM Table2, Table1 WHERE Table1.Column1 &…

Read More
Select records based on what a field begins with?

I have a database with a table that has identifiers c1, c2, c3..etc.. Instead of writing a query that has a bunch of ORs in it, how can I modify the below query with something that will catch all …

Read More