Re: Off Topic - Access to Postgres

On Wed, 28 Dec 2005 07:07:28 -0800, Steve Jorgensen <nospam@xxxxxxxxxxxxx>
wrote:

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

....More...

Another problem with pass-throughs:
- Since you're embedding parameters in SQL text yourself, you are now
responsible for doing all the things a query parameter would normally handle
for you behind the scenes like the difference between a value and Null, what
to do with strings that contain quote marks and other special characters, how
the server expects dates values to be delimited, etc."

More issues with server-side objects in general:
- To the extent that your application relies on server-side objects, you have
now hitched your train to that server. You can no longer demo your app using
an MDB back-end nor switch to a different server back-end without some amount
of rewriting. If you can keep all the queries in Access, you're not stuck.

Personally, I always end up using a few stored procedures and views with
Access, but I use them as a surgical tool when necessary, and not just because
they're supposed to be "better" than using Access queries.

There are situations where doing -everything- with stored procedures does make
sense such as when there will be more than one front-end, but Access just
doesn't make the friendliest front-end to those applications for reasons
described above. Also, most such applications would be better designed as
3-tier rather than relying on server-side objects, and for 3-tier
applications, Access is almost no use whatsoever.
.

Relevant Pages

  • Re: Off Topic - Access to Postgres
    ... If sprocs and pass through queries are not the solution then ... >However, I find that using views, stored procedures and pass through queries ... The reason pass-through queries are a problem for Access (and stored ... may have to create a saved pass-through query, ...
    (comp.databases.ms-access)
  • 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)
  • 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: 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)