Making Conversion Easy

Some time ago I wrote a comment here about the future automation story in Access will move toward Access Macros and away from VBA.   I am even more convinced of that than I was then – but I am still not convinced that this will play out in the Access 2010 time-frame – it will be stronger in Access 2012 (or whenever).

In the meantime I believe that the only way Access is going to “survive” (and I mean “survive”) is in pointing out why and how to move huge numbers of exisiting Access Client applications to what I call “Hybrid” status (although I have been told not to use that term but I like the idea of a single word to describe something rather than a whole paragraph :-)).  Without moving Access apps to SharePoint Access is dead – it will have no future either inside or outside Microsoft.

In 2010 any commercial software application HAS to have a Web story, and another iteration of Access without a serious one would be the end of the technology.  But IMHO it is in the migration of existing Access Client apps (even those using SQL Server or other linked backends) has got to be the activity that will not only allow Access to survive but also to thrive and position itself for future enhancements on the Web itself.  Without that then Access will be pushed to the curb before another version can be launched – it will be too late.

In this vein, this post will “top”what I think is the single largest wall to climb for the conversion of Access client apps to Access Services and SharePoint 2010 and that is cascading deletes on “text” primary keys that will simply NOT convert and that could break many existing conversions.

Numeric Primary Keys and Lookup Relationships

One of the things that many Access developers have done over the years is take advantage of Access’s acceptance of Text Prmary and Foreign Keys. The idea for this was that it would give the developer the ability to create Primary of Foreign Keys that are readable without the need to use Joins to attach readable descriptions to a Recordset.  There are lots of people who think that was a bad idea and I think they are probably right (I do NOT use this “Feature”) but there is no real problem with having used it if one isn’t worried about scaleability, upsizing and “purity” of design.

But in Access Services tables on SharePoint 2010 “Text” primary keys are simply not allowed.  Furthermore Text is not allowed in the keys being referenced in the Table Lookups that will allow for the creation of Referential data integity, Cascading Deletes and Updates.  So many, if not all, conversions from Acess Table backends to AccessServices Backends will be broken.

One way to manage this is to perform major surgery on your tables to create compliant “Lookups”, thereby breaking nearly all your queries, Forms and Reports and much of your VBA code throughout your application.  Not a happy prospect.

I have determined that the cost of this conversion will be a “deal-killer” for many “Hybridizations” and could be a major block to Access 2010 acceptance.  Instead I have determined that the solution that “should” solve this problem in many, many situations is to leave the tables and defacto relationships being used throughout the application in place but create a unique Numeric ID field as the Primary Key simply for good form and performance on every table without a numeric PK already.   This allows the developer to also leave the exisiting Queries, Forms, Reports and code intact.

But what about Performance and what about Cascading Deletes and updates that are SO handy?

Performance

The fact that the Access Services-based Client Access application actually lives in a local cache on the client machine all the time means that performance is unlikely to be negatively impacted by the loss of Referential Intergrity at the table level.  On the other hand with the addition of Indexes at the field-level you are likely to find the performance of this new application better than any previous application based on linked network connections to an MDB or ACCDB data source.

Cascading Deletes and Updates

Here is a scenario:

1. Table1 has a list of peope with a field called Alias that is Text and unique.  This could be the old Access Text Primary Key

2. Table 2 is the Many side of the relationship where the Foreign key is the Alias from Table1:

Here is a Data Access macro that deletes all the record of a “Child” table when the parent record on the “One” side is deleted:

 

This Macro is firing AFTER the deletion takes place.  This particular technique of cycling through the records of a Filtered subset of Table2 on the BeforeDelete Event and deleting them one at a time may seem clunky compared to a simple Action query cannot be run in the BeforeDelete event but it will get the job done.  It simply says for the macro to cycle through each record in Table2 where the value in the ALIAS field in Table2 is equal to the contents of the Alias field in Table at the time the Event was fired.   The “[Old]” keyword is used to refer to the value of the ALIAS field of the calling record – the one just deleted.  It is Old because it doesn’t exist anymore by the time this code runs – so it is “Old”.

The result is that the records that match the Alias of the parent record will be deleted in the child table.  If Table2 is left open when the deleton takes place this is the visual result until the window is closed:

The same concept could be applied for Updates and also to prevent Deletions if there is data in the child table – but frankly doing this after the delete might not be practical and might be best accomplished with a BeforeDelete Event macro.

There is no denying that this isn’t as “Pretty” as using a Lookup or the old Relationships, but this WILL work and WILL allow you to effectively leave your application pretty much  intact despite moving the data to Access Services Tables.  Because it sits at the Table level as a Data Macro this will run from wherever a deletion of the source record is called for with no more effort.

Conclusion

While all the new UI and Data macros added to Access 2010 can and will become useful as we start integrating Web-Ony features into our Client-side apps, and as we start building totally Web-based apps in the future, my TODAY involves getting exisiting apps up and running in this new enviroment and taking advantage of the cool new capabilities around deployment, maintenance, back up and especially the capability to include users anywhere in the World (literally) into my Access applications in real-time and just as easily as if the users were in the next office down the hall.