Click here to Skip to main content
14,694,018 members
Please Sign up or sign in to vote.
3.00/5 (2 votes)
See more:
hi friends , I have requirement on MS access Database. importing the excel data to ms access database using vb.net . this is new requirement for me. Please any one help me.....

Dim Access As String = "C:\Users\grva3242\Desktop\guru.mdb"

Dim Excel As String = "C:\Users\grva3242\Desktop\NLRMP\RSR.XLS"
' Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Excel & ";Extended Properties=Excel 8.0;"

Dim connect As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Excel + ";Extended Properties=""Excel 12.0 Xml;HRD=NO"""


Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = "INSERT INTO [MS Access;Database=" & Access & "].[village] SELECT * FROM [Sheet1$]"
conn.Open()
cmd.ExecuteNonQuery()
End Using
End Using


Here village is empty table in guru database.

I got the error is :<b>Could not find file 'C:\Users\grva3242\Desktop\guru.mdb'.</b>
Posted
Updated 12-Sep-16 7:18am
v3
Comments
Devangrathod 28-Feb-15 6:58am
   
This code gives me following error.. pl.pl.pl... Help me....!
"The INSERT INTO statement contains the following unknown field name: 'F1'. Make sure you have typed the name correctly, and try the operation again."

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

     Dim fln As String = "D:\flName.xls"

     Dim conn As String

     conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & fln & ";" & "Extended Properties=Excel 8.0;"

     Dim con As OleDbConnection = New OleDbConnection(conn)

     Dim da As OleDbDataAdapter = New OleDbDataAdapter()

     Dim cmd As OleDbCommand = New OleDbCommand()

     cmd.Connection = con 

     cmd .CommandText = "SELECT * FROM [Sheet1$]"

     da.SelectCommand = cmd 

     Try

         da.Fill(ds1, "sheet1")

         MessageBox.Show("The import is complete!")

         Me.DataGridView1.DataSource = ds1

         Me.DataGridView1.DataMember = "sheet1"

     Catch e1 As Exception

         MessageBox.Show("Import Failed, correct Column name in the sheet!")

     End Try

 End Sub
   
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System
Imports System.Windows.Forms

Public Class Form1
    Private Excel03ConString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"
    Private Excel07ConString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'"

    

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        RadioButton1.Checked = True
    End Sub


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

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        Dim filePath As String = OpenFileDialog1.FileName
        Dim extension As String = Path.GetExtension(filePath)
        Dim header As String = If(RadioButton1.Checked, "YES", "NO")
        Dim conStr As String, sheetName As String

        conStr = String.Empty
        Select Case extension

            Case ".xls"
                'Excel 97-03
                conStr = String.Format(Excel03ConString, filePath, header)
                Exit Select

            Case ".xlsx"
                'Excel 07
                conStr = String.Format(Excel07ConString, filePath, header)
                Exit Select
        End Select

        'Get the name of the First Sheet.
        Using con As New OleDbConnection(conStr)
            Using cmd As New OleDbCommand()
                cmd.Connection = con
                con.Open()
                Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
                sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
                con.Close()
            End Using
        End Using

        'Read Data from the First Sheet.
        Using con As New OleDbConnection(conStr)
            Using cmd As New OleDbCommand()
                Using oda As New OleDbDataAdapter()
                    Dim dt As New DataTable()
                    cmd.CommandText = (Convert.ToString("SELECT * From [") & sheetName) + "]"
                    cmd.Connection = con
                    con.Open()
                    oda.SelectCommand = cmd
                    oda.Fill(dt)
                    con.Close()

                    'Populate DataGridView.
                    DataGridView1.DataSource = dt
                End Using
            End Using
        End Using
    End Sub

    End Class
   
Comments
[no name] 12-Sep-16 14:06pm
   
Wow... all that code 3 years later for a simple "File not found" error?

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