I am finally taking the plunge into moving a database from a Jet backend to SQL Server and moving to an ADP. I have been developing in Access for nearly ten years but have done little with ADP's and only moderate work with SQL Server.
What would really help me out is a list of the methods or concepts that can be employed to work around the difference in doing things. A in a nutshell guide to programming equivalents or tricks would be great.
I understand that I have to use Stored procedures in order to use parameters.
I guess my question related to that is what ways can the parameters be passed?
Obviously a connection object can be used to call a stored procedure and concatenating variables is in is trivial. The issue I see is having a combobox with a stored procedure as its row source. How does one specify the parameter? Does requerying work the same as if it were Jet referencing a form control as a parameter?
The second thing that has me uncertain is handling nested parameter queries. The feature I am seeing lost is the ability to make a parameter query that behaves as a view. Specifically, I want a parameterterized result set to be used in another higher level select statement (my understanding is you can't use a stored procedure as a subquery). In an MDB, this is easy as each querydef has this view like behavior unless of course it is an action query. My only thought is to take each lower level querydef on the MDB side and make it a subquery in the select of the top level object. Besides being tedious, this adds a complexity to code management because I might really only want to maintain this one parameterized select statement in one place instead of several higher level objects.
All insights, recommendations and thoughts on how to skin the cat are welcome.
What would really help me out is a list of the methods or concepts that can be employed to work around the difference in doing things. A in a nutshell guide to programming equivalents or tricks would be great.
I understand that I have to use Stored procedures in order to use parameters.
I guess my question related to that is what ways can the parameters be passed?
Obviously a connection object can be used to call a stored procedure and concatenating variables is in is trivial. The issue I see is having a combobox with a stored procedure as its row source. How does one specify the parameter? Does requerying work the same as if it were Jet referencing a form control as a parameter?
The second thing that has me uncertain is handling nested parameter queries. The feature I am seeing lost is the ability to make a parameter query that behaves as a view. Specifically, I want a parameterterized result set to be used in another higher level select statement (my understanding is you can't use a stored procedure as a subquery). In an MDB, this is easy as each querydef has this view like behavior unless of course it is an action query. My only thought is to take each lower level querydef on the MDB side and make it a subquery in the select of the top level object. Besides being tedious, this adds a complexity to code management because I might really only want to maintain this one parameterized select statement in one place instead of several higher level objects.
All insights, recommendations and thoughts on how to skin the cat are welcome.
![[pipe]](/data/assets/smilies/pipe.gif "[pipe] [pipe]")