Moving data from one table to another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a table with 32 fields...there is only one record in this table, but I'd like to take the data from each field, and copy them to another table I've created, only this time there is only one field, and I'd like to create 32 records (each record would contain the data of each field from my original table. Could I go about doing this? Thanks!
 
Hi:

Make sure your code has a reference to "Microsoft DAO 3.6" (in Tools |
References in your VBA Editor).

Try this code in a button:

Dim fld as DAO.Field
Dim rs as DAO.Recordset, rs1 as DAO.Recordset
Dim db as DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from [FromTable]",dbOpenSnapshot)
Set rs1 = db.OpenRecordset("Select * from [ToTable]",dbOpenDynaset)

For each fld in rs.Fields
rs1.AddNew
rs1.Fields(0) = fld.value
rs1.Update
Next

Set rs1 = Nothing
Set rs = Nothing
Set db = Nothing

Regards,

Naresh Nichani
Microsoft Access MVP
D Lee said:
I've got a table with 32 fields...there is only one record in this table,
Click to expand...
but I'd like to take the data from each field, and copy them to another
table I've created, only this time there is only one field, and I'd like to
create 32 records (each record would contain the data of each field from my
original table. Could I go about doing this? Thanks!
 
Naresh, that worked perfectly! Thanks for the (instant) assistance

darre

----- Naresh Nichani MVP wrote: ----

Hi

Make sure your code has a reference to "Microsoft DAO 3.6" (in Tools
References in your VBA Editor)

Try this code in a button

Dim fld as DAO.Fiel
Dim rs as DAO.Recordset, rs1 as DAO.Recordse
Dim db as DAO.Databas

Set db = CurrentD
Set rs = db.OpenRecordset("Select * from [FromTable]",dbOpenSnapshot
Set rs1 = db.OpenRecordset("Select * from [ToTable]",dbOpenDynaset

For each fld in rs.Field
rs1.AddNe
rs1.Fields(0) = fld.valu
rs1.Updat
Nex

Set rs1 = Nothin
Set rs = Nothin
Set db = Nothin

Regards

Naresh Nichan
Microsoft Access MV
D Lee said:
I've got a table with 32 fields...there is only one record in this table
Click to expand...
but I'd like to take the data from each field, and copy them to anothe
table I've created, only this time there is only one field, and I'd like t
create 32 records (each record would contain the data of each field from m
original table. Could I go about doing this? Thanks
 
Back
Top