Append Same Data To Table N Times

  • Thread starter Thread starter ButchyBoy via AccessMonster.com
  • Start date Start date
B

ButchyBoy via AccessMonster.com

Howdy Everyone! . . . .

I need to append the same data, N numer of times, to a table on the many
side of a relationship. I have no problem performing this in VBA using an
append SQL statement & the docmd.RunSQL command.

My question is, Can this be done with query or append query somehow?
 
Howdy Everyone! . . . .

I need to append the same data, N numer of times, to a table on the many
side of a relationship. I have no problem performing this in VBA using an
append SQL statement & the docmd.RunSQL command.

My question is, Can this be done with query or append query somehow?
Click to expand...

If (and in my opinion it's a pretty big IF!; storing the exact same
data multiple times seems like bad design) you need to do this, you
can use an auxiliary table. Let's call it Num, with a single numeric
field N; fill Num with values from 0 to the largest N you'll ever need
(be generous, it's still a tiny table).

Include Num in your Append query with NO join line - a Cartesian Join.
As a criterion on N put

< 300

if you want to append 300 rows - or

< [Enter number of rows]

and use code like

Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim prm As Parameter
Set db = CurrentDb
Set qd = db.Querydefs("MyAppendQuery")
Set prm = qd.Parameters(0)
prm.Value = N <the number of rows to add>
qd.Execute dbFailOnError
Set qd = Nothing

John W. Vinson[MVP]
 
Back
Top