Which is better (better asked)

  • Thread starter Thread starter djc
  • Start date Start date
D

djc

need to add what could be in some cases a large number of records to a
table all in one operation. It would be from an access front end linked to
sql server2000 backend table (jet-connected ODBC).

I will be keying off of one field in one recordset to use as the key field
in the new records I need to add to the backend table.

which would be faster:
A. loop through my recordset and .addNew each record individually directly
to the BE table.
OR.
B. loop trough my recordset and create a local table. Then do some kind of
mass append operation?
OR
C. NO looping through recordset. Maybe an INSERT INTO or some kind of make
table query would be best? Then append the whole table to the target table?

Thanks in advance.
 
A. loop through my recordset and .addNew each record individually directly
to the BE table.
OR.
B. loop trough my recordset and create a local table. Then do some kind of
mass append operation?
OR
C. NO looping through recordset. Maybe an INSERT INTO or some kind of make
table query would be best? Then append the whole table to the target table?
Click to expand...

None of the above, I'd say: just create an Append query, appending the
selected records from the SQL table directly into the target table.
 
thanks for the reply,

However I don't think I was clear on one thing: I am only using 1 field from
the source table (a username) as part of the new record for the target
table. So going on your suggestion I would still need to somehow create a
temp table first right? (then append the temp table to the target table)

I have:

tblUsers:
fldUserName (key)
fldFirstName
fldLastName
etc..

tblAssignments:
fldID (key)
fldUserName

Through a user interface the user will filter down a subset of users from
tblUsers. Then select a value that would be a valid fldID, and hit a button
that would create one entry in tblAssignments for every user in the filtered
subset. End result would look like this:

tblAssignments: (fldID; fldUserName)
100; mmcormick -already there
100; mrjenkins -already there
101; jsmith -new
101; jblow -new
101; mlou -new
101; cfloyd -new
etc..

the user just filtered for the names you see by 101 and chose to assign them
to something with that fldID

sorry so long. I am having a hard time explaining this.
 
the other field data is only one field. An ID field. I just need to add one
record for every UserName in the current filter on table1 to another table
(table2). The table I need to add these records to (table2) only has two
fields. 1) the ID field which would be obtained from a user selection and 2)
the UserName field which comes from the current filter on table1.

ID field that is obtained from a user selection (a dropdown box) would be
the same value for each operation. Meaning: user selects ItemRoom19 from the
drop down which has ID 101 bound to it. If there are 12 UserNames in the
current filter then table2 (from above) would get 12 new records like this:

101, UserName1
101, UserName2
101, UserName3
etc...

the user would have to select a new ID and launch this action agian to get a
new ID & Username pair in the table2.

The dropdown from which the user selects the ID to be used in table2 does
come from another table. (say table3) Is this what you were asking? Does
this mean I should use an 'append' query?

thanks,
djc
 
Back
Top