------------------------------------------------ FRC - Fast Record Counter for Access 97/2000/XP Version 1.0 ------------------------------------------------ Steve Nyberg - 9/29/2001, Updated October 27, 2002 email: stevenyberg@yahoo.com web: http://www.mile50.com Overview of FRC Fast Record Counter: ------------------------------------ FRC is a free Access utility that can be used to view counts of data in tables and queries. It contains a form that can be imported into your own Access 97/2000/XP databases. On this form, you select a table or query, then optionally fields to group by, then FRC will create a query that displays the record count for the unique field values from that source. This is designed to be a simple way to review the data in tables and queries, whether they are Access, SQL Server, Oracle, or other types of ODBC linked tables. Update from 0.9 to 1.0: ------------------------ This new version adds a tabbed control for selecting tables or queries. Requirements: ------------- 1. Unzipping utility to open the downloaded .zip file. WinZip or PKZip are good unzipping programs widely available on the web. 2. Access 97/2000/XP "MDB" file. FRC is not designed to work with the newer "ADP" format of Access 2000+. FRC has not been tested in the "Runtime" environment of Access, although my prediction is that it should work ok (with no query design ability, of course). 3. DAO (Microsoft Data Access Objects 3.51 or 3.6) reference. Please see section below about this. 4. If the database uses Access workgroup security, the user must have permission to create and modify queries in the database. Also the mdb file cannot be read-only. Installation: ------------- Its recommended that you first install and test FRC in a non-production copy of your database. Unzip the mdb file from the download of FRC. Open a test copy of your destination database. For Access 2000 and above: Verify that you have a reference to DAO 3.6. If not, you need to add one. See the section below for how to set a reference to DAO. For Access 97/2000/XP: From the database window:choose File-GetExternalData-Import from the menubar. Browse for your downloaded and unzipped copy of the FRC mdb file. Select the "Forms" tab, and highlight the form "z_FRC_frmMenu". Click OK and it will import the form. Immediately compile your application (to avoid a known bug in Access that can corrupt applications after code is imported). Using Fast Record Counter: --------------------------- Open the main FRC form. On the left, one tab shows a list of tables, another shows a list of "select" queries. Select a source object from one of the tabs, and field lists will display. Select 1 or more fields in order of how they should be grouped in the counting, starting from the left. (See notes below about which types of fields are recommended for use with FRC). You could also select no fields, which will create a query showing the record count for the entire source object. If you selected at least 1 field, then select a sort order. Click the "Fast Record Count" button to build and run the query. Close the query to return to the FRC menu. Click on a different source object to try a new query, or choose different fields to group by. The Clear buttons will remove fields from the grouping. Technical Notes: -------------------------------- CAUTION about using FRC against large databases: FRC has been tested with many field types in Access, SQL Server, and Oracle, and it has been tested again Oracle and SQL Server tables with millions of records. FRC may not work with some field types, and some fields should not be used for grouping for performance reasons. Examples are Memo fields, datetime stamps, currency, or long character fields in SQL/Oracle. Indexed or non-indexed fields can also have a big impact on performance. Some queries generated by FRC could be very slow to run (or even timeout after 60 seconds, the default for new queries in Access). If you are querying against non-Access tables, please contact your database administrator to determine which tables and fields should be avoided. Methods for Generating Unique Query Names: FRC creates and/or modifies the SQL of a query when you preview the record counts. There are 3 built-in options for how it should determine what query name to use. The default is a hard-coded query name. If you have a shared mdb file (more than 1 user simultaneously), or a shared mdb with full Access security (logins and passwords), then you may want to switch FRC to use one of the other methods. (Why change the method? In recent versions of Access, you might encounter weird locking problems if simultaneous users are trying to "edit" the SQL behind the same query name. These other options can be used to give each user their own query, or a random query name to make conflicts less likely. Also note the "Save Model" changes in Access 2000+ could mean long delays in using FRC - it may take a long time to save design changes in big Access 2000+ applications, even for simple changes like what FRC does to make a query. I'm not aware of workarounds to prevent this, so it could make FRC un-usable in large applications, especially where a big mdb file is shared by multiple users.) You should have basic familiarity with code in Access before attempting to change the method FRC uses to generate query names. To change the method, you'll need to make minor edits to the code behind the main FRC form. View the design of this form, then from the menubar choose View-Code. Near the top, find the function called "FRCQueryName". In here, you'll see notes on the 3 types of query name generation. Comment out, or uncomment the one method you want to use. When done, choose Debug-Compile to make sure your application compiles correctly. Close/Save then reopen your database. When you open the FRC form, you should see the temp query name using your new method to determine the name. Adding support for other types of queries: By default, FRC 1.0 only supports "select" queries as a data source on the query tab. If you want to display crosstab or pass-through queries, you will need to modify the FRC code to add the appropriate DAO QueryDef Type values. Other query limitations: FRC cannot display query names that contains a semi-colon ";" since that is the separator used in a listbox. Also, for databases with huge numbers of select queries, you may run overrun the number of characters allowed in the RowSource property of a listbox. If you encounter this problem, I'd recommend changing FRC to use a static table with a list of queries, or make a sql rowsource against the system table of objects. Contact me for more ideas on this. Setting a Reference to DAO - Microsoft Data Access Objects: --------------------------------------------------------------- FRC requires that your mdb include a "reference" to Microsoft Data Access Objects (DAO) version 3.6 or above. By default, Access 97 databases have this reference, and you won't need to verify or modify them to use FRC. Access 2000/XP databases do NOT have this reference included by default for new databases (They have an ADO reference instead). But Access 97 databases that were converted up to 2000 should have this reference. Confusing, I know! For Access 97: This step can be skipped. For Access 2000/XP: Here's how to verify and set the DAO reference in your Access 2000 or XP database. This should be done first on a test copy of your database, to be sure it doesn't affect other code. (Problems can be encountered if you have existing code that is not ADO or DAO specific. If you add DAO, then the code may not know which to use, and this could cause errors in compiling or running your database.) From the database window, right click and choose "Visual Basic Editor", or from the menubar choose Tools-Macro-Visual Basic Editor. Or you could also view the design of any module in your database. The Visual Basic window will open. From the menu, choose Tools-References. If you see a checked entry near the top called "Microsoft DAO 3.6 Object Library", then you already have the reference, and can compile the application, close the Visual Basic window and FRC should be ready to use. If you don't, then scroll way down to find and check the "Microsoft DAO 3.6 Object Library", or a higher version number than 3.6 if available. After checking this, click OK. Next, re-compile the application, from the menubar choose Debug-Compile "YourDatabaseNameHere". (If you encounter compile errors, you may have other bad code in your application, or you have code that does not like having both ADO and DAO references. If this is the case, you should probably delete the FRC form and uncheck the DAO reference.) After successfully re-compiling, close the Visual Basic window, and close Access 2000 and then reopen your application. Be sure to thoroughly test your application to verify that the new reference did not cause other problems.