Uploading Excel File into MS Access using vb.net

Question

I am trying to import an Excel file into an Access DB via vb.net the idea is the customer can export the data into excel, modify it, add, delete them importing it back.

The data exported has exactly the same format than the table to import to. I am using the code below:

Try
    Dim strFileName As String = String.Empty
    Dim XLda As New OleDbDataAdapter
    Dim ExcelTables As New DataTable
    Dim StrSelect = "SELECT * FROM [{0}]"

    OpenFileDialog1.FileName = ""
    OpenFileDialog1.InitialDirectory = mdlGlobalStuff.sMasterDataPath
    OpenFileDialog1.Filter = "Excel|*.xls|All files (*.*)|*.*"
    If OpenFileDialog1.ShowDialog() <> Windows.Forms.DialogResult.OK Then
        Exit Sub
    End If
    strFileName = OpenFileDialog1.FileName
    Dim MyXLConnection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileName & ";Extended Properties=Excel 8.0;")

    Using MyXLConnection
        Using cmd As New OleDbCommand
            cmd.Connection = MyXLConnection
            cmd.CommandText = "INSERT INTO [MS Access;Database=InvoicingToolDB.accdb].[tbl_Bases] SELECT * FROM [Sheet1$]"
            If MyXLConnection.State = ConnectionState.Open Then
                MyXLConnection.Close()
            End If
            MyXLConnection.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using

Catch ex As Exception
    MsgBox("ImportLinkLabel_LinkClicked: Importing Base data" & vbCrLf & ErrorToString())
End Try

I always have an error message saying:

Unrecognized database format 'c:\--path to db--\InvoicingToolDB.accdb'

The path is correct and I don't understand why the format wouldn't be recognized.

Answer 1

Ok I have found the issue. The OLEDB Provider version was not the right one (4.0 is not reading Access .accdb format but old .mdb format only) Replacing:

Dim MyXLConnection As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strFileName & ";Extended Properties=Excel 8.0;")

With:

Dim MyXLConnection As New leDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & ";Extended Properties=Excel 8.0;")

Works perfectly.