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

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.