Best Practise For Updates between Access 2000 and SQL Server

[PeteP]

Hi,


Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! )


The developer has, for better or for worse, (I'm not sure), designed a
frontend that grabs a set of order records from SQL and pulls them into
a form. While the records are there, the user can allocate the order to
a person by selecting their name, we would only be talking about 300
records at a time. When the user closes the relevant form, the orders
that have been updated in the frontend are sent to the SQL-Server.
Now, from what I can make of the code that Updates back to SQL, the
developer initially grabs a recordset of those orders in the Frontend
requiring an update and then loops through them one by one and updates
the SQL-Server record using a field call OrderID.


My questions are: Is this the only method of doing this? Meaning -
surely there is a way to do a bulk Update between the two applications
that doesn't require cycling through records.


And what is considered best practise for doing bulk record updates
between Access and SQL-Server? I could probaly re-invent the wheel
myself, but if anyone can point me in the right direction it would be
appreciated.


Kind Regards

 

[Jeff Boyce]

Pete

Without a better idea of what the loop is doing, it will be tough to tell if
there is a "better way".

If the loop is using variable values, calculating results or other
manipulation for each record to be added, there's the possibility that
looping in code is the only way to get it done. Possibility, though...

There's also the possibility (again depending on what is being done with/to
the data) that an update query could work on the entire set at once.

?Perhaps if you posted back with the code/loop?