I'm working on an Access 2003 ADP connected to a SQL Server 2000 backend.
So far Access has managed all of my data access for me, and done a great job of doing it. I use stored procedures for the record sources of my forms and reports but it's only because it's so easy to pass parameters to them (all you have to do is have a control with the same name as the parameter, and requery after updating that control). These stored procedures have really not been much more than views aside from the parameters.
Over the weekend I discovered using ADO to create a list of all the forms and reports in my project and then all the properties of all the controls on those forms and reports.
Now I'd like to get into some procedural events, and I was wondering which to use: SQL Stored Procedure OR ADO using VBA
I have created a multi-select list box that lists all the orders in our system. I want the user to be able to select one or more orders and create a record in a related table, but first I have to check to see if there is already a record in the related table or if I need to create a new one.
What are the advantages/disadvantages of doing this through a stored procedure or programming it in VBA using ADO recordsets?
Any thoughts would be greatly appreciated as I'd like to begin working on this by the end of the day.
Well, I think I've decided to go with the stored procedure, for several reasons:
1. Security, it's functionality falls under SQL Servers security model
2. Keep as much work/data on the server
3. I keep seeing that it tends to be easier to sort data in an SP than with ADO
(this is not the gospel, just an impression I get)
Now I just need to learn how to do what I need.
I think I'll create a function to test if the record exists that returns a boolean
and then create an update procedure and a new record procedure.