Click here to Skip to main content
15,896,118 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I try to put all data from my excel file to datagridview and i want it save it to MS access database. In short Excel File -> datagridview VB.NET-> MS access.
Thank you In advance :)


Mark Jake Baltazar
MIS Specialists

What I have tried:

I don't have any codes yet but i already start researching.
Posted
Updated 23-Jan-19 2:46am
Comments
MadMyche 21-Jan-19 22:05pm    
Any particular question you have?
If you break this down into 2 the parts, you may find it easier to query google (eg "Read Excel in DataGridView" and "DataGridView save to MS Access")
Programmerprogrammeranlang25 21-Jan-19 23:01pm    
how can i fill the datagridview in vb.net from microsoft excel and after i upload it, Now how can i transfer the data from my datagridview to microsoft access, i need some sample codes. So i will have an idea. although i have a codes for uploading excel using dataset but my problem is all of the data was not filled.

1 solution

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:
VB.NET
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()

'1.
Using connection AS OleDbConnection = New OleDbConnection(sConStr)
    Dim sql As String = "SELECT * FROM [Sheet1$] WHERE UserName Like @UserName;"
    connection.Open()
    '2.
    Using command As OleDbCommand= New OleDbCommand(sql, connection)
        command.Parameters.AddWithValue("@UserName", "%j_L%")
        '3.
        Dim reader As OleDbDataReader = command.ExecuteReader()
        '4.
        dt.Load(reader)
        reader.Close()
        reader.Dispose()
    End Using
End Using
'5.
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:
SQL
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[^]
 
Share this answer
 
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900