Click here to Skip to main content
Click here to Skip to main content

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

, 12 Apr 2010
Rate this:
Please Sign up or sign in to vote.
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Joao Tito Livio
Software Developer (Senior)
Portugal Portugal
I'am Developing my career in the IT industry and development, having worked specifically in areas related to system administration, database Administration and development. Developed specific skills in developmental screening/support for technical incidents, network management, Server Administration, software development, training, project management and technical advice.

Comments and Discussions

 
GeneralMy vote of 2 PinmemberAxelM12-Apr-10 21:11 
GeneralSome poor technique PinmvpPIEBALDconsult12-Apr-10 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140821.2 | Last Updated 12 Apr 2010
Article Copyright 2010 by Joao Tito Livio
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid