How to Create a Table with Fields in an Access Database (ADOX and JET SQL)

Introduction

A robust way to create tables in an Access Database is by using the ADOX Catalog. In order to use, you have to add the reference Microsoft ADO Ext. 2.8 for DDL and Security below or above.

Background

In this sample, we will only create and change fields in an existing database, so you can manipulate your Tables. This code only works with an MDB file, if you want to use a ACCDB you have to change the connection string to Access 12 provider "Provider=Microsoft.ACE.OLEDB.12.0" and have also installed the "2007 Office System Driver: Data Connectivity Components".

Using the Code

You have to give the Full Path to the MDB in string myPath:

public bool GenerateLogDatabase(string myPath)
        {   
            CatalogClass cat = new CatalogClass();
            string strSQL;
            string cs;

            try
            {
                cs = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + myPath + ";" +
                     "Jet OLEDB:Engine Type=5";

                strSQL = "CREATE TABLE Issues 
			(mID AUTOINCREMENT, mUser TEXT(100) NOT NULL " +
                         ", mError TEXT(100) NOT NULL, " +
                         "mDescription TEXT(100) NOT NULL, mDate DATETIME NOT NULL)";

                cat.Create(cs);

                using (OleDbConnection cnn = new OleDbConnection(cs))
                {
                    OleDbCommand cmd = new OleDbCommand();
                    try
                    {
                        cmd.CommandText = strSQL;
                        cmd.CommandType = CommandType.Text;
                        cmd.Connection = cnn;
                        cnn.Open();
                        cmd.ExecuteNonQuery();
                        cnn.Close();
                        return true;
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                    finally
                    {
                        cnn.Close();
                        cmd.Dispose();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {   
                Marshal.FinalReleaseComObject(cat);
            }
        }

Points of Interess

• 2007 Office System Driver: Data Connectivity Component
• Fundamental Microsoft Jet SQL for Access 2000

History