Export data from Excel to Access MDB

[mipakteh]

Hi All,

I want to export data in Excel columns to Access MDB database.

I got runtime error.



My excel sheet look like bellow.



Imports ADOX
Imports System.Data.OleDb
Imports ADOX.DataTypeEnum

Public Class Form1


Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'cleanup old databases
Dim catNewDB As New ADOX.Catalog
Dim tblNew As New ADOX.Table

Dim fi As New IO.FileInfo("C:\Users\user\Documents\DB.mdb")
If fi.Exists Then
If MessageBox.Show("Delete?", "Existing File db.mdb", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Users\user\Documents\DB.mdb")

With tblNew
.Name = "Result_D"
' Create fields and append them to the
' Columns collection of the new Table object.
With .Columns
.Append("No_ID", DataTypeEnum.adInteger)
.Append("A", DataTypeEnum.adVarWChar)
.Append("B", DataTypeEnum.adVarWChar)
.Append("C", DataTypeEnum.adVarWChar)
.Append("D", DataTypeEnum.adLongVarWChar)
End With

End With

catNewDB.Tables.Append(tblNew)
catNewDB = Nothing


End Sub

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

' initialize the connect string
Dim _filename As String = "C:\Users\User\Desktop\Book1.xlsx"

Dim _conn As String
_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1"";"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)
'Use OledbCommand object to select all the data from sheet1 and execute an ExecuteNonQuery to import data into Book.mdb.
Dim _command As OleDbCommand = New OleDbCommand()

_command.Connection = _connection

Try

_command.CommandText = "SELECT * INTO [MS Access;Database= C:\Users\user\Documents\DB.mdb].[Result_Daun] FROM [Sheet1$]"
_connection.Open()
_command.ExecuteNonQuery()
_connection.Close()
MessageBox.Show("The import is complete!")


Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!" & Environment.NewLine & "Error Message:" & Environment.NewLine & e1.Message)
End Try

End Sub
Maybe someone help to fix this code.

Continue reading...