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.
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.
Comment