Call SQL Stored Proc to calculate a field in a table in Access 2007

[barbola]

I have all my backend data in SQL 2000 (soon to be upgrading to 08). Data gets imported and a bunch of stored procedures run to update additional fields in the data table.

I have another table and an Access front-end form for the use to enter some information. I have another SP in SQL to calculate one of the fields after the user input.

I don't seem to have a clue how to call this SP, or when to call it, or how to make sure only one record gets updated at a time. The only documentation I can find either isn't for Access 2007 or is with parameters and i can't make sense out of it.

I guess I need connection strings and stuff (the VBA code has always been greek to me). Can someone please at least help get me started?

My Access file (accdb) is linked to the SQL tables.

 

[PHV]

Can't you use triggers in the SQL BE ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[barbola]

I suppose so but I don't know how.

 

[PHV]

Ask the DBA.

 

[barbola]

That's funny, I am the DBA although I am not "a" DBA. I have no help around here, which is why I come to TT.

I have converted all my SP's to Triggers. but now I have a problem, not knowing my DBA stuff. Some of the calculations rely on the results of other calculations. How do I tell SQL to run the triggers in a specific order? Am I way off track?

This project is a priority and we have very limited resources. They won't hire a real DBA because then they would have to pay a real DBA's salary. So they've got me, and I'm not the fastest learner, and I don't have alot of time to learn. So again, I am asking for help here. I can find another web site if you don't want to help bu this one is alot easier to use.

thank you.