Click here to Skip to main content
12,254,430 members (67,913 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

22.3K views
4 bookmarked
Posted

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

, 12 Apr 2010 CPOL
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.

You may also be interested in...

Comments and Discussions

 
Generali used your "How to Create a Table with Fields in an Access Database (ADOX and JET SQL)" Pin
Member 110645111-Oct-14 22:21
memberMember 110645111-Oct-14 22:21 
GeneralMy vote of 2 Pin
AxelM12-Apr-10 21:11
memberAxelM12-Apr-10 21:11 
GeneralSome poor technique Pin
PIEBALDconsult12-Apr-10 8:35
mvpPIEBALDconsult12-Apr-10 8:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 12 Apr 2010
Article Copyright 2010 by Joao Tito Livio
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid