473,238 Members | 1,896 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

Views / Stored Procedures / Updating Records

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

Jul 23 '05 #1
3 2445
(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, "Programming 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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
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?

Jul 23 '05 #3
(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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Mike N. | last post by:
Hello: I am new to T-SQL programing, and relativly new to SQL statements in general, although I have a good understanding of database theory. I'm a little confused as to the fundamental...
1
by: Brad H McCollum | last post by:
I've looked through many suggestions and partial examples all over this newsgroup and still am not coming up with anything that does specifically what I'm wanting to accomplish. I'm writing a VB...
8
by: Mike | last post by:
Hello, I have a few rather urgent questions that I hope someone can help with (I need to figure this out prior to a meeting tomorrow.) First, a bit of background: The company I work for is...
2
by: Dave Stone | last post by:
I've just installed SP3A on MSDE 2000 since when any projects I open have a database window which (under 'Objects') lists Database Diagrams, but not Views or Stored Procedures. Has anyone else seen...
0
by: billmiami2 | last post by:
Perhaps many of you MS Access fanatics already know this, but it seems that stored procedures and views are possible in Jet. I thought I would leave this message just in case it would help anyone....
28
by: mooreit | last post by:
The purpose for my questions is accessing these technologies from applications. I develop both applications and databases. Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and...
4
gateshosting
by: gateshosting | last post by:
Good day, I am programming my Stored Procedures and Views for performance, since we are dealing with millions of records. I know this question is going to sound like I do NOT want to do it this...
6
by: binky | last post by:
Hi folks, I'm just learning how to use MS Access as a front end for SQL Server, and have a question: I have a stored procedure that returns a set of records from a SQL Server and loads it into a...
5
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.