Check the MS Access database table if it does not exist.

Question

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

Answer 1

Just execute the following code, if the table exists, it will return an error, otherwise 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 } 

These error codes are returned if the table already exists - check here .

Answer 2

You can iterate over table names to validate a specific table. See the code below for 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(); } 

Answer 3

To check if a table exists, you can extend the DbConnection as follows:

 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; } } 

You can then call TableExists in any derived class, such as OleDbConnection, SQLiteConnection, or SqlConnection.

Answer 4

For completeness, I will point out that some time ago I published 4 different ways to code the TableExists () function in Access . The version that runs SQL SELECT on MSysObjects will work from outside Access, although a security error may occur in some contexts (because you are not allowed to access the Jet / ACE system tables).

Answer 5

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; } }