Link to home
Create AccountLog in
Avatar of Eileen Murphy
Eileen MurphyFlag for United States of America

asked on

Stored Procedures - Microsoft Access

SQL Stored Procedure Question - From within an Access application -- sending changes to the SQL Server via a stored procedure call ----

Is it better to update one field at a time -- or to send all changes made on an unbound form in one function to the server?

Attached is an example of the Stored Procedure.

Client is experiencing awful bottlenecks and locking - ODBC timeouts, etc. I am in the process of rewriting the application in the trouble-spots but want to get some advise on how best to do this. I have used almost the exact stored procedure as the attached for other client apps with no issues -- but in this client's environment it locks everyone up - everything locks their system up.

Some forms have dozens of hidden subforms with links back to the customer table -- being eliminated -- and all their combo boxes (to select a customer) is another link back to the customer table -- I am replacing those with passthrough queries, etc.
SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Eileen Murphy

ASKER

Hi John. I am moving them to temp tables where I can -- their database is pretty dynamic... 3 different apps use the customer information -- and are also updating it throughout the day -- the accounting dept is updating customer information, sales is updating customer information, marketing is reading (mostly) customer information, etc. That is the primary table causing the locking when I ran the SQL scans -- and I also isolated the biggest culprits being the sales departments (the most users pounding away at customer information all day) -- it's just awful for them. So - I am de-constructing each form and rebuilding them as I go -- just wasn't sure about the stored procedure versus say a single call to the server with an execute, db SeeChanges, or an update query --- am in a time crunch trying to perform the work while they're all out of the system.   Appreciate your input :)
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
"Well, as a user I expect that the entire data is saved. Sending it per field means, that an error can lead to partial saved data, which is imho a no go." << Good Point!