Bulk copy of records VBA

[richardw]

Hi dear all,

I am working on a project using Access 2007 and I'm looking for a method (If that exists) that allow to do a bulk copy of records on the same table (>200 records).

Another issue, the IDs are not AutoNumber and should be updated too when doing the copy to avoid duplicates.

Thanks in advance

 

[arnelgp]

using code:

dim db as dao.database
dim rs as dao.recordset
dim lngID as long
dim lngCounter as long
dim lngRecordCount as long
dim bm as variant
dim varField1 as variant
set db=currentdb
set rs= db.openrecordset("yourtable", dbopendynaset)

lngcounter = 1
with rs

if not (.bof and .eof) then
.movelast
lngRecordCount = .Recordcount
lngID = ![id]
.movefirst
for lngcounter = 1 to lngRecordCount
varField1=![Field1]
bm = .bookmark
.addnew
![Field1] = varField1
.update
.bookmark = bm
.movenext
next
end if
.close
end with

 

[richardw]

Hi arnelgp, thank you so much.

I didn't try it yet, but does it update the id too (new IDs with no duplicates)? Because I didn't get the syntax..

Thank you again !!

 

[arnelgp]

dim db as dao.database
dim rs as dao.recordset
dim lngID as long
dim lngCounter as long
dim lngRecordCount as long
dim bm as variant
dim varField1 as variant
set db=currentdb
set rs= db.openrecordset("yourtable", dbopendynaset)

lngcounter = 1
lngID = DMax("ID", "yourTable")

with rs

if not (.bof and .eof) then
.movelast
lngRecordCount = .Recordcount
.movefirst
for lngcounter = 1 to lngRecordCount
varField1=![Field1]
bm = .bookmark
.addnew
![Field1] = varField1
![ID] = lngID + lngCounter
.update
.bookmark = bm
.movenext
next
end if
.close
end with

 

[richardw]

Thank you Arnelgp, you are a hero !

I have one more question if you don't mind.
I'd like to copy just the data where ID = [comboboxID]

Thank you again and have a great day

 

[richardw]

Thank you Arnelgp, you are a hero !

I have one more question if you don't mind.
I'd like to copy just the data where ID = [comboboxID]

Thank you again and have a great day

Not the main ID of the table but another related ID.