|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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
"PeteP" <Peter.Pianta[ at ]optus.com.au> wrote in message
news:1130131102.296489.11440[ at ]g47g2000cwa.googlegroups.com...
[Quoted Text] >
> 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
>
|
|
One reason for doing individual updates is so that
you can identify which particular updates fail.
(david)
"PeteP" <Peter.Pianta[ at ]optus.com.au> wrote in message
news:1130131102.296489.11440[ at ]g47g2000cwa.googlegroups.com...
[Quoted Text] >
> 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
>
|
|
|