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:
- Copy always: was the default option and overwrites the saved data
- Do not copy: it didn’t overwrite saved data but changes on datatables and database weren’t reflected on the local database
- 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
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.
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...