Export (0) Print
Expand All

Creating Connections to Access Databases

When connecting to an Access database, use the Microsoft Jet 4.0 OLE DB Provider.

Security Note   Storing connection-string details (such as the server name, user name, and password) can have implications for the security of your application. Using Windows Integrated Security is a more secure way to control access to a database. For more information, see Database Security.

There are two ways to connect to an Access database:

  • Visually, with design-time tools.
  • Programmatically.

Connecting to an Access Database in Server Explorer

Establishing a connection to an Access database in Server Explorer allows you to easily create OleDbConnection, OleDbDataAdapter, and OleDbCommand objects by dragging data objects from Server Explorer onto forms or designers.

To create a connection in Server Explorer

  1. In Server Explorer, right-click Data Connections and click Add Connection.
  2. In the Data Link Properties dialog box, click the Provider tab.
  3. Click the Microsoft Jet 4.0 OLE DB Provider, then click Next.
  4. Click the ellipsis (...) button to browse to the Microsoft Access (.mdb) file you want to access.
  5. Type the User ID to use for authentication when you log on to the data source.
  6. Type the Password to use for authentication when you log on to the data source.
  7. Click OK.

Connecting to an Access Database from Within Your Application

Creating connections to data sources from within your application can be accomplished visually using Visual Studio .NET design-time tools (including Server Explorer and the Toolbox), or programmatically by creating data connection objects in code.

Connecting to an Access Database Visually

Visual Studio supports creating data objects by dragging items from Server Explorer or the Data tab of the Toolbox onto your form or designer.

To create a connection from Server Explorer

  1. Create a Data Connection in Server Explorer to your Access database. For more information, see the above section titled "Connecting to an Access database in Server Explorer."
  2. Drag the connection from Server Explorer onto your form or designer.

    An OleDbConnection object appears in the component tray.

  3. Advance to the section "Next Steps" later in this topic.

To create a connection from the Data tab of the Toolbox

  1. Drag an OleDbConnection object onto your form or designer.

    An unconfigured OleDbConnection object appears in the component tray.

  2. In the Properties window, click the ConnectionString property.
  3. Do one of the following:
    • Click an existing connection in the drop-down list.

      - or -

    • Click New Connection to open the Data Link Properties Dialog Box and configure a new connection.
  4. Advance to the section "Next Steps" later in this topic.

Connecting to an Access Database Programmatically

You can create a OleDbConnection object directly in code.

To programmatically create a connection between your application and an Access database

  • The following code creates an OleDbConnection object, sets the OleDbConnection.ConnectionString property, and opens the connection.
    ' Visual Basic
    Public Sub ConnectToAccess()
        Dim conn As New System.Data.OleDb.OleDbConnection()
        ' TODO: Modify the connection string and include any
        ' additional required properties for your database.
        conn.ConnectionString = & _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & _
        "C:\Documents and Settings\username\My Documents\dbFile.mdb"
        Try
           conn.Open()
           ' Insert code to process data.
        Catch ex As Exception
           MessageBox.Show("Failed to connect to data source")
        Finally
           conn.Close()
        End Try
    End Sub
    
    // C#
    public void ConnectToAccess()
    {
        System.Data.OleDb.OleDbConnection conn = new 
            System.Data.OleDb.OleDbConnection();
        // TODO: Modify the connection string and include any
        // additional required properties for your database.
        conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data source= C:\Documents and Settings\username\" +
            @"My Documents\AccessFile.mdb";
        try
        {
            conn.Open();
            // Insert code to process data.
        }
            catch (Exception ex)
        {
            MessageBox.Show("Failed to connect to data source");
        }
        finally
        {
            conn.Close();
        }
    }
    

Next Steps

Now that you have established a connection between your application and the Access database, you can create objects to fetch and update data. The following table provides links to some relevant topics.

ToSee
Fetch read-only dataRetrieving Data Using the DataReader
Fill a dataset with dataADO.NET Datasets
Execute SQL statements directlyPerforming Database Operations Directly

See Also

Connecting to Data Sources with ADO.NET | Adding New Data Connections in Server Explorer | Databases in Server Explorer | Visual Database Tools | Data Link Properties Dialog Box | OleDbConnection Class

Show:
© 2014 Microsoft