[AccessD] Access ADP file format

[Paul Wolstenholme]

Ryan,

The important parts of the VBA code to refilter the form are:
    Dim rstTemp As ADODB.Recordset
    Set rstTemp = FilterRecordSet
    Set Me.Recordset = rstTemp
    Me.UniqueTable = "tO150DeliveryOrder"
    Me.ResyncCommand = "EXEC fO150DeliveryOrders_usp ?"
    Set rstTemp = rstTemp.NextRecordset
    ' Use the 2nd recordset returned by the stored procedure as the combo
box source
    Set Me.nCustomerFilter.Recordset = rstTemp
    ...
    rstTemp.Close       ' Note: The prior result sets are NOT closed.
    Set rstTemp = Nothing

The function FilterRecordSet is written to assemble the T-SQL call of the
stored procedure with values of any parameters you want to pass it and then
open it using a ADODB connection (one connection which is kept open for all
calls until usage fails).  It returns an ADODB.recordset (which actually
supports a series of recordsets in the case where the stored procedure has
more than one SELECT statement following each other).

In answer to your question, Yes, you can create and edit data in the form
with this.  Data is written back through the recordset that the SQL Server
stored procedure has passed to Access (possibly with the help of
UniqueTable which isn't always needed).
BUT Access then needs to read back from the database to update how it
should show the altered record on the form.  The ResyncCommand specifies
the command to read this.  To do that, some prefer to write a SELECT
statement with fields exactly matching those returned by the stored
procedure.  As shown, I prefer to re-use the same stored procedure and
ensure it returns only one record in one resultset when called this way.

Other than a trick in each form to handle multiple record deletion, that's
about it.

The best part of this is the ability of the SQL Server's stored procedures
to handle any complexity of query I care to imagine AND to dutifully keep
my copious comments explaining it all.

Be warned that Access versions from recent years have a bug affecting the
display of the previous record while inserting a record on a continuous
form.  David suggests opening a data entry form to avoid showing this
disconcerting behaviour.
You could interpret this as a bad omen suggesting this legacy functionality
should be relied upon at your peril.

Paul Wolstenholme


On Mon, 27 Mar 2023 at 12:45, Ryan W <wrwehler at gmail.com> wrote:

> Paul,
>
> Can you give me an abbreviated version of how that works? I assume you
> can’t write back data without custom code because of the stored procedure ?
>
> Sent from my iPhone
>
> > On Mar 26, 2023, at 4:07 PM, Paul Wolstenholme <
> Paul.W at industrialcontrol.co.nz> wrote:
> >
> > Stuart,
> >
> > Thanks for that reference.  The bit I like is:
> > "But you can do almost all the things that were essential to ADPs with
> VBA
> > code. E.g. you can still bind a form to an *ADODB.Recordset* created from
> > an SQL-Server stored procedure via an OleDb-Connection."
> > I'm thankful to David Emerson for introducing me to that possibility,
> > helping me to master the technique and for introducing me to this group.
> > Yes there is more code involved but form loading is so much faster (70 x
> in
> > my experience when packets travel 5000km between the front end and back
> > end ).
> >
> > Paul Wolstenholme
> >
> >
> >> On Sat, 25 Mar 2023 at 01:51, Stuart McLachlan <stuart at lexacorp.com.pg>
> >> wrote:
> >>
> >>> On 24 Mar 2023 at 7:34, Arthur Fuller wrote:
> >>>
> >>> I digress. Why did the Access team abandon the ADP file format?
> >>>
> >> A clue here:
> >> https://codekabinett.com/rdumps.php?Lang=2&targetDoc=access-adp-history
> >>
> >> Finally, with Access 2013, Microsoft discontinued ADPs and removed
> support
> >> for
> >> them from Access.
> >> I do not have any inside information on the reasoning and the process
> >> behind this
> >> decision. From my outside point of view, I guess the rationale was a
> >> combination of
> >> three things.
> >> *   The adoption of ADPs was not as strong as Microsoft had hoped.
> Mainly
> >> due
> >>    to the flaws and problems outlined above.
> >> *   The pressure of keeping up with the pretty fast SQL-Server
> development
> >> was
> >>    probably pretty intense for the Access-Team.
> >> *   The OleDb technology was deprecated and support for it was to be
> >>    discontinued. This would have removed the very basis ADPs were built
> >> on.
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> https://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >>
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > https://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>