Data Access Layer For Microsoft Access Databases

**Anilrgowda** · 03-Jan-2007, 10:19 PM

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.

That's it for this installment of the article. In this installment a basic class library (DLL) has been created that can be extended to become a data access layer for Microsoft Access databases. In the next installment the class library will be extended and used for the first time by a Windows Forms application

03-Jan-2007, 10:19 PM	#1 (permalink)
Anilrgowda Administrator Posts: 18,715 Join Date: Jan 2006 Rep Power: 10 IM:	Data Access Layer For Microsoft Access Databases 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. That's it for this installment of the article. In this installment a basic class library (DLL) has been created that can be extended to become a data access layer for Microsoft Access databases. In the next installment the class library will be extended and used for the first time by a Windows Forms application

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)