[AccessD] Access ADP file format

[Ryan W]

Paul,
 Thanks. I've been tinkering with this with a test table/procedure and
form.  I am not seeing any advantage thus far of setting the UniqueTable or
ResyncCommand properties, but I am not using any complex joins, which may
be more of what these are for?  I update a field, it shows instantly on the
form and when I move off the record entirely I can see the change on the
back end "per usual".

 I have seen with my current setup this is significantly less "chatty" than
the typical recordsource/rowsource bindings I'd normally use...(which
explains why it works better over WAN)




On Sun, Mar 26, 2023 at 7:58 PM Paul Wolstenholme <
Paul.W at industrialcontrol.co.nz> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>