[AccessD] A2003: return records from Stored proc in SQL

Hi Darren,

1. Why would you want to run a query this way? Why not create the query in
the database itself, as a view, say, or a stored procedure, and fire that
instead? You could open one of these files and copy the syntax into EM or QA
and save it as a view. Then all you need to do is name the view as your
record source for the form. Ditto incidentally for combo boxes, etc. Just
include the name of the view as your rowsource.

2. There are a couple of ways to do this: use a view and then "parameterize"
it using the data tab of the form's property sheet. You'll see the extra
elements there toward the bottom. This is a very easy to do it. Second, you
can use a stored procedure and supply the parameters there, also on the data
tab. The view approach is easier and you don't get tied up with read-only
data, assuming that want to write to it, of course.

3. I'm not sure about that one, at least in terms of how to display the
result count on the status bar or something. Of course, what you can do is
DoCmd.RunSQL, which will open a datasheet with the result set in it, and
then you can look at the navigation bar and get the rowcount there.

P.S.
Although you didn't ask, I'll give you a tip. If you have a master-detail
form, use views for both record sources. Access will automatically scope the
detail rowset by its parent key, without you lifting a finger, and it's
blazingly fast. I have done it that way with 50K rows in the master and
obviously more in the detail, and performance was very acceptable.

hth,
Arthur

On 6/19/07, Darren D <darrend at nimble.com.au> wrote:
>
> Hi Arthur
>
> Excellent - I have a question already (3 actually) :-)
>
> I have SQL queries stored as dot SQL files - cool
>
> How can I run one of these files SAY...C:\MyFolder\SomeCoolQuery.sql from
> one of
> the new ADP forms I intend to create?
>
> Basically I can just 'drag' the dot SQL file over to the Query Analyser
> (QA)
> Grid
>
> Now maybe I can reference the 'path' of the dot SQL file and 'run it' - Is
> this
> possible?
>
> Second Question
> Assuming we can get the 'File Name' version of a dot SQL file to run how
> do I
> pass it parameters?
>
> Third Question - I've got a jillion more - but am being reserved :-) -
> Early
> days you see
> In QA I see the results - EG it may say 13000 rows updated etc
> Is it possible to see anything like that in ADP?
>
> Thanks for this
>
> ooooooooohhhhhh this is gonna be fun!!!!!!!
>
> Darren
> ------------------
>
> -----Original Message-----
> From: Arthur Fuller [mailto:fuller.artful at gmail.com]
> Sent: Wednesday, 20 June 2007 10:22 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] A2003: return records from Stored proc in SQL
>
> I consider myself one of the earliest adopters of this stuff and I've made
> a
> jillion mistakes in working with it (the essential characteristic of an
> expert LOL), so if you have questions -- and you will, for sure -- fire
> away.
>
> A.
>
>
> On 6/19/07, Darren D <darrend at nimble.com.au> wrote:
> >
> > Oh yeah!!!!!!!!!! - the possibilities - oh my gosh
> >
> > Many many thanks Arthur
> >
> > Have a great day
> >
> > Darren
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.472 / Virus Database: 269.9.1/854 - Release Date: 19/06/2007
> 1:12
> PM
>
>
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.5.472 / Virus Database: 269.9.1/854 - Release Date: 19/06/2007
> 1:12
> PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>