You don't need DataGridView-broker to be able to import data from Excel into MS Access database.
You can use
ADO.NET[
^] -
OleDb[
^] to be able to read Excel and Access files.
All you need to do is to:
1. create
OleDbConnection[
^]
2. create
OleDbCommand[
^]
3. execute command to
OleDbDataReader
[
^]
4. load data to
DataTable[
^]
5. set
datagridview's datasource property[
^] to DataTable.
For example:
Dim sFileName As String = "D:\MyUsers.xlsx"
Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName)
Dim dt As DataTable = New DataTable()
Using connection AS OleDbConnection = New OleDbConnection(sConStr)
Dim sql As String = "SELECT * FROM [Sheet1$] WHERE UserName Like @UserName;"
connection.Open()
Using command As OleDbCommand= New OleDbCommand(sql, connection)
command.Parameters.AddWithValue("@UserName", "%j_L%")
Dim reader As OleDbDataReader = command.ExecuteReader()
dt.Load(reader)
reader.Close()
reader.Dispose()
End Using
End Using
Me.DataGridView1.DataSource = dt
Easy?
The method to get data from Excel or Access is the same. The only thing which changes is a connection string for
Microsoft ACE OLEDB 12.0
provider, see:
Access connection strings - ConnectionStrings.com[
^]
Excel connection strings - ConnectionStrings.com[
^]
Please, carefully read notes about using ACE OLEDB provider in x86/x64 architecture.
More:
How to: Bind Data to the Windows Forms DataGridView Control | Microsoft Docs[
^]
[
Update]
You can even use only one connection - to MS Access database - then grab data from MS Excel via using query similar to below one:
INSERT INTO YourTableName (Field1, Field2)
SELECT Field1, Field2
FROM [SheetName$] IN "c:\documents\xldata.xlsx" "EXCEL 12.0;";
See:
IN clause (Microsoft Access SQL) | Microsoft Docs[
^]
INSERT INTO statement (Microsoft Access SQL) | Microsoft Docs[
^]