Copy a Record from One Table To Another 2

[Michael42]

Hello, can anyone tell me the easiest way to copy a record from one table to another? I am using Access97 (same DSN).

My goal is to find a routine that grabs all fields without coding for each individual field. This would make it very scaleable.

Thanks for your help,

Michael

 

[JeremyG]

Dont need VB code at all.....execute this SQL Statment in your code.

strSQL = "INSERT INTO NewTable (Field1, Field2, Field3) " & _
"SELECT Field1, Field2 Field3 FROM OldTable"


connection.execute strSQL

 

[GriffMG]

I think Micheal wants to do it without knowing the field names - he can do the asp equivalent of VFPs scatter to and gather from stuff.

I think it can be done by using recordsets and refering to
the field numerically...

rsTargetTable.Edit
for i = 1 to (number of fields in question)
rsTargetTable(i) = rsSourceTable(i)
next i
rsTargetTable.Update

Off the top of my head, I can't remember if recordsets start at 0 or 1, or for that matter how you get the upper limit! (could cheat and let it error out) I'll look it up and get back to you.

Tables must be identical structures, same fields in same order.

HTH
Griff

 

[GriffMG]

From VB this seems to work:

Dim i As Integer
rsTarget.Edit
For i = 0 To rsSource.Fields.Count - 1
rsTarget(i) = rsSource(i)
Next
rsTarget.Update

 

[Michael42]

Hey Guys - this worked great. It was not grabbbing my last field (a memo) so I had to do that manually. Anyone know why? Thanks again for you help!!!


Here is how I used it:

Dim i
oRSb.AddNew
for i = 0 to oRSa.Fields.Count -1
'response.write oRSa(i) & &quot;<br>&quot;
oRSb(i) = oRSa(i)
next
oRSb(&quot;Comments&quot = oRSa(&quot;Comments&quot
oRSb.Update

 

[GriffMG]

Micheal,

I made the upper bound equal the field cound, because the lower bound is 0. You could try taking the -1 off to see if that sorts it (although why it would is beyond me!)

Regards
Griff