move data from sql 2000 to ms access

[bghodsi]

Hi folks:

Can anyone tell me what I am doing wrong in the coding below:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")


            Dim isconnecting As Boolean = True

            While isconnecting

          

            Dim str As String = _
            "INSERT INTO snippet  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
                Dim cn As New OleDbConnection(str)
                Dim cmdd As New OleDbCommand(str, cn)
                With cmdd
                    .Parameters.Add(New OleDbParameter("@snippetName", _
                     OleDbType.VarChar, 50)).Value = ds.Tables("snippetName")
                    .Parameters.Add(New OleDbParameter("@snippetSource", _
                    OleDbType.VarChar, 50)).Value = ds.Tables("snippetSource")
                    .Parameters.Add(New OleDbParameter("@snippetCode", _
                    OleDbType.LongVarWChar)).Value = ds.Tables("snippetCode")
                End With
                cn.Open()
                cmdd.ExecuteNonQuery()
                cn.Close()
                isconnecting = False
            End While


        Catch ex As Exception
            MsgBox("Error " & ex.Source & "  " & ex.Message)

        End Try

    End Sub

[kvprajapati]

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")



            Dim str As String = _
            "INSERT INTO [snippet]  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
            Dim cn As New OleDbConnection(str)
            Dim cmdd As New OleDbCommand(str, cn)
            cmdd.Parameters.Add(New OleDbParameter("@snippetName",OleDbType.VarChar, 50))        
            cmdd.Parameters.Add(New OleDbParameter("@snippetSource",OleDbType.VarChar, 50))

            cmdd.Parameters.Add(New OleDbParameter("@snippetCode",OleDbType.LongVarWChar))
             cn.Open()
           For Each row as DataRow in ds.Tables("snippet")
                cmdd.Parameters("@snippetName").Value = row("snippetName")
                cmdd.Parameters("@snippetSource").Value = row("snippetSource")
                cmdd.Parameters("@snippetCode").Value = row("snippetCode")
                cmdd.ExecuteNonQuery()
           Next
           cn.Close()
    End Sub

[kvprajapati]

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

        Dim ConnectionString As String = "Server=hp;database=library;integrated security=SSPI"

        MessageBox.Show("Connection established")

        Dim sql As String = "SELECT snippetID, snippetName, snippetSource, snippetCode FROM snippet order by snippetName"

        Dim cnn As New SqlConnection(ConnectionString)
        Dim cmd As New SqlCommand(sql, cnn)
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        cmd.CommandText = sql
        da.SelectCommand = cmd
            da.Fill(ds, "snippet")



            Dim str As String = _
            "INSERT INTO [snippet]  (snippetName, snippetSource, snippetCode)" & _
            "VALUES (@snippetName, @snippetSource, @snippetCode)"
            Dim cn As New OleDbConnection(str)
            Dim cmdd As New OleDbCommand(str, cn)
            cmdd.Parameters.Add(New OleDbParameter("@snippetName",OleDbType.VarChar, 50))        
            cmdd.Parameters.Add(New OleDbParameter("@snippetSource",OleDbType.VarChar, 50))

            cmdd.Parameters.Add(New OleDbParameter("@snippetCode",OleDbType.LongVarWChar))
             cn.Open()
           For Each row as DataRow in ds.Tables("snippet")
                cmdd.Parameters("@snippetName").Value = row("snippetName")
                cmdd.Parameters("@snippetSource").Value = row("snippetSource")
                cmdd.Parameters("@snippetCode").Value = row("snippetCode")
                cmdd.ExecuteNonQuery()
           Next
           cn.Close()
    End Sub

[meffe]

Why dont you just create the SQL statement from Sql 2000 then execute it in Access