Views / Stored Procedures / Updating Records

[Ryan.Chowdhury]

This is a general question regarding the use of view and stored
procedures. I'm fairly new to databases and SQL.

I've created a SQL database using an Access Data Project ("ADP") and
I'm satified with the table structure. I've moved on to building some
front ends for our users.

I'm running into situations where I want subreports to be built from
queries [views or stored procedures-I don't know which to use so I use
the term query] that are dependent on the values in other controls.
I've played with stored procedures and I've figured out how to send
criteria to a stored procedure and then dynamically change the record
source of a subreport.

However, I'm running into cases where I can't add records to the
results of a stored procedure. The table I'm running a stored procedure
on has five fields: (1) Primary Key for each record, (2) FundID that's
a primary key in another table, (3) CompanyID that's a primary key in
another table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute
2 of the (Fund/Company).

The stored procedure filters the set of Fund/Companies based on a
FundID from a form. I can update this stored procedure. However, for
users, they would like to see the Fund Name from the table that has
unique FundIDs. As soon as I include that into the stored procedure, I
can no longer add records.

My questions are many:
1. Is there a primer online that discusses the theory behind my
question? Recordsets, updatability, working with recordsets in forms?
2. What are some best practices for developing subreports, combo boxes,
list boxes, etc. where the data is dependent on the values in a control
3. I'm struggling with the best ways to grab objects on a form. If I'm
on the main form I'm comfortable working with the
Me.__object__._ _sub-oject routine. However, if I'm in one subform where
I need another subform to change based on the record I'm in, I feel
that my code to get at the subform is very klunky..
forms.main form name.sub form name.form.recor d source
I don't even know how I figured out the "form" part before record
source. Again, are there some basic rules or guides about navigating
through forms in VBA?
4. Should I be developing front ends in some other environment?

I know it's a lot, but all the advice from the newsgroups seems to
presuppose some knowledge about how ADP, ADO, ODBC..blah blah and I
can't seem to find any documents about ADP and SQL.

[Erland Sommarskog]

(Ry************ @gmail.com) writes:

However, I'm running into cases where I can't add records to the
results of a stored procedure. The table I'm running a stored procedure
on has five fields: (1) Primary Key for each record, (2) FundID that's
a primary key in another table, (3) CompanyID that's a primary key in
another table, (4) Attribute 1 of the (Fund/Company) and (5) Attribute
2 of the (Fund/Company).

The stored procedure filters the set of Fund/Companies based on a
FundID from a form. I can update this stored procedure. However, for
users, they would like to see the Fund Name from the table that has
unique FundIDs. As soon as I include that into the stored procedure, I
can no longer add records.
This problem as well as most of the questions you are ask are related
to Access and ADO. Access I don't have any experience of, but I do
some ADO. ADO is not a very good client library, because it's trying
to be smart, without being capable.

This particular problem can be worked around by not using the
..Update method of the recordset object, but perform updates through
stored procedures. However, there are cases where ADO will not even
let you modify the fields of a record.
My questions are many:
1. Is there a primer online that discusses the theory behind my
question? Recordsets, updatability, working with recordsets in forms?
David Sceppa has a good book on Microsoft Press, "Programmin g ADO",
but it's couple of years old by now (as is ADO).
2. What are some best practices for developing subreports, combo boxes,
list boxes, etc. where the data is dependent on the values in a control
3. I'm struggling with the best ways to grab objects on a form. If I'm
on the main form I'm comfortable working with the
Me.__object__._ _sub-oject routine. However, if I'm in one subform where
I need another subform to change based on the record I'm in,
These are Access questions that are better asked in
comp.databases. ms-access.
4. Should I be developing front ends in some other environment?

If you have the choice, I would recommend you to switch to Visual
Studio .Net. There is a whole lot you will have relearn if you
move to .Net, but the .Net lanaguage are nicer to work in, and instead
of ADO you get ADO .Net. Despite the similarity in name, ADO .Net
is completely different and much nicer to work with. ADO .Net
permits you to use methods equivalent to .Update. If the default
does not work, you can override.

As for learning ADO .Net, David Sceppa has a good book on that
as well. As for general

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ryan.Chowdhury]

Thanks Erland. I will check out those books. Recently, I've also
discovered the Function/View wrapper technique for requerying forms.
Thanks for you help. Down the road, if our company was to develop an
ADP project, would your firm be able to port it over to a .Net
environment?

[Erland Sommarskog]

(Ry************ @gmail.com) writes:

Thanks Erland. I will check out those books. Recently, I've also
discovered the Function/View wrapper technique for requerying forms.
Thanks for you help. Down the road, if our company was to develop an
ADP project, would your firm be able to port it over to a .Net
environment?

You might get better answers to such question in an Access newsgroup
or a .Net newsgroup. Having worked not at all with Access, and just a
little with .Net, I am not the right person to answer.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp