9

How do you programmatically check for MS Access database table, if not exist then create it?

LEMUEL ADANE
  • 8,336
  • 16
  • 58
  • 72

5 Answers5

13

You could iterate though the table names to check for a specific table. See the below code to get the table names.

        string connectionstring = "Your connection string";
        string[] restrictionValues = new string[4]{null,null,null,"TABLE"};
        OleDbConnection oleDbCon = new OleDbConnection(connectionString);
        List<string> tableNames = new List<string>();

        try
        {
            oleDbCon.Open();
            DataTable schemaInformation = oleDbCon.GetSchema("Tables", restrictionValues);

            foreach (DataRow row in schemaInformation.Rows)
            {
               tableNames.Add(row.ItemArray[2].ToString());
            }
        }
        finally
        {
            oleDbCon.Close();
        }
Community
  • 1
  • 1
Stephen Dryden
  • 561
  • 4
  • 12
  • +1, more elegant than just catching an error. Please remove the `catch { throw; }`, though: That's a NOOP. – Heinzi Jan 24 '13 at 14:31
9

To check if a table exists you can extend DbConnection like this:

public static class DbConnectionExtensions
{
    public static bool TableExists(this DbConnection conn, string table)
    {
        conn.open();
        var exists = conn.GetSchema("Tables", new string[4] { null, null, table, "TABLE" }).Rows.Count > 0;
        conn.close();
        return exists;
    }
}

Then you can call TableExists in any derived class like OleDbConnection, SQLiteConnection or SqlConnection.

hon2a
  • 7,006
  • 5
  • 41
  • 55
csname1910
  • 1,195
  • 11
  • 14
8

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.

kkmonlee
  • 379
  • 2
  • 16
Shekhar_Pro
  • 18,056
  • 9
  • 55
  • 79
2

an easy way to do this is 

public bool CheckTableExistance(string TableName)
    {
        // Variable to return that defines if the table exists or not.
        bool TableExists = false;

        // Try the database logic
        try
        {
            // Make the Database Connection
            ConnectAt();

            // Get the datatable information
            DataTable dt = _cnn.GetSchema("Tables");

            // Loop throw the rows in the datatable
            foreach (DataRow row in dt.Rows)
            {
                // If we have a table name match, make our return true
                // and break the looop
                if (row.ItemArray[2].ToString() == TableName)
                {
                    TableExists = true;
                    break;
                }
            }

            //close database connections!
            Disconnect();
            return TableExists;
        }
        catch (Exception e)
        {
            // Handle your ERRORS!
            return false;
        }
    }
Erwin Draconis
  • 764
  • 8
  • 20
1

For completeness sake, I'll point out that a while back I posted 4 different ways of coding up a TableExists() function within Access. The version that runs a SQL SELECT on MSysObjects would work from outside Access, though in some contexts, you might get a security error (because you're not allowed to access the Jet/ACE system tables).

Community
  • 1
  • 1
David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58