Part 1  

Source Code:  DalForMSAccessPart1.zip

This multi installment article will provide instructions and source code you can use to create a data access layer (DAL) for Microsoft Access databases. Along the way the article will explain when a DAL may be appropriate and some of the roles ADO.NET and Visual Studio data technologies can play in a DAL.

This first installment provides instructions and a downloadable example that can be used to create a basic class library (DLL) that will be extended in future installments to become a data access layer for Microsoft Access databases.

Instructions for Creating a Class Library for a Data Access Layer

A. Create a new Visual Basic Visual Studio 2005 Windows Forms solution.

B. Add a Visual Studio 2005 Class project to the Visual Studio solution.

1. From the File menu select Add -> New Project...

 

2. The 'Add New Project' window will open.

3. In the 'Project types:' section of the window select "Visual Basic".

4. In the 'Templates:' section of the window select "Class Library".

5. In the 'Name:' text box enter 'DataService'.

6. Click the 'OK' button.

7. A class library project named DataService is added to the solution, 'Class1' is created in the library, and 'Class1' opens in the code editor.

8. Close 'Class1'

9. Delete 'Class1' from the DataService project.

C. Add a component class to the DataService project.

1.  Right-click the 'DataService' project in the 'Solution Explorer' panel and select Add -> New Item...

2.  The 'Add New Item - DataService' window will open.

3.  In the 'Templates' section of the window select 'Component Class'.

4.  In the 'Name:' text box enter 'DAL'.

 

5.  Click the 'OK' button.

6.  A component class named 'DAL' is added to the DataService project and the 'DAL' component designer opens.

7.  Close the 'DAL' component designer.

 

D. Add one or more data sources to the DataService project.

1. Add a data source to the DataService project

a.  Click the DataService project in the Visual Studio solution to make sure it is selected.

b.  From the Visual Studio 'Data'  menu select 'Add New Data Source...'

c.  The 'Data Source Configuration Wizard' window will open.

d.   In the 'Where will the application get its data from?' section of the window click 'Database'.

 

e.   Click the 'Next' button.

f.   The 'Choose Your Data Connection' step window of the wizard will open.

 

g.   Click the 'New Connection' button.

h.   The 'Add Connection' window will open.

 

i.    Make sure the 'Data Source' label reads 'Microsoft Access Database File (OLE DB)'.

(1)    If it doesn't click the 'Change...' button.

(2)    The 'Change Database' window will open.

(3)    Double-click 'Microsoft Access Database' in the 'Data source:' section of the window.

j.    Click the 'Browse...' button.

k.    The 'Select Microsoft Access Database File' dialog will open. Navigate to an Access Database (.mdb) file.

 

l.     Double-click the file.  You are returned to the 'Add Connection' window.

m.   Click the 'Test Connection' button.

n.    If the test is successful, click the 'OK' button. Otherwise, backup and try again.

o.    You will be returned to the 'Choose Your Data Connection' step window of the wizard.

p.    Click the 'Next...' button.

q.    A dialog box will open.  The dialog asks if you want to add a copy of the database to the project.

 

r.     Click the 'Yes' button.

s.     The 'Save Connection String to the Application Configuration File' step window of the wizard opens.

 

t.     Click the 'Next...' button.

u.    The 'Choose Your Database Objects' step window of the wizard opens.

 

v.    In the "Which database objects do you want in your dataset?' section use the tree view control to make the tables in the DataSource you wish to included in the dataset.

w.   Click the 'Finish...' button.

x.    A copy of the Access database you selected and a DataSet are added to the DataService project.

 

y.   Right-click the database and select 'Properties'.

z.    The 'Properties' panel will open. In the Properties panel set the 'Copy to Output Directory' property to 'Copy if newer'. This will ensure that changes are reflected in the database as you program and debug your application.  If you leave the default setting 'Copy always' a fresh copy of the database will be copied to the Bin directory each time you execute the application and you will not see changes in the database made before the current run.

 

 

NOTE: If your application will use more than one Access database, repeat the process of adding a DataSource for each Access database. Additional databases can be added as your application's data needs expand. In the example solution two Access database data sources have been added.

 

Get Dot Net Code is a web site full of free and pay for use Visual Basic source code for Visual Basic programmers.