Nearly all the code is in class modules, so it doesn't collide with your existing modules. All the libraries are accessed as methods of
the 'xf' object (think of it like the Access 'application' object), so you get intellisense along the way.
Many of the small number of public global methods are named with the 'xf_' prefix to further decrease the risk of collision.
In fact, it might be a problem that not enough of the framework's methods are available in the global space (so you can use them in SQL
or in Control Source properties). Don't worry, you can expose them in a controlled manner with you own custom module!
Standard configuration also gives you a Control Panel form to make managing feature settings and using the framework tools a no-brainer.
Easy to Install and Configure
The framework has a handy setup form that imports and configures the rest of the framework - you don't have to think about a thing. It can also completely
remove itself (handy when upgrading to a later version of the framework).
If you tick the Control Centre option of hiding all the framework objects, you might even forget it was there if it didn't leave the base module
visible as a reminder!
The framework is completely 64-bit-Office compatible, and is distribute as an unlocked MDB file, meaning zero integration and deployment hassles
with new or existing projects.
The framework table linker supports any number of ODBC or Access back ends and has three linking modes for Access back ends:
- Local - front and back end in the same location
- Fixed - back end fixed location
- Follow - follow the path of another back end
Linked table information is saved in a local table so that even if all linked tables are removed they can be recreated.
The linker is integrated with the front end update process so that linked tables don't need to be configured for the production
environment before releasing an update.
Automated Front End Updates
The framework can monitor a designated network folder for newer versions of the application, and automatically update to that version.
Linked back end information is copied to the new front end as part of the update so that the new front end may re-link to the
production environment on startup.
Augmenting built-in SQL with a pre-processor
The pre-processor screens the SQL script and where enhanced syntax is used, implements the commands using direct DAO or ADO manipulation.
Non-enhanced commands are passed direct to the SQL engine.
This allows the processor to both compensate for the well-documented deficiencies of built in DDL, and offer powerful enhancements to deal with native
Access field properties.
- multi-command scripts (commands separated by GO, like Transact-SQL)
- comment handling
- 'Allow Empty String' handling in SQL (using the NULLSTR and NOT NULLSTR keywords)
- SET and DROP all field or table properties like Caption, Unicode Compression, Default Value, Validation Rule, etc
- enhanced ALTER COLUMN functionality (including the ability to change the field/column name)
- IFEXISTS enhancement to DROP statement
- binary update command
Script Generation Tools
The Control Centre offers script generation tools, like MSSQL Management Studio, to create augmented SQL scripts for DDL (schema)
and DML (data). It also offers a script editing window and a script execution tool, allowing you to run a script against
any database anywhere.
Automated Back End Schema Updates using Enhanced SQL Scripts
Back end databases are assigned a major and minor version number, and the front end can be used to deliver and automatically
execute scripts to update the back end to the latest version.
Each script updates a particular back end from one version number to the next, so it's not a problem for the update tool
to update several back ends and many version levels.
A Utility Belt of String, Escaping, Control-Related, and Other Functions
Intellisense-navigable function libraries:
- most of the .NET/Java string functions, plus more
- identical string libraries in case-sensitive and non-case-sensitive versions
- control-centred library handling common form control scenarios
- 'interaction' library offering user interaction and OS/filesystem interaction
- general purpose escaping, value conversion/detection library
- database 'brain dump' for analysing data quality in the Foreign Key relationships in that database you've just been handed
- search and/or replace in form/report control properties
Powerful New Classes
Enhances and replaces the built in VBA Collection class. The dictionary allows iteration through the keys as well as the values, and
has a lot of checks and balances and some debugging built in to make it less 'bare metal' than the VBA Collection.
Encapsulates a database connection and provides methods for data to be extracted from it, whether DAO or ADO, local or remote.
This class is used by the framework to pass to methods as
a generic database wrapper, rather than having to have DAO/ADO versions of everything.
But it also has a lot of handy functions, including
- RunSQL (using the dbs.Execute method rather than DoCmd)
- ReturnRec (like DLookup but takes a full SQL command)
- returning a row or key/value pairs for many rows as an xf_Dictionary
- returning results as delimited lists
- date escaping, SQL formatting and range checking (eg. MSSQL ShortDatetime)
The default xf_DbConnect object is xf.DbConnect, which always refers to the current database.
xf_DataList and xf_DataListExt
The DataList is like an in-memory RecordSet on steroids.The DataList and Extended DataList (DataListExt) allow
- manual init and population, or reading data from a database with an SQL statement
- accessing data by row and column number
- multi column sorting
- multi column filtering, with additive or subtractive results to simulate OR criteria
- grouping with aggregate functions
- hashed lookup of column values
- export row or column data as delimited list
These objects are great for creating complex processing logic in VBA, given all the tools they bring with them.
But the xf_DataList and xf_DataListExt objects can also be used as a datasource for ListBox and ComboBox controls, allowing
innovative and lightning fast multi column search and filter screens to be created.
A class to cache SQL result sets. It returns a DataList object, fetching it from memory using the SQL as a key if it has seen the SQL before,
or reading it from the database and storing it if not.