Hasan Gürsoy Hasan Gürsoy - 10 months ago 51
SQL Question

Check if access table exists

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 Source

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.