Connecting to MS Access Database in Microsoft Visual Studio 2010
Make a connection to the Microsoft Access database file, that is named mydb.mdb and placed on the drive by the following path:
The application that need connect to database, must be realized as Windows Forms Application.
1. Run MS Visual Studio.
Run the MS Visual Studio. Creating application as Windows Forms Application.
2. Call the master of database connection.
To access the database file should be implemented connection of this file to the application. This is done by calling the command “Add New Data Source …” from the menu “Data” (Fig. 1) or by clicking at the far left of the toolbar of the “Data Source” button.
Fig. 1. Calling the master of database connection
3. Selection of the data source type (DB).
After that, the window of master for the connection to data source will be opened (see fig. 2).
Fig. 2. Selection of connection type from which application will get data
In the window need to select one of four options of connection to the data source.
MS Visual Studio 2010 provides four connection types to data sources:
- Database – database connection and selection of database objects;
- Service – opens a “Add Service Reference” dialog window that allows a create connection to a service that returns data for your application;
- Object – allows you to select objects of our application, which can later be used to create controls that bound to data;
- Share Point – allows you to connect to a SharePoint site and choose the SharePoint sites for your application.
In our case, we choose an element Database and continue by pressing on the Next button.
4. Selection of the model of database connection.
Next step – selection of the model of database connection (fig. 3).
Fig. 3. Selection of the model of database connection
The system offers one of two
The system offers us a choice of one of two options:
- of the data model based on data set (Dataset);
- Data Model Entity, which means that the system can generate a data model from a database that can be Microsoft SQL Server, Microsoftt SQL Server Compact 3.5 or Microsoft SQL Server Database File, or create an empty model as a starting point for the visual design of the conceptual model using toolbar.
In our case we select DataSet model type.
5. Connecting to database.
The next wizard window (Fig. 4) is the choice of a data connection which will be used by application to connect to database.
Fig. 4. Selection of connection to database
To create a new connection must select the button “New Connection …“. The window “Add connection” will be opened. In this window, we need add a new Microsoft Access connection also select the path to database file. In our case the “Data source” field already contains connection type “Microsoft Access Database File (OLE DB)” that is required for us.
Fig. 5. Adding a new connection and selection database file
If you want select another database, then you must use the “Change …” button, which opens window shown in Fig. 6.
Fig. 6. Data source changing
In the Fig. 6. by the Microsoft Visual Studio system will be offered the following types of data sources:
- Microsoft Access Database File – database Microsoft Access;
- Microsoft ODBC Data Source – access to database using ODBC (Open Database Connectivity);
- Microsoft SQL Server;
- Microsoft SQL Server Compact 3.5;
- Microsoft SQL Server Database File;
- Oracle Database – Oracle database.
Press the “Browse…” button and, in opened window, select the path to database file “mydb.mdb“. It is reasonable to place the database file in the same directory that contains module of executive program.
To verify the connection, you can use the button “Test Connection“.
After pressing the OK button the system will generate string “Connection string” (Fig. 7) which further useful to us for program connection to the database.
Fig. 7. String “mydb.mdb” and string “Connection string“
6. Forming configuration file of application.
After selecting the wizard button “Next” the screen appears, which offers add connection string in application configuration file (Fig. 8).
Fig. 8. The proposal to save database connection string in configuration file of application
Do not change anything, leave everything as is (select button Next).
8. Choosing the database objects for using in program.
Last Wizard (Fig. 9) offers a select list of objects (tables, queries, macros, forms, etc.) to be used in the dataset. Normally we select all tables (the database contains three tables: “Order“, “Customer” and “Tariff“).
Fig. 9. Selection of database objects
By pressing a “Finish” button the wizard of connection to database will closed. Now our application is connected to database.
9. What has changed in the program as a result of wizard execution?
If you select a panel Data Source (Fig. 10), you can see how the connected data set named mydbDataSet has a three tables “Order“, “Plan” and “Customer” with the appropriate fields.
Fig. 10. The DataSources panel
You can also notice a changes in the panel Server Explorer (Fig. 11) where appeared database “mydb.mdb“. We can have several databases in application.
Fig. 11. The panel “Server Explorer“
10. Connecting of methods of operating the database.
In order to use the methods that will operate with database MS Access (not just MS Access), you must include namespace System.Data.OleDb.
For this in the main form (Form1.cs) in the Solution Explorer select the code view mode (View Code) from the context menu (Fig. 12) and at the begin of file add the following line:
Fig. 12. Calling of the program code of main form (Form1.cs) in Solution Explorer
General view of the top of the “Form1.cs” file already has the form:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb;
That’s all, the connection to database “mydb.db” was realized. Further steps are coding for manipulating data in the database.