Best Practise For Updates between Access 2000 and SQL Server

Hi,
Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! :o)
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 RegardsThis is not the right forum.
--
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:

> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> 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
>|||Petep
This is the right forum for you. There is a seperate forum for acceess/sql
integration post there.
What you described is not the right method. probably they are using mdb not
adp.
SQL SERVER integrates well with access in adp mode. you can do set based
operations as you do in sql. In this mode data will completely reside in sql
.
so you can write spocs/functions what not, everything in sql.
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:

> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> 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
>