Best Practive - Updates between Access 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

 

[aaron.kempf]

yes; you can do updates like that on the SQL server side throgh a
simple Update statement; maybe a subquery.

i personally think that MDB linked against SQL is crap-- it's not

reliable
fast
scalable

I believe that ACCESS DATA PROJECTs are a better fit for most
situations

no linking, no refreshing, no crappy queries (views and sprocs are much
more powerful)

 

[Robert Morley]

As to what the "Best Practice" is, I avoid to labelling anything as "best"
in a newsgroup, cuz rightly or wrongly, somebody will invariably disagree
with you.

In terms of what's practical (at least from the front-end), the two best
approaches to bulk-updating that I can think of are to use ADODB in
bulk-update mode, or to create your own "UPDATE" commands and send a whole
bunch of them at once. If you want to get the back-end more involved in the
process, then some kind of Stored Procedure is probably your best bet.

Using a bulk-updating recordset is fairly simple. Assuming you've already
got a regular ADODB recordset being opened, just change your code a tiny
bit...it'll probably look something like the following:

myRS.CursorLocation = adUseClient
myRS.Open "myTableOrView", CurrentProject.Connection, adOpenStatic,
adLockBatchOptimistic, adCmdText 'or adCmdTable as appropriate
...
While Not myRS.EOF
(remove any myRS.Update's in your loop)
myRS.MoveNext
Wend
myRS.UpdateBatch

The CursorLocation, adLockBatchOptimistic, UpdateBatch, and removing your
existing Update's are the important parts here.


If you want to do it via UPDATE commands, this might turn out to be even
faster, but it has very little in terms of error checking other than "did
this command work".

To use this method, you'd just construct a bunch of UPDATE commands...syntax
is identical to what it was in Access, I believe:

Dim strCommand as String

...
(Loop through each update)
strCommand = strCommand & "UPDATE myTableOrView SET OrderID = <New
Value> WHERE OrderID = <Old Value>" & vbCrLf
(End Loop)
CurrentProject.Connection.Execute strCommand, , adExecuteNoRecords


Finally, if you're using a Stored Procedure, you can either use a similar
concept to the bulk updates, immediately above, or you can have the SP
simply run an update query based on a linked table...on the Access side,
you'd (bulk) insert values into a table with two fields, NewValue and
OldValue, then call the SP, and let SQL Server handle it from there. Again,
you have a little less ability to handle errors with this approach.



Best of luck,
Rob

 

[aaron.kempf]

never ever ever ever ever rely on ADODB to do that consistently

run the SQL Statements 'Update mytable set myfield = myvalue where mypk
= 12'

 

[david epsom dot com dot au]

One reason for doing individual updates is so that
you can identify which particular updates fail.

(david)

 

[aaron.kempf]

i dont buy it.

things dont just fail; make your SQL Statement either update all or
none in one big transaction