Bulk copy of records VBA (1 Viewer)

richardw · May 12, 2016

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 · May 12, 2016

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 · May 12, 2016

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 · May 14, 2016

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 · May 17, 2016

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 · May 17, 2016

richardw said:
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.

Bulk copy of records VBA (1 Viewer)

richardw

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

richardw

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

richardw

Registered User.

richardw

Registered User.

Similar threads

Users who are viewing this thread