Establish connection to MS Access in a Console Application using OleDb Provider

This article seems to be old but I still like the way it’s presented. I did not have the time to publish it in time because I do a lot of things for the past few weeks. Hope it helps you get started with console applications. Happy coding!

— Actual post date: 5/19/2008
Yesterday morning my Einstein ego started to think, “what if I’ll connect to an access db in a console application? And read those data in it?”. Well, I decided to write a simple project since I have much time before going to work. I know it’s not kind of difficult but some other developers I met want to do such thing. So this article might be a reference or something to remember.

Here, I created a simple project written in VB.NET and C#.NET. Normally, I’ll post sample in C# and re-write it in VB for other geeks who might like to write it in VB.

First, I created the access db and define the fields Id and FullName. Id is set to autonumber and FullName to Text and save the table as MyTable. It would look like this in design view.

Next, fire-up Visual Studio 2005 or Visual Studio 2005 Express Editions (depends on your preferred language) and create a new project and select Windows Console application.

In the application I added a reference of System.Configuration

in the References folder in our application since will use the ConfigurationManager.ConnectionString method to get our connection string. How to do that? Well let’s make it fast, right-click References folder, select Add Reference and from that a dialog box appears titled “Add reference”. Slide to the bottom and select System.Configuration. Bytheway, I stored the connectionstring in the app.config file. I think it’s pretty neat and easy to add/edit/delete our app settings in that way. Next to that, will add a app.config file to store our ConnectionString in it.



Open the app.config file. As you can see there are few nodes and elements in a config file of a console application. Will be the one to specify what we need. In our sample we can see the <configuration></configuration> element. Now under the element will add the following lines:

  <connectionStrings>
    <add name="AccessConStr" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=SimpleDBForConsoleApp.mdb;User Id=admin;Password=;" />
  </connectionStrings>

More of the code, we create a method with a private access/privileged and with a Boolean returned type.


        private bool connect_db()
        {
            bool returnval = false;

            if (ConnectionProvider == null)
                ConnectionProvider = new OleDbConnection(ConnectionString);

            if (ConnectionProvider.State == System.Data.ConnectionState.Closed)
            {
                returnval = true;
                ConnectionProvider.Open();
            }

            return returnval;
        }

there’s nothing much in there, we just check if ConnectionProvider is null and build a new OleDbConnection with a connection string provided from the app.config file and then open the connection.

And in our Main() method we invoke the connect_db() method, insert data into the database, query it and print it in console.


                ms.connect_db();

                OleDbCommand cmd = null;
                OleDbDataReader rds;

                // first will try to isnert records
                cmd = new OleDbCommand("insert into MyTable (fullname) values ('my name is cliper');", ms.ConnectionProvider);
                cmd.ExecuteNonQuery();

                // and will query it if it exist…
                cmd = new OleDbCommand("select id, fullname from MyTable;", ms.ConnectionProvider);
                rds = cmd.ExecuteReader();

                while (rds.Read())
                    Console.WriteLine("[Id] \t [Test Name]\n {0} \t {1}", rds["id"], rds["fullname"]);

                rds.Close();
                ms.ConnectionProvider.Close();

Thats it! We’re done. Perhaps you might think what we already know within this article. We know how to implement a simple console application with ADO.NET and of course use the OleDb data provider for .NET.

There’s nothing much to it. I just want to share this simple project. Perhaps somebody might need it for their console projects.

You can download the source-code below:

MSAccessToConsoleApp_CSharp.zip (39.75 kb)

MSAccessToConsoleApp_VB.zip (13.45 kb)

Leave a Reply

Close Menu