MS-Access Interaction

1. Create a MS-Access Database
2. Create/Edit/Delete Tables in it
3. Access it and Add/Edit/Update data in those tables all with VB.NET.

Note:This is not a beginner's tutorial and I'll assume you know all the basics of creating WinFormbased applications using VS.NET. The screen-shots provided are from VS.NET 2005 - but you shouldn't face any difficult even if you're using VS.NET 2003. The functionality is essentially the same.

We will start by creating a blank Windows Application project called MS-AccessTest.

Creating a MS-Access Database

Microsoft didn't provide us with any easy modern method of creating an Access Database. No such classes are provided in the .NET Framework and hence we'll have to fall back onto an old library routine that came along with the older genre of Visual Studio. As for accessing a database and modifying data in it, we can conveniently use the OleDB Wrapper.

For now, right-click on the project name in the Solution Explorer and select Add Reference. This will bring up the Add Reference dialog box. Next select the COM tab and scroll down till you find a library named Microsoft ADO Ext. 2.8 for DLL and Security as shown in the screen-shot. 

Click OK to add the reference to this dll. The library should show up as ADOX in your References in the Solution Explorer, if you're using VS.NET 2003. Else you can spot it under the References tab under Project Properties in VS.NET 2005.

Next, we'll create our own class to encapsulate all database related routines. In the Solution Explorer, right-click on the solution name and select Add > Class. Name this classDatabase.We'll create a function called CreateDatabase( FileName ) that'll take the FileName as a parameter and create a blank database at the given location.

Here's the code for the function - it returns True or False depending on the success/failure in creating the database. We use the Create method which can be found under the ADOX.Catalogclass to create the database. This method takes the standard OleDB connection string (as shown in oConnect) and creates a blank database with the file name passed to it as Data Source.

CODES:

Create/Edit/Delete Tables
Our next job is to define some tables in this database. For this we use the standard OleDB Data Provider.You need to have a little prior knowledge of SQL to understand this - although syntactically this is very simple. We start by defining a process called CreateTable(). We'll create a simple table named accessTest for demonstration purposes. This will have just two columns:

1. ID - which is of the Data Type COUNTER, which basically means an Auto Incrementing Integer field
2. Name - A TEXT field of length 50, which will store the name of a person.

Moreover, we'll set the ID field as the Primary Key.The code for this function follows.
CODES:

Next we've to define some methods to READ data from this table and return to us as a DataSet - which can be easily bound to a DataGrid to display the data directly on screen. Once again - we define a method called FetchData() to facilitate this. We use the standard connectors that we'd used in the above procedure, but along with that we introduce a new object called aDataAdapter - which is used to read the whole table and place the data into the DataSet. 
CODES:

So far we've created some basic routines to create database/table and fetch data. Now it's time to concentrate on the main interface and come up with a way to display the fetched data on the screen.For this we'll utilize a DataGrid control, as it can be easily bound to the fetched data. On the main form, drop a DataGrid. Also add a DataSet to the main form. We're going to link these two controls and whenever we update the dataset with any data from the database, the datagrid will reflect the changes on screen. Likewise, when data is modified on screen, the changes get reflected in the actual database.

Continued in Part II ...