Working with an Access DB in VB.Net

Part 2 - Datasets and adapters

Now that we have a connection its time to look at how we actually read records from our access database. There are two ways you can do it, either execute SQL commands directly against the database or use a data adapter to fill a dataset and then manipulate that.

The first method is great if you want to just update or delete items from your database and in my opinion the second is best if you want to display records to users and manipulate them. We'll look at the first later but for now we will cover the dataset and data adapter method.

First off we'll need a dataset. Creating a dataset A dataset is really a 'copy' of one of your tables in your database. It is held in memory and populated with a data adapter. The data adapter is also used to write changes back to the physical database from your dataset. Dim ds As New Data.DataSet That's it.. one shiny new dataset ready to be filled with data, so what are we waiting for!

Creating a data adapter As already mentioned a data adapter is used to fill a dataset with data. You create one as such..

Dim adapter As OleDbDataAdapter = New OleDbDataAdapter("Select * from Customers", con)

Its fairly self explanatory when you read it through.. The slightly confusing bit though is the two overloads that are used. The "Select * from Customers" is a very simple SQL select statement. The adapter will use this when fetching new records from our database. The second overload is the connection that the adapter is to use when executing the select statement, in this case its 'con' which is the connection we created in part1.

You also need to set a special property on the adapter as well. Its the Missing Schema Action property. It tells the adapter what to do when it fills a completely blank table in a dataset. If you don't set this property then you will get various messages like "The table does not have a primary key defined' etc when you try to select rows from it. Simply enter this line to configure the property. adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

Filling the dataset Again this is pretty straight forward, we just use the 'Fill' method of our data adapter to fill the dataset like so.. adapter.Fill(ds,"Customers")

This will create a table called 'Customers' in our dataset (ds) and then fill it with data as defined by the adapters select command. The table you create in your dataset does not have to be called the same as the source table, but its easier to work with when you are working with several tables if you do! As a point of note, a dataset can hold lots of different tables as long as they are all given different names.

Part 3 : Modifying the data in a dataset

Part 1 : Creating a connection