Copying a record 1

[Dryseals]

I have a dump from a system that I'll need to break apart into around 50 individual tables. I'll need to do this a few times a year so I wanted to automate the process.

I'm down to the final part of the program and I'm having a brain poof. I want to copy a whole record out of one table and place it in another. Which of the fifty tables the record will go into is based on a value in field3 of the main table,table name and field3 are exact matches, easy enough.

Each record will have 156 fields, so I'm trying to come up with a way of not having to create a string for this.

Is there a way to grab the all the fields in the record, copy them and append it into another table, the append part I have, it's the copy I can't seem to find an easy way to do.

 

[adalger]

Dim rstSrc as DAO.Recordset
Dim rstDest as DAO.Recordset

Dim fld as Field

// do your stuff to get the desired record into rstSrc
// and point rstDest at the destination table

// Loop the following for each record you want copied:
rstDest.AddNew
For Each fld in rstSource.Fields
  rstDest.Field(fld.Name) = rstSrc.Fields(fld.Name)
Next fld
rstDest.Update

Is that what you're looking for?

TMTOWDI - it's not just for Perl any more

 

[JerryKlmns]

Dryseals

Create a recordset to gather distinct values of that field3 of the main table, and loop.

Do While Not rst.EOF
SQL="INSERT INTO Table" & rst.Fields(0) & ".* "_
"SELECT OriginalTable.* " & _
"FROM OriginalTable " & _
"WHERE field3=" & rst..Fields(0)

CurrentProject.Connection.Execute SQL,,129
rst.MoveNext
Loop

 

[Dryseals]

Thanks to both of you, I used adalger's method and with a little massaging was able to get it to work just like I needed it to.