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

A few tips on how to connect and work with an Access database in MS Visual Studio

By , 25 Jun 2012
Rate this:
Please Sign up or sign in to vote.

Some days ago I had to make software to a client who asked for a local MS Access database as a must. Then I spent a few days researching on the subject, but no matter what, I couldn’t get the database to connect. After a few head bangs I managed to successfully connect the database to my project discovering the whole topic was a lot much easier than you may think. Here I give you a list of the problems I had to address and how I solved them.

  • The first problem was about the connection, after reading a few forums about connection strings, ODBC access, OLE access, making connection files, all of that was useless for me. Then I discovered that I could drag the database to my project and… voila! It was successfully configured.
  • Another problem I had is that I couldn’t make any query because it said that the MS.jet driver was not installed on this PC. Later after reading some forums, I found out that I had to switch my platform target on the project build options from “Any CPU” to “X86” that was because Microsoft hasn’t implemented OLEDB 64 bit drivers yet.
  • Having the possibility to perform queries on the database, I couldn’t make the data to be persistent, that means anytime I compiled the software the database data was lost. That problem is because any time you compile the database on the executable folder was overwritten by the project database that was in the project root folder and that was the database Visual Studio opened to configure. After checking the possible configurations, I discovered that all were wrong for my project, for example:
    1. Copy always: was the default option and overwrites the saved data
    2. Do not copy: it didn’t overwrite saved data but changes on datatables and database weren’t reflected on the local database
    3. Copy if newer: that option also overwrote my data, just anytime I saved data to the database.

    I just wanted and option that allowed me to save data and not overwritten but also with the option to reflect changes I made to the project database. Then I had to make a little hack, and this hack was the motivation for me to make this small article.

    The trick is in development time, connect to the project database not a database located in an output folder. For that hack you have to modify the connection string like this:

    static public void Configure()
    {
        string connectionString = 
           "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\database.mdb;Persist Security Info=True";
        uta.Connection.ConnectionString = connectionString;
        tdta.Connection.ConnectionString = connectionString;
        pta.Connection.ConnectionString = connectionString;
        abta.Connection.ConnectionString = connectionString;
        sta.Connection.ConnectionString = connectionString;
        saleTa.Connection.ConnectionString = connectionString;
        spta.Connection.ConnectionString = connectionString;
        pota.Connection.ConnectionString = connectionString;
        tabPta.Connection.ConnectionString = connectionString;
    }

    And make all your project datatables have it. That function must be called before making any query to the database and has to be called only once.

    Then anytime you want to copy the output project so it works in other PCs, you only have to comment that line and that is all.

  • The last problem I faced was that anytime I added a new field on the Access database I didn’t know how to update it on the datatables and I had to erase the entire database and drag the new one to the project, again losing all the custom queries I had made. The solution was to make right click on the selected datatable and select the configure option.

That’s all for now. I wrote this article in the exact amount of words I wish I have read on the internet before I began to work on this subject. Any feedback is welcome.

My dev blog is www.vasilydev.blogspot.com.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Vasily Tserekh
Software Developer
Canada Canada
born on 86, had my first computer at the age of 8, wrote my first code at the age of 15(pascal), began to study informatic engineering at 2005 and graduated in 2010, now I am currently working on a software development department in my company. Working with extjs mysql and php. I have a dev blog www.vasilydev.blogspot.com. My real pasion is 3D game programming and playing guitar. Ive programmed stuff in C#, python, Delphi, PHP, C++, JS, QT and others...

Comments and Discussions

 
GeneralThoughts PinmemberPIEBALDconsult26-Jun-12 8:21 

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
Web02 | 2.8.140415.2 | Last Updated 25 Jun 2012
Article Copyright 2012 by Vasily Tserekh
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid