excel to msaccess

hiten_9

New member
Joined
Nov 29, 2006
Messages
1
Programming Experience
Beginner
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

Well-known member
Joined
Nov 16, 2006
Messages
287
Programming Experience
Beginner
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.
 
Top Bottom