Access: Create Table If It Does Not Exist

Access: create table if it does not exist

For SQL DDL code the answer is no. ACE/Jet SQL does not have any control-of-flow syntax and a ACE/Jet PROCEDURE can only execute one SQL statement. Yes, that's right: an ACE/Jet PROCEDURE does not support procedural code :(

Check for MS Access database table if not exist create it

Simply execute following code if table will exist it will return error other wise it will create a new one:

try
{
        OleDbConnection myConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + frmMain.strFilePath + "\\ConfigStructure.mdb");
        myConnection.Open();
        OleDbCommand myCommand = new OleDbCommand();
        myCommand.Connection = myConnection;
        myCommand.CommandText = "CREATE TABLE <yourtable name>(<columns>)";
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();
}
catch(OleDbException e)
{  
    if(e.ErrorCode == 3010 || e.ErrorCode == 3012)
    // if error then table exist do processing as required
}

Those error codes are returned if a table already exists - check here for all.

Access query runs on table that does not exist in database

No, the query runs on an aliased table. It's actually just querying INV_MTL_ITEM_LOCATIONS

Using SQL, you can create an alias for a table. This is especially useful when querying the same table twice in one query, but also commonly used to shorten queries.

Your query will probably look something like this:

SELECT something
FROM INV_MTL_ITEM_LOCATIONS AS INV_MTL_ITEM_LOCATIONS_1

Access automatically creates these aliases when using the query builder and if you add the same table more than once. When removing the non-aliased table, the other one stays aliased.

This is entirely normal, and as far as I know, never a problem.

How to create table in access that is only visible in the query tools of a report?

The first thing to check is whether or not the table/query has merely been hidden from the Navigation Pane. To do this, ensure that Show Hidden Objects is enabled within the Navigation Options:

Assuming that you're not referring to a table/query which has been set to be Hidden, the query is likely to be a subquery defined only within the SQL of the query and not saved as a separate query in your database.

For example, if you were to copy the following SQL into the SQL View for a new query, you would see a subquery called MySubQuery shown in the graphical Design View, but not represented by a corresponding saved query in the database.

select * from (select count(*) as MyCount from msysobjects) as MySubQuery

As such, if you needed to determine how your 'hidden' query is defined so that you may reproduce it, you should inspect the SQL for your query.

Check if access table exists

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.

C# & MS Access - SQL for Create Table, Column and add values at the start of the app

Access SQL does not support CREATE TABLE IF NOT EXISTS so we need to check for the existence of the table ourselves:

string tableName = "team";
DataTable dt = myConnection.GetSchema("Tables", new string[] { null, null, tableName });
if (dt.Rows.Count > 0)
{
    Console.WriteLine("Table already exists.");
} 
else
{
    Console.WriteLine("We need to CREATE TABLE");
}

Also, as mentioned in the comments to the question, Access SQL does not support CREATE DATABASE at all, so we need to use ADOX

var myConnection = new OleDbConnection(connectionString);
try
{
    myConnection.Open();
    Console.WriteLine("Database already existed.");
}
catch
{
    var cat = new ADOX.Catalog();
    cat.Create(connectionString);
    Console.WriteLine("Database created.");
    myConnection.Open();
}

---

Related Topics