ODBC DataSet to Update Ole Access Table

microsoft.public.dotnet.framework.adonet

31 Mar 2006 11:01 PM

rickn

I have a dataset that shows data from an ODBC connection and want to
insert that data into an access data base. I'm able to create the
record lock on the access table, but am unable to get any data inserted
from the dataset to the access table. Below is the coding that I have
so far. I've struggled with this for about 2 weeks. This is VB.net. Any
guidence is truly welcomed. RickN

Private Sub btnLoadAccess_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles btnLoadAccess.Click

        Dim sSQL As String
        Dim icount As Integer
        Dim i As Integer
        Dim ds1 As DataSet1

        ' create connection and create the command

        Dim oOleDbConnection As OleDb.OleDbConnection
        Dim objCommand As New OleDb.OleDbCommand
        Dim sConnString As String = _
                 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=C:\Documents and Settings\Rick
Nicholson\My Documents\Visual Studio Projects\MAX Data Interface\Lot
Table.mdb;" & _
                 "User ID=Admin;" & _
                 "Password="

        oOleDbConnection = New OleDb.OleDbConnection(sConnString)
        oOleDbConnection.Open()

        'start a loop here and loop thru and update access

        For i = 0 To DataSet1.Tables(0).Rows.Count - 1
        Next
        Try

            'working sql from pervasive or access...what ever
            'INSERT INTO table_name (column_name1,
column_name2,...)VALUES (value1, value2,....)
            ' try this insert into "LotTrackingHistory"("PRTNUM_72")
values ('333333')

            For Each rowX As DataRow In ds1.Lot_Tracking_Hist().Rows
                sSQL = "INSERT INTO LotTrackingHistory (PRTNUM_72)
Values (" & rowX.Item("PRTNUM_72").ToString & ")"
            Next

            With objCommand
                .Connection = oOleDbConnection
                .CommandText = sSQL
                .CommandType = CommandType.Text
                .ExecuteNonQuery()

            End With

            'Count the number of entries and display them
            'icount = (objCommand.ExecuteNonQuery())
            ' MessageBox.Show(icount)
            oOleDbConnection.Close()
            oOleDbConnection = Nothing

        Catch
        End Try
    End Sub
End Class