none
How do I connect to an Access 2007 (accdb) file in Visual Studio 2012? RRS feed

  • Question

  • I've tried to do it in many different ways, last error was: OleDbException was unhandled: Unrecognized database format 'c:\...
    • Moved by Ego Jiang Monday, April 8, 2013 9:31 AM
    Friday, April 5, 2013 11:32 PM

Answers

  • OleDBConnection is the standard class to connect to Access Databases. You can always use the wizard (Tools > Connect to Database... > Microsoft Access Database File > Continue > Browse for the file > Enter username and password (if any) > Test Connection > Ok).

    But if you prefer doing it with code, here is an example:

    // OleDbSample.cs
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Xml.Serialization;
    
    public class MainClass {
        public static void Main ()
        {
                // Set Access connection and select strings.
                // The path to BugTypes.MDB must be changed if you build 
                // the sample from the command line:
    #if USINGPROJECTSYSTEM
                string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\BugTypes.MDB";
    #else
                string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BugTypes.MDB";
    #endif
                string strAccessSelect = "SELECT * FROM Categories";
     
                // Create the dataset and add the Categories table to it:
                DataSet myDataSet = new DataSet();
                OleDbConnection myAccessConn = null;
                try
                {
                      myAccessConn = new OleDbConnection(strAccessConn);
                }
                catch(Exception ex)
                {
                      Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                      return;
                }
     
                try
                {
                
                      OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect,myAccessConn);
                      OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
     
                      myAccessConn.Open();
                      myDataAdapter.Fill(myDataSet,"Categories");
     
                }
                catch (Exception ex)
                {
                      Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
                      return;
                }
                finally
                {
                      myAccessConn.Close();
                }
     
                // A dataset can contain multiple tables, so let's get them
                // all into an array:
                DataTableCollection dta = myDataSet.Tables;
                foreach (DataTable dt in dta)
                {
                Console.WriteLine("Found data table {0}", dt.TableName);
                }
              
                // The next two lines show two different ways you can get the
                // count of tables in a dataset:
                Console.WriteLine("{0} tables in data set", myDataSet.Tables.Count);
                Console.WriteLine("{0} tables in data set", dta.Count);
                // The next several lines show how to get information on
                // a specific table by name from the dataset:
                Console.WriteLine("{0} rows in Categories table", myDataSet.Tables["Categories"].Rows.Count);
                // The column info is automatically fetched from the database,
                // so we can read it here:
                Console.WriteLine("{0} columns in Categories table", myDataSet.Tables["Categories"].Columns.Count);
                DataColumnCollection drc = myDataSet.Tables["Categories"].Columns;
                int i = 0;
                foreach (DataColumn dc in drc)
                {
                      // Print the column subscript, then the column's name
                      // and its data type:
                      Console.WriteLine("Column name[{0}] is {1}, of type {2}",i++ , dc.ColumnName, dc.DataType);
                }
                DataRowCollection dra = myDataSet.Tables["Categories"].Rows;
                foreach (DataRow dr in dra)
                {
                      // Print the CategoryID as a subscript, then the CategoryName:
                      Console.WriteLine("CategoryName[{0}] is {1}", dr[0], dr[1]);
                }
          
       }
    }
    

    Disclaimer: I got this code sample from the MSDN library, can't post a link since my account isn't verified yet.
    Saturday, April 6, 2013 7:15 AM
  • One reason you may get this exception comes from an improper connection string, for instance trying to open a MS-Access 2007 database using the connection string in figure 1 while the correct connection string is in figure 2. Figure 1 and 2 cause OleDb to attempt to open Database1.accdb in the same folder as the executable. If the database is in a different folder we need to include the path as shown in figure 3.

    Figure 4 is a simple example for opening a table in a MS-Access 2007 database, load the data into a DataTable which is set as the DataSource of the DataGridView. Note that the primary key is included which would be needed if you want to reference back to the table.

    Figure 1

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.accdb

    Figure 2

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb

    Figure 3

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Database1.accdb

    Figure 4

    Public Class Form1
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            If Not IO.File.Exists(FileName) Then
                Exit Sub
            End If
            Dim dt As New DataTable
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn
                    }
                    cmd.CommandText = "SELECT Identifier, Product, Quantity, Cost, ReviewDate FROM Table1"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                End Using
            End Using
            DataGridView1.DataSource = dt
        End Sub
    End Class

    The following example expands on the above by adding a BindingSource component which provides additional functionality not in the DataTable. Also with this said any time you are displaying data in a DataGridView as per above or below you should access your data once loaded via the underlying data source rather than the rows and columns of the DataGridView. In button 1 below I show how to get information for the current row in the DataGridView with a few lines of assertion (meaning making sure objects have values).

    Public Class Form1
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        WithEvents bsData As New BindingSource
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsData.DataSource IsNot Nothing Then
                If bsData.Current IsNot Nothing Then
                    Dim CurrentRow = CType(bsData.Current, DataRowView).Row
                    MessageBox.Show(CurrentRow.Field(Of String)("Product") & " ID is " & CurrentRow.Field(Of Int32)("Identifier"))
                End If
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            If Not IO.File.Exists(FileName) Then
                Exit Sub
            End If
            Dim dt As New DataTable
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn
                    }
                    cmd.CommandText = "SELECT Identifier, Product, Quantity, Cost, ReviewDate FROM Table1"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    bsData.DataSource = dt
                End Using
            End Using
            DataGridView1.DataSource = bsData
        End Sub
    End Class

    Resources

    http://msdn.microsoft.com/en-us/library/fxsa23t6.aspx

    http://www.connectionstrings.com/

    The next link goes to a sample project I did which goes farther than what I discussed above

    https://skydrive.live.com/redir?resid=A3D5A9A9A28080D1!199&authkey=!ACfINhK_ZbJisHc

    This project shows how to use a singleton pattern (do not get hung up on this now, get the basics down then come back to this example project).


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, April 6, 2013 11:55 AM
  • The error is occurring because you are attempting to use the Jet OLEDB Provider. The ACE OLEDB Provider is required for Access 2007 and higher versions. It is unclear whether you have Microsoft Office or the Access Database Engine installed, but you can download the components below:

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    There are 32-bit and 64-bit versions of this component depending on which platform your app is targeting.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 8, 2013 12:03 PM

All replies

  • OleDBConnection is the standard class to connect to Access Databases. You can always use the wizard (Tools > Connect to Database... > Microsoft Access Database File > Continue > Browse for the file > Enter username and password (if any) > Test Connection > Ok).

    But if you prefer doing it with code, here is an example:

    // OleDbSample.cs
    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Xml.Serialization;
    
    public class MainClass {
        public static void Main ()
        {
                // Set Access connection and select strings.
                // The path to BugTypes.MDB must be changed if you build 
                // the sample from the command line:
    #if USINGPROJECTSYSTEM
                string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\\..\\BugTypes.MDB";
    #else
                string strAccessConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BugTypes.MDB";
    #endif
                string strAccessSelect = "SELECT * FROM Categories";
     
                // Create the dataset and add the Categories table to it:
                DataSet myDataSet = new DataSet();
                OleDbConnection myAccessConn = null;
                try
                {
                      myAccessConn = new OleDbConnection(strAccessConn);
                }
                catch(Exception ex)
                {
                      Console.WriteLine("Error: Failed to create a database connection. \n{0}", ex.Message);
                      return;
                }
     
                try
                {
                
                      OleDbCommand myAccessCommand = new OleDbCommand(strAccessSelect,myAccessConn);
                      OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(myAccessCommand);
     
                      myAccessConn.Open();
                      myDataAdapter.Fill(myDataSet,"Categories");
     
                }
                catch (Exception ex)
                {
                      Console.WriteLine("Error: Failed to retrieve the required data from the DataBase.\n{0}", ex.Message);
                      return;
                }
                finally
                {
                      myAccessConn.Close();
                }
     
                // A dataset can contain multiple tables, so let's get them
                // all into an array:
                DataTableCollection dta = myDataSet.Tables;
                foreach (DataTable dt in dta)
                {
                Console.WriteLine("Found data table {0}", dt.TableName);
                }
              
                // The next two lines show two different ways you can get the
                // count of tables in a dataset:
                Console.WriteLine("{0} tables in data set", myDataSet.Tables.Count);
                Console.WriteLine("{0} tables in data set", dta.Count);
                // The next several lines show how to get information on
                // a specific table by name from the dataset:
                Console.WriteLine("{0} rows in Categories table", myDataSet.Tables["Categories"].Rows.Count);
                // The column info is automatically fetched from the database,
                // so we can read it here:
                Console.WriteLine("{0} columns in Categories table", myDataSet.Tables["Categories"].Columns.Count);
                DataColumnCollection drc = myDataSet.Tables["Categories"].Columns;
                int i = 0;
                foreach (DataColumn dc in drc)
                {
                      // Print the column subscript, then the column's name
                      // and its data type:
                      Console.WriteLine("Column name[{0}] is {1}, of type {2}",i++ , dc.ColumnName, dc.DataType);
                }
                DataRowCollection dra = myDataSet.Tables["Categories"].Rows;
                foreach (DataRow dr in dra)
                {
                      // Print the CategoryID as a subscript, then the CategoryName:
                      Console.WriteLine("CategoryName[{0}] is {1}", dr[0], dr[1]);
                }
          
       }
    }
    

    Disclaimer: I got this code sample from the MSDN library, can't post a link since my account isn't verified yet.
    Saturday, April 6, 2013 7:15 AM
  • One reason you may get this exception comes from an improper connection string, for instance trying to open a MS-Access 2007 database using the connection string in figure 1 while the correct connection string is in figure 2. Figure 1 and 2 cause OleDb to attempt to open Database1.accdb in the same folder as the executable. If the database is in a different folder we need to include the path as shown in figure 3.

    Figure 4 is a simple example for opening a table in a MS-Access 2007 database, load the data into a DataTable which is set as the DataSource of the DataGridView. Note that the primary key is included which would be needed if you want to reference back to the table.

    Figure 1

    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database1.accdb

    Figure 2

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database1.accdb

    Figure 3

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\Database1.accdb

    Figure 4

    Public Class Form1
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            If Not IO.File.Exists(FileName) Then
                Exit Sub
            End If
            Dim dt As New DataTable
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn
                    }
                    cmd.CommandText = "SELECT Identifier, Product, Quantity, Cost, ReviewDate FROM Table1"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                End Using
            End Using
            DataGridView1.DataSource = dt
        End Sub
    End Class

    The following example expands on the above by adding a BindingSource component which provides additional functionality not in the DataTable. Also with this said any time you are displaying data in a DataGridView as per above or below you should access your data once loaded via the underlying data source rather than the rows and columns of the DataGridView. In button 1 below I show how to get information for the current row in the DataGridView with a few lines of assertion (meaning making sure objects have values).

    Public Class Form1
        Private FileName As String = IO.Path.Combine(Application.StartupPath, "Database1.accdb")
        WithEvents bsData As New BindingSource
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            If bsData.DataSource IsNot Nothing Then
                If bsData.Current IsNot Nothing Then
                    Dim CurrentRow = CType(bsData.Current, DataRowView).Row
                    MessageBox.Show(CurrentRow.Field(Of String)("Product") & " ID is " & CurrentRow.Field(Of Int32)("Identifier"))
                End If
            End If
        End Sub
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            If Not IO.File.Exists(FileName) Then
                Exit Sub
            End If
            Dim dt As New DataTable
            Using cn As New OleDb.OleDbConnection With
                {
                    .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName
                }
                Using cmd As New OleDb.OleDbCommand With
                    {
                        .Connection = cn
                    }
                    cmd.CommandText = "SELECT Identifier, Product, Quantity, Cost, ReviewDate FROM Table1"
                    cn.Open()
                    dt.Load(cmd.ExecuteReader)
                    dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                    bsData.DataSource = dt
                End Using
            End Using
            DataGridView1.DataSource = bsData
        End Sub
    End Class

    Resources

    http://msdn.microsoft.com/en-us/library/fxsa23t6.aspx

    http://www.connectionstrings.com/

    The next link goes to a sample project I did which goes farther than what I discussed above

    https://skydrive.live.com/redir?resid=A3D5A9A9A28080D1!199&authkey=!ACfINhK_ZbJisHc

    This project shows how to use a singleton pattern (do not get hung up on this now, get the basics down then come back to this example project).


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Saturday, April 6, 2013 11:55 AM
  • The error is occurring because you are attempting to use the Jet OLEDB Provider. The ACE OLEDB Provider is required for Access 2007 and higher versions. It is unclear whether you have Microsoft Office or the Access Database Engine installed, but you can download the components below:

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    There are 32-bit and 64-bit versions of this component depending on which platform your app is targeting.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, April 8, 2013 12:03 PM