excel to msaccess

[hiten_9]

how can i retrieve records from excel to ms access database
for example i want to insert from 20th row to 30th row of excel sheet to ms access database. i have the code :---

Dim cmdbldr As New OleDbCommandBuilder(oda)
Dim dro2 As DataRow
'Dim dro As DataRow
Dim dro3 As DataRow
Dim con2 As OleDbConnection
' Dim srr As OleDbDataReader
Dim cmd2 As OleDbCommand
Dim oda1 As System.Data.OleDb.OleDbDataAdapter
Dim ds1 As System.Data.DataSet

Dim k As Integer
Dim l As Integer



Dim WorkSheet As String
WorkSheet = "sheet1"
If Not WorkSheet.EndsWith("$") Then
WorkSheet &= "$"
End If
con2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox3.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")

'con2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & TextBox3.Text & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""")
cmd2 = New OleDbCommand("SELECT * FROM [" & WorkSheet & "] ", con2) '[" & "hktest5.xls" & "]"
oda1 = New OleDbDataAdapter(cmd2)
oda1.TableMappings.Add("Table", "Attendence")
' ds1 = New DataSet
ds1 = New DataSet
oda1.Fill(ds1)
con2.Open()












'srr = cmd2.ExecuteReader()
'While srr.Read
'Dim itr As Object() = {" ", " ", " ", " ", " ", " "}
Dim i As Integer
For i = TextBox5.Text To (ds1.Tables(0).Rows.Count - 1)

dro3 = ds1.Tables(0).Rows(i)

dro2 = ds.Tables(0).NewRow()

dro2("ProductID") = ds1.Tables(0).Columns(0)
dro2("URL") = ds1.Tables(0).Columns(1)
dro2("Title") = ds1.Tables(0).Columns(2)
dro2("Description") = ds1.Tables(0).Columns(3)
dro2("keyword") = ds1.Tables(0).Columns(4)
dro2("conttent") = ds1.Tables(0).Columns(5)
ds.Tables(0).Rows.Add(dro2)
oda.Update(ds)

Next
' End While

'Next
'End If

For k = 0 To 100
For l = 0 To 99999
ProgressBar3.Value = k
Next
Next

MessageBox.Show("Data Inserted")

 

[lingsn]

I think the con2.Open() should called before you ask the adapter to fill the dataset. It will raise the exception if you haven't open the connection.

After you fill-up the dataset, you can close the connection and open a new connection to connect to the ms access database.

In your for loop, put your insert statement to the DbCommand, and call ExecuteNonQuery method to insert it to the database.