Excel File - Microsoft Access

[reubenfoo]

Hi all,

i would want to be able to import data from an excel file into a MS access
database.. Pls check my codes to see what is wrong.

1. I've created a Primary key - APCITID increment of 1 in MS Access table
2. The table name is called "APCITI"
3. The Message Box Shows "The Import is Complete!" however no data goes in
the Access Database.
4. Overall, the coding doesn't show any error but nothing is imported into
access.

THE CODING IS:

Private Sub startBtn_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles startBtn.Click

If TextBox1.Text = "" Then
Me.Close()
End If
Dim _filename As String = TextBox1.Text
Dim _conn As String

_conn = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
_filename & ";" & "Extended Properties=Excel 8.0;"
Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim _command As OleDbCommand = New OleDbCommand()

_command.Connection = _connection
_command.CommandText = "SELECT * FROM [Sheet1$]"
da.SelectCommand = _command
Try
da.Fill(ds1, "sheet1")

MessageBox.Show("The import is complete!", "Import File",
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch e1 As Exception
MessageBox.Show("Import Failed!", "Import File",
MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try
Dim daA As SqlDataAdapter
Dim conn As SqlConnection
Dim cb As SqlCommandBuilder

conn = New SqlConnection("Data Source=Servername;Initial
Catalog=mydb;Integrated Security=True")
Dim sel As String = "SELECT * FROM APCITID"
daA = New SqlDataAdapter(sel, conn)
cb = New SqlCommandBuilder(daA)
daA.MissingSchemaAction = MissingSchemaAction.AddWithKey
da.Fill(ds2, "APCITID")

For Each dr As DataRow In ds1.Tables(0).Rows
Dim expression As String
expression = "PMCO =" + CType(dr.Item(0), Integer).ToString
Dim drs() As DataRow = ds2.Tables(0).Select(expression)
If (drs.Length = 1) Then
For i As Integer = 1 To ds2.Tables(0).Columns.Count - 1
drs(0).Item(i) = dr.Item(i)
Next
Else
Dim drnew As DataRow = ds2.Tables(0).NewRow
For i As Integer = 0 To ds2.Tables(0).Columns.Count - 1
drnew.Item(i) = dr.Item(i)
Next
ds2.Tables(0).Rows.Add(drnew)
End If
Next
Form2.Show()
End Sub



reubenfoo
email: (e-mail address removed)