Working with an Access DB in VB.Net

Part 1 - Creating a connection

Working with an access database manually (e.g. not using the wizards in Visual Studio) is actually one of the easiest 'hard' things you can do in VB.NET. I say 'easiest hard thing' because most beginners think working with a database in VB.NET is going to be quite difficult when in reality its very simple. Access DB's are great, we even used to use a small Access DB to store configuration information in ArchiverFS (details of which can be found here). The first thing you need to do is make yourself an access database. Alternately you can download the test one here. The next thing you need to do is create some basic building blocks .. first is the connection.. Creating the connection We are going to oversimplify this greatly, just to show you the basics of how it works..

Dim con as new OledD.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0.; Data Source=C:\db1.mdb")

So, all we are doing here is simply creating a new connection object, in this case 'con', and then feeding it a connection string as an overload at creation. The connection string tells the connection object what connection provider to use and where the database is. We are assuming your using the test database and have simply saved it to the root of your C:\ drive. The connection string can hold a lot more information than just this though. It can hold a user name and or password, as well as various other options.

The easiest way to generate a more advanced connection string in Visual studio 2005 is to add a new data source to your project, and then when it is all set up simply copy the connection string.
1) Go to 'Data' on the drop down menu and then 'New Data source'
2) Choose 'Database' and click next
3) Click the 'New Connection' button
4) Change the 'Data Source' to 'Microsoft Access Database File (OLE DB)'
5) Click the 'Browse' button and find your database
6) If you have set up security on your Access Database then enter the appropriate user name and password. If you set up a simple database password then leave the user name box blank and just enter a password.
7) Once all your options are set click on 'Ok' to return to the 'Data source configuration wizard window"
8) You can now click on the '+' symbol in the middle of the window to reveal the connection string.

Dynamic Connection strings....

The above example is fine if you know where your database is.. but once a piece of software gets past design stage that all goes out the window! You'll want to distribute your software with an installer and give your end user the choice of where to install it. Typically your Access Database will be distributed with your software installation and will exist in the same directory as your .exe file (or a subdirectory of that directory). This is where the 'My.Computer.Filesystem.CurrentDirectory' object swoops in to save the day. It will resolve to the directory that your running your .exe from. EG. the same directory you access database is in. With that in mind your connection string can then look like this.

Dim con as new OledD.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0.; Data Source=" & My.Computer.Filesystem.CurrentDirectory & "\db1.mdb")

But what if your accessing your database with a windows service? Windows services are interesting because the above method won't work. Even though the .exe file may be located in the folder it wont show as running there. It is all down to how windows runs services. The only thing to do in this case is to create a registry key with your installer that points to the folder containing your database and read that key whenever your service starts. Opening the connection Now that the connection has been created it needs to be opened.. con.Open() That actually opens the database. If there are going to be any connection errors then this is where they will happen.

Part 2 : Creating a dataset and data adapter manually