Re: Off Topic - Access to Postgres

On Wed, 28 Dec 2005 14:02:58 GMT, "Randy Harris" <randy@xxxxxxxxxxxx> wrote:

>"Hank" <hankrunner@xxxxxxx> wrote in message
>news:1135769846.716409.189180@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>>
>> Steve Jorgensen wrote:
>> > On 27 Dec 2005 18:00:46 -0800, "Hank" <hankrunner@xxxxxxx> wrote:
>> >
>> > > We have just recently migrated the data from our Access 2000
>> > >backend to Postgres. All forms and reports seem to run correctly but,
>> > >in many cases, very slowly. We do not want to switch over until we
>> > >can speed things up. We would like to start implementing Stored
>> > >Procedures so we can do Server-Side processing.
>> > > Can anyone recommend a book that would help us learn how to
>> > >use sprocs or pass-through queries? I apologize if my terminology is
>> > >incorrect. I am in the position of not quite knowing how to ask the
>> > >right questions.
>> > >Thanks,
>> > >Hank Reed
>> >
>> > When you have a speed problem with client/server, using pass-through
>queries
>> > and stored procedures is not usually the best answer, and may not help
>at all.
>> > Doing that is, however, a good way to make your program 4 or 5 times
>harder to
>> > develop and maintain.
>> >
>> Steve,
>> Thanks for the advice. I have always used PKs in every
>> table. My novice understanding of a database like Postgres is that we
>> would ONLY get the filtered records. I have been led to believe (or
>> convinced myself) that a stored procedure would select and return only
>> the records of interest, thus saving bandwidth and transfer time, in
>> general. If sprocs and pass through queries are not the solution then
>> what would you recomend?
>> Thanks again,
>> Hank Reed
>
>
>Frankly, I'm a bit puzzled by that portion of Steve's comments. I certainly
>agree with the part about not returning unfiltered, unaggregated results.
>However, I find that using views, stored procedures and pass through queries
>are among the most effective ways of accomplishing that.

The problem is that Access was never designed to work very well with stored
procedures and pass-through queries. Views can be used effectively in
combination with Access queries that add filtering, but make sure to have a
virtual primary key on the view, and specify the "key" when making the link.
Also, with PostgreSQL, the view will not be editable unless you define
"instead of" triggers to make that happen. That's not a problem, it's just
something you need to know and do.

The reason pass-through queries are a problem for Access (and stored
procedures can only be called via pass-through queries) is that Access cannot
implicitly pass arguments to them, nor edit the records they return. This
means that for a single case, instead of just writing one parameter query, you
may have to create a saved pass-through query, and rewrite its definition from
code before each time you use it, and you may furthermore have to add another
add/edit form to add or edit records since you can't edit directly through the
bound form. Now, realize that you'll have to requery the viewing form after
each of these operations to refresh the data. Be careful about that saved
pass-through query. Are you using it from only one place? Are you making
sure only one instance of one copy of the front-end will be used at a time?

Frankly, you just have to use Access in a much less Access-like way and do a
lot more work as soon as you employ stored procedures if they will be used
behind forms, reports, or combo/list controls.
.

Relevant Pages

  • Re: Access migration to SQL Server
    ... caveat in the first sentence, i.e. remove access all together and you don't ... > about having to convert all your queries to views and stored procedures. ... > using a named query parameter to look up a value from a form control and ...
    (microsoft.public.access.conversion)
  • Re: Access migration to SQL Server
    ... caveat in the first sentence, i.e. remove access all together and you don't ... > about having to convert all your queries to views and stored procedures. ... > using a named query parameter to look up a value from a form control and ...
    (microsoft.public.sqlserver.server)
  • Re: Off Topic - Access to Postgres
    ... >>However, I find that using views, stored procedures and pass through queries ... >procedures and pass-through queries. ... >The reason pass-through queries are a problem for Access (and stored ... - To the extent that your application relies on server-side objects, ...
    (comp.databases.ms-access)
  • Re: queries Vs Stored Procs
    ... > queries in one stored procedure rule. ... > should put each branch into its own stored procedures. ... to have a chain of Procs rather than a chain of queries. ... a single procedure can execute and use the results from several ...
    (microsoft.public.sqlserver.programming)
  • Re: sql 2k stored procedure as source for list box
    ... I use pass thru queries all the time. ... Brain fart city! ... about technique for using SQL stored procedures with an Access frontend: ... > procedures called through pass-through queries. ...
    (microsoft.public.access.forms)