Insert Recordset Data

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • artemetis
    New Member
    • Jul 2007
    • 76

    #1

    Insert Recordset Data

    Hey there....

    I am using a recordset to pull some data out of a table and in turn dump that data into a new table.

    My code is as follows and works fine, but I am looking for assistance in dumping this into the new table.

    Any help would be greatly appreciated.

    --Art


    Private Sub Form_Load()
    Dim rs0 As Recordset
    Dim ArraySplit() As String
    Dim n As Integer
    Set rs0 = CurrentDb.OpenR ecordset("UserF ile")

    rs0.OpenRecords et

    Do Until rs0.EOF
    ArraySplit = Split(rs0![GroupList], "~")
    For n = 0 To UBound(ArraySpl it)
    Debug.Print rs0![User] & ";" & rs0![Name] & ";" & _
    rs0![Mgr] & ";" & ArraySplit(n)
    Next n
    rs0.MoveNext
    Loop

    rs0.Close

    End Sub
  • JKing
    Recognized Expert Top Contributor
    • Jun 2007
    • 1206

    #2
    What will the table you're inserting into look like? In otherwords what fields are in the other table and what fields from the current recordset do you want to insert?

    Comment

    • artemetis
      New Member
      • Jul 2007
      • 76

      #3
      Thanks for the reply.

      The only fields I need in the new table are the ones in the recordset above + a UID.

      GroupList
      User
      Name
      Mgr

      Comment

      • JKing
        Recognized Expert Top Contributor
        • Jun 2007
        • 1206

        #4
        Ok and what data types are all the fields? Are they text or numbers or dates.
        Is the UID going to be an autonumber?

        Comment

        • artemetis
          New Member
          • Jul 2007
          • 76

          #5
          Sorry, I should've posted that 411.
          The UID will be autonumber and the rest are plain old text.

          Thanks again!

          Comment

          • abolos
            New Member
            • Apr 2007
            • 65

            #6
            Originally posted by artemetis
            Hey there....

            I am using a recordset to pull some data out of a table and in turn dump that data into a new table.

            My code is as follows and works fine, but I am looking for assistance in dumping this into the new table.

            Any help would be greatly appreciated.

            --Art


            Private Sub Form_Load()
            Dim rs0 As Recordset
            Dim ArraySplit() As String
            Dim n As Integer
            Set rs0 = CurrentDb.OpenR ecordset("UserF ile")

            rs0.OpenRecords et

            Do Until rs0.EOF
            ArraySplit = Split(rs0![GroupList], "~")
            For n = 0 To UBound(ArraySpl it)
            Debug.Print rs0![User] & ";" & rs0![Name] & ";" & _
            rs0![Mgr] & ";" & ArraySplit(n)
            Next n
            rs0.MoveNext
            Loop

            rs0.Close

            End Sub
            The command you need is Insert:

            form.recordset= insert into <table> [field1],[field2],... values val1,val2,...

            Comment

            • artemetis
              New Member
              • Jul 2007
              • 76

              #7
              Originally posted by abolos
              The command you need is Insert:

              form.recordset= insert into <table> [field1],[field2],... values val1,val2,...
              Not really sure I follow how to add that line of code?

              Comment

              • JKing
                Recognized Expert Top Contributor
                • Jun 2007
                • 1206

                #8
                Hi, again. I think what you want to do is create a string for your sql and then execute it within the for loop.

                [code=vb]
                Private Sub Form_Load()
                Dim rs0 As Recordset
                Dim ArraySplit() As String
                Dim n As Integer
                Set rs0 = CurrentDb.OpenR ecordset("UserF ile")

                rs0.OpenRecords et

                Do Until rs0.EOF
                ArraySplit = Split(rs0![GroupList], "~")
                For n = 0 To UBound(ArraySpl it)
                strSQL = "INSERT INTO yourTableName VALUES ('" & rs0![User] & "','" & rs0![Name] & "','" & _
                rs0![Mgr] & "','" & ArraySplit(n) & "')"
                Docmd.RunSQL(st rSQL)
                Next n
                rs0.MoveNext
                Loop

                rs0.Close

                End Sub
                [/code]

                Notes:
                yourTableName - replace this with actual name of the table you want to add this data to

                The values you are inserting should be in the same order as they appear in the table you are adding them to. In this case we are inserting in the order of User, Name, MGR, GroupList. So the table we insert to should have columns in the order of UID, User, Name, MGR, GroupList.

                Notice we did not include UID as part of the insert. This is because it is an autonumber and generates itself for each new record added.

                Give that a try and let me know how you make out with it. If you have any further questions just ask.

                JKing

                Comment

                • artemetis
                  New Member
                  • Jul 2007
                  • 76

                  #9
                  Thanks so much!
                  This seems to work, but it is prompting me for the insert at each record.
                  Is there a way around that?
                  There are thousands of records.

                  Thanks!
                  --Art

                  Comment

                  • JKing
                    Recognized Expert Top Contributor
                    • Jun 2007
                    • 1206

                    #10
                    Yes, there is a command for turning the warnings off though you must always be sure to turn them back on when your action is finished.

                    [code=vb]
                    Docmd.SetWarnin gs False

                    'Start of your insert code here
                    'more insert code here
                    'and some more here
                    'End of your insert code

                    Docmd.SetWarnin gs True
                    [/code]

                    Comment

                    • ADezii
                      Recognized Expert Expert
                      • Apr 2006
                      • 8834

                      #11
                      Originally posted by artemetis
                      Thanks so much!
                      This seems to work, but it is prompting me for the insert at each record.
                      Is there a way around that?
                      There are thousands of records.

                      Thanks!
                      --Art
                      This should work nicely for you with no Errors, just make a couple of personalized substitutions:
                      [CODE=vb]Dim MyDB As DAO.Database, rs0 As DAO.Recordset
                      Dim ArraySplit() As String, rstNew As DAO.Recordset
                      Dim n As Integer

                      Set MyDB = CurrentDb()
                      Set rs0 = MyDB.OpenRecord set("UserFile", dbOpenSnapshot)
                      Set rstNew = MyDB.OpenRecord set("tblNewTabl e", dbOpenDynaset)

                      Do Until rs0.EOF
                      ArraySplit = Split(rs0![Grouplist], "~")
                      For n = 0 To UBound(ArraySpl it)
                      With rstNew
                      .AddNew
                      ![Grouplist] = ArraySplit(n)
                      ![User] = rs0![User]
                      ![Name] = rs0![Name]
                      ![Mgr] = rs0![Mgr]
                      .Update
                      End With
                      Next n
                      rs0.MoveNext
                      Loop

                      rs0.Close
                      rstNew.Close[/CODE]

                      Comment

                      Working...