Populate DataGridView with Data from an Access Database

Recently I was involved in a thread at a Q&A forum that discussed how to populate a DataGridView.

On its face this seems to be an easy operation, i.e. acquire the data from the Database and in the next step add the data to the DataGridView Control. As with most things; if we have the necessary knowledge then it’s easy to achieve the task and if not it would be difficult.

The key aspect in this context is to be aware of that a DataSet can include one or more tables. Therefore we need to explicit state in the code which table to display.

In the following example we first acquire the wanted data from the Access database. In the next step we add the acquired data to a table in the created DataSet object and use it as the data source to the DataGridView. Finally we tell the DataGridView which table to display by setting its DataMember property:

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

Const Con As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Users\Dennis Wallentin\Documents\NorthWind 2010.accdb;" & _
"Persist Security Info=False;"

Const SQLExpression As String = "SELECT * FROM Customers;"

Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) _
Handles Button1.Click

Dim cn As New OleDbConnection(Con)
cn.Open()
Dim da As New OleDbDataAdapter(SQLExpression, cn)

Dim ds As New DataSet
da.Fill(ds, "AccessData")
cn.Close()

With Me.DataGridView1
.DataSource = ds
'Here we control which table the DataGridView should display.
.DataMember = "AccessData"
End With

ds = Nothing
da.Dispose()
cn = Nothing

End Sub
End Class

When we execute the above code we get the following outcome:

As we all know by now, the Office 2010 comes also in an x64 version. That force us to consider if the above solution also will work with Excel 2010 x64 or not.

With the release of Office 2010 we also got the x64 ACE OLEDB Provider. If You cannot find it on Your x64 set up then You can download it from the Microsoft Access Database Engine 2010 Redistributable x64.

Kind regards,
Dennis

A New Center for Excel Developer on MSDN

I thought that I should notify readers of my blog that Microsoft recently has launched a new Excel Developer Center on MSDN.

The new Center is available here: Excel Developer Center

I find it really nice to see, for instance, VBA and VSTO on the same page. Don’t miss the presentation of Excel Developer RoadMap.

I guess we all need to update the start pages in our Web browsers 🙂

Kind regards,
Dennis