VSTO & .NET & Excel

November 16, 2010

Populate DataGridView with Data from an Access Database

Filed under: .NET & Excel, Excel, VSTO & Excel — Dennis M Wallentin @ 9:02 pm

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

About these ads

2 Comments »

  1. Excellent code. Works like a dream. Totally compact. Thank You!

    Comment by w. johnson — October 1, 2012 @ 9:50 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 37 other followers

%d bloggers like this: