How to upload excel data to ms access using VB.net button control

Ayyappan A
Ayyappan A used Ask the Experts™
on
Hi,
 I want to upload my excel data file to access using button event. Below is my code.

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click

        Dim Conn As OleDbConnection

        Dim Cmd As OleDbCommand

        Dim Filename As String = "D:\Ayyappan\claimDump.xlsx"

 

        'Try

        Dim StrFileName As String = String.Empty

        Dim XLA As New OleDbDataAdapter

        Dim XLTable As New DataTable

        Dim StrSelect = "SELECT * FROM [{0}]"

 

        OpenFileDialog1.FileName = ""

        OpenFileDialog1.Filter = "Excel |*.xlsx|All files (*.*)|*.*"

        If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then

            Exit Sub

        End If

        StrFileName = OpenFileDialog1.FileName

        Conn = DBConn()

 

        Cmd = New OleDbCommand("INSERT INTO claimDump([a],[b],[c],[d],[e],[f])  SELECT * INTO [Excel 12.0 xml;DATABASE=" & StrFileName & ";Extended Properties=Excel 8.0;]", Conn)

        Cmd.ExecuteNonQuery()

        ' Catch ex As Exception

        ' MsgBox("Some error")

 

        'End Try

 

    End Sub

While running the code I got "IErrorInfo.GetDescription failed with E_FAIL(0x80004005" this error.

 
Am checking my excel sheet the header name is same as per the insert statement like a,b ,c etc..

Can anyone help me on that.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Does the query work when you manually execute it in the Access database?

Author

Commented:
Yeah.. it works fine. Even I pass the value manually in that insert into statement it will  works fine.

Author

Commented:
Hi I found the error. It's my mistake thanks to everyone

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial