Cannot import the data from Excel to .mdb access file using vb.net

See more:

C#

VB

VB12

Hi Friends,

I m recently doing a program were import the data from Excel to .mdb access file using vb.net. But i have some problem, the data were inside the excel file is cannot import into the access file. can anyone help me? Please your help is required....

Thanks.

Best Regards,
Thanes

 Private DBPath As String
    Private conn As OleDbConnection
    Public Sub New()
        InitializeComponent()
    End Sub

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

DBPath = "C:\Users\Admin\Desktop\test\CA\test.mdb"

        
        If Not File.Exists(DBPath) Then
            Dim cat As New ADOX.Catalog()
            cat.Create(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
            cat = Nothing
            MsgBox("Done create DBPath")
        End If

        conn = New OleDbConnection(Convert.ToString("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=") & DBPath)
        conn.Open()
        MsgBox("Done connected to DBPath")
        Try
            Using cmd As New OleDbCommand("CREATE TABLE [test] ([id] COUNTER PRIMARY KEY, [no] INT, [name] MEMO, [class] INT);", conn)
                cmd.ExecuteNonQuery()
            End Using
            MsgBox("Done create Table and Columm")
        Catch ex As Exception
            If ex IsNot Nothing Then
                ex = Nothing
                MsgBox("Error in creating Table or Collum")
            End If
        End Try

        ' initialize the connect string
        Dim _filename As String = "C:\Users\Admin\Desktop\test\CA\test.xls"
        Dim _conn As String
        _conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & _filename & ";" & "Extended Properties=""Excel 8.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 test.mdb.
        Dim _command As OleDbCommand = New OleDbCommand()
        _command.Connection = _connection

        Try
            _command.CommandText = "SELECT num,name,no 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
End Class