Useful Microsoft Office Functions
Download File Description

NEW 28-Nov-03

ADO Version of Access Table Connection Routines

 These routines serve the same function as the older version described below.  This set is build for Access 2000 and uses ActiveX Data Objects (ADO) rather than Data Access Objects (DAO).  For more detail, see: Steve Goodhall’s Connection Routines for Access 2000
Access Security Test Bed The files in this distribution are a test bed for automated application of Microsoft Access Security. 

After the application is run, the target databases will be secured so that:

  1. Only the designated Administrative ID can update objects.
  2. Only the designated User ID and the designated Administrative ID can execute the application
It will not be possible to bypass the administrative security by using a different MDW file with an unprotected Admin ID.  The Admin ID is removed from the Admins group.  All privileges are removed for the Admin Id for all objects.  The ownership of all objects is changed to the designated Administrative ID.
ODBC Access to an Excel Workbook This archive contains a demonstration of the use of ODBC and ADODB to read an Excel workbook. This example is implemented using Excel, but the coding techniques can be used in Word or Access 
Included files are:
ODBCSource.xls - source of date
ODBCCode.xls - code example
readme.txt - description of demonstration
Test Referential Integrity for Optional Fields This database has been updated to incorporate the response to another thread on the Access Developer listserv about creating relationships with program code.
This database is posted as part of the response to a thread on the Access Developer listserv about using Referential Integrity on Foreign Keys where a null value is permitted in the referencing table.
Access Table Connection Routines These routines dynamically connect Back End Tables to a Front End database containing Forms, Reports, Modules, etc.
  • These routines were designed to reconnect to the BE every time the FE is opened.  This is probably not very efficient, but it is very safe.  In the next version, I plan to check the connections before reconnecting. 
  • By default, the routines connect to all tables in any database in the same directory as the FE.  If the BE is stored on a shared drive and the FE is on a local drive, use the SJGConnect.ini file.  If the file SJGConnect.ini exists, the directory name from that file is used.  At this time, only one directory is supported.
  • If the same table name is found in more than one BE, the first one encountered is used and a message box is displayed.  this is shown in the test cases.
  • The macro, SJGReconnect runs both the disconnect and reconnect routines with a display of what is being done.  To eliminate the display, refrain from opening the form before calling the routine.  The idea of the macro is to include it in the Autoexec macro so that it runs each time the FE is opened.  You can run this macro to test or demonstrate the routines.
Access Version Control Routines These routines support automatic updating of a Front End (Code Only) database.
  • The routines are implemented as VBScript programs executed under the Windows Scripting Host.  
  • The download file includes test databases and batch files to test each script.
  • For detailed documentation, click here.
Test Image Control This database is posted here for a person on the Access Developer listserv who tried my suggestion about displaying blobs (specifically images) and could not get it to work.
Test Correlated Subquery This database is posted as part of the response to a thread on the Access Developer listserv about grouping TOP n rows by a foreign key using correlated subqueries.