|
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.
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 username 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 username and password. If you set
up a simple database password then leave the username 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
a directory in program files 'My.Computer.FileSystem.CurrentDirectory' will always
resolve to WINNT\System32. 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..
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
|