Loop: Import data while giving each row a running number(1,2,3 ...

  • Thread starter Thread starter ali
  • Start date Start date
A

ali

I have a linked table name Table1:


Tabl1:
NAME Nationality

James Japan
Yuri Russia
Steve GB
Dunst USA

----------------------------------------------------------
Question:

I want to import these name into an empty table (tb_all) in my MS Access
while giving each row a running number from "1" (2,3,4 ... onwards)


so that result should be

tb_all:
Running_number Name Nationality
1 James Japan
2 Yuri Russia
3 Steve GB
4 Dunst USA
 
Hi ali,
you can do that

dim rec_src as recordset
dim rec_trgt as recordset
dim rec_cntr as long

currentdb.execute("delete * from tb_all") ''to delete all records from tb_all
set rec_src=currentdb.openrecordset("select * from table1",dbopendynaset)
set rec_trgt=currentdb.openrecordset("select * from tb_all",dbopendynaset)
rec_cntr=1
do while not rec_src.eof
rec_trgt.addnew
rec_trgt.Running_number=rec_cntr
rec_trgt.name=rec_src.name
rec_trgt.nationality=rec_src.nationality
rec_trgt.update
rec_src.movenext
rec_cntr=rec_cntr+1
loop
rec_src.close
rec_trgt.close
msgbox "Done"

This is aircode so you have to test it.
HTH Paolo
 
Hi Paolo,

thanks for your reply.
However, this is not working. will you test it again

thanks alot!
 
Back
Top