Click here to Skip to main content
14,425,416 members
Rate this:
Please Sign up or sign in to vote.
See more:
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
Posted
Comments
DamithSL 26-Aug-15 4:04am
   
"some problems" will not help, please explain in detail, have you debug?
cgthanes 26-Aug-15 4:10am
   
yes i m debug ready. And also show the message box "The import is complete!". But when i open the access file not any data is exist as in excel file. Have any suggestion..?
F-ES Sitecore 26-Aug-15 5:13am
   
Nowhere in your code are you inserting data into the database so I don't understand why you don't know why this code isn't working. This looks more like a "do my work for me" question which this site is not intended for.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

you need to read from excel and insert into access database using insert sql statement, currently you are creating access file and table but not inserting data. reading from excel file using select sql statement will not insert data to access database. sample code :
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + _filename + ";Extended Properties=Excel 8.0;"
Using conn As New OleDbConnection(connect)
	Using cmd As New OleDbCommand()
		cmd.Connection = conn
		cmd.CommandText = "INSERT INTO [MS Access;Database=" + DBPath + "].[test] SELECT * FROM [Sheet1$]"
		conn.Open()
		cmd.ExecuteNonQuery()
	End Using
End Using}
   
Comments
cgthanes 26-Aug-15 6:12am
   
Thank You Very much...it is working...
DamithSL 26-Aug-15 6:48am
   
you are welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100