Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

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

0.00/5 (No votes)
12 Apr 2010 1  
Creating a simpleTable with fields in an Access Database using 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

History

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here