strive4peace

If you like my tools, please donate so I can keep sharing what I build
thank you
Access Consultant

Over the years, I have written a lot of code for Access. Here are some of my favorites. I demonstrate these as a free presentation to user groups, companies, and organizations ... in person or remotely ~ so wherever in the world there is an audience, I can be there ~ contact me for details,

Free Tools for Microsoft Access on Channel 9 (MSDN)
Video Presentation about free tools and resources available for Microsoft Access to help you be amazing
Microsoft MVP
Access Basics Video Tutorials Learn VBA ListFiles Database Random Picker Error Handling Reference Database Whistles & Bells Help Examples crystal's Analyzer Code Documenter

Free Tools and Resources for Microsoft Access
by crystal



Contact Template for Microsoft Access

videos

Access Contact Template, Contact Management System (CMS) (cc)
watch video on YouTube
learn how to take advantage of the most powerful free template you can download for Microsoft Access

Tracking Projects, Tasks, and Time with Contact Template for Microsoft Access (cc)
watch video on YouTube
Keep track of projects, tasks, and time spent on projects

download

free Contact Template for Access including Project Management and Common Business Needs
  • Names, Addresses, Phone Numbers, eMail addresses
  • Maps
  • Lists and Members
  • Company contacts
  • Projects, tasks, and times
  • Notes
  • Attachments
  • Birthdays
  • User management


Access Documentation Tools


My Favorite Power Tools

free Analyzer for Microsoft Access (latest on CodePlex work with Access 2007 and up. 2000-2003 is on my website and you'll have to email me for the 97 version)

The Analyzer is the most important tool in my kit. Now, it is posted on CodePlex and the source code is available for non-commercial purposes. Data structure is the most important thing in Access to get right. I help others create new databases and redesign existing databases. Quickly understanding what is built saves time. Along with the relationships diagram, the Analyzer provides a way to really see what is going on, document, and identify deficiencies in the data structure.

All versions give you these reports:

  • Table Summary Table Names, Connection information, # Records and Fields, Autonumber fieldname, Date Created and Modified
  • Deep Analysis Data Dictionary: Field Name, Data Type, Size, and much more
  • Field List Alphabetical listing of all fields in the database. Easily see which tables contain fields with the same name.
  • Relationships All Relationships in the database, including those that are hidden.
  • Table Indexes Visible Table Indexes as well as hidden indexes created by Access to enforce referential integrity on relationships.
There are many more reports! If you are coming to one of my live presentations and want help interpreting the Analyzer results, I will make time for you. Save the 5 basic reports in PDF format and put them on a USB stick or your laptop. To do this, while you are in Print Preview, right-click on the report and choose Export, PDF.

Code Documenter (available for Access 2007 and up -- works with Word, Excel, and PowerPoint files too)

Document Modules ... Procedures ... and Lines of Code ... in your Access databases, Excel workbooks, Word documents, and PowerPoint presentations. My Code Documenter puts all your code in one place and gives you tools to compare it. Get code out of old databases before you move to a version that can't open them anymore.

See:

  • Objects Names of Tables, Queries, Formas, Macros, Modules, and more.
  • Modules Module names and types, number of procedures and lines.
  • Procedures Procedure names and number of lines. Sort by name to see where you have duplicates.
  • Code Lines Code can be shown as it was written ... it can also be filtered and sorted.
To make procedures and modules easy to find and incorporate into other projects, output all code to individual files (one of the report choices) and use Everything to find them.

Everything is free and amazingly fast for finding files once it builds the initial database. In fact, I can usually find a file faster with Everything than I can navigating to where it is even when I know the exact location. For instance, if you are looking for a BAS file with 'Properties' in the name, you can type:
bas prop
prop is probably enough to find Properties
As you type, separate what to look for with spaces. You can also use wildcards. Case doesn't matter.

To Copy Path to Clipboard, or the Copy Full Name to Clipboard, right-click the file in Everything, and choose from the shortcut menu to copy and then paste wherever you like. Double-click on a file to open it, or right-click and Open With. You can also drag files, or ctrl-drag (to make a copy) from Everything to a folder.

Document Query SQL, Form and Report RecordSources. Make Objects Query (Access 2000 and up)

Document the SQL stored for each Query; Document RecordSources for Forms and Reports. You can also run a procedure create a query that lists the main object names and types in your database.

Generate:

  • Queries Query Names and SQL to a nicely formatted Word document.
  • Forms Form Names and SQL, Tablename, or Queryname for the RecordSource to a Word document.
  • Reports Report Names and SQL, Tablename, or Queryname for the RecordSource to a Word document.
  • Objects Query Query that lists the Table, Query, Form, Report, Macro, and Module Names and Types in your database

useful utilities

Data Dictionary, Display Control (Access 2007 and up)

Import the DataDICTIONARY_DisplayControl form and module into a working database, compile and save, then Open the form: f_DataDICTIONARY_DisplayControl
  • View Data Dictionary for selected table
  • go to Table Design view of selected table
  • open table Datasheet View of selected table
  • Rename selected table
  • See if there are text or memo fields where Unicode Compression is not set
  • see an estimate of record width (sum of the data type sizes, taking compression into account)
  • Change Display Control of selected fields:
    1. Combo and Listbox to Textbox - View and Copy SQL Statement before changing
    2. Integer to Checkbox

Show Fieldnames, Data Types, Sizes, and Descriptions for Tables (all versions)

This code was originally written by Duane Hookom, then modified by me to show additional information. This is great for running on one or more tables when you want to share, or document, the basic design of a table. Make what you are talking about easier to see on a thread so that other posters can follow better.

Module:

  • mod_ShowFields_Duane_Crystal_121114.bas Code to document one or all table structures to the debug (Immediate) window. Handy for communicating on public forums because table structure can be pasted into a post. Documents tablename, fieldname, data type, size, field description.



Charts, Graphs, Dashboards

videos

do you want to know how to do it?

How To Make a Graph with Microsoft Access (cc)

Create chart, change what data is displayed including formatting, chart type, titles and legend

do you want to see how to create charts using criteria selected from a menu?

Tool and Code: Manipulate Graphs in Microsoft Access using VBA (cc)

20-minute video shows how to drive charts from a menu form that collects criteria, change data for the graph, set the chart title, x-axis title, and modify scales and formats.

download

Download Chart Menu Database

Download sample Access database so you can learn how to build chart menus that let user specify criteria for a chart.


Calendar Forms


Popup Calendar (Access 2007 and up. Link to 2000 version at bottom of page)

Popup a full-featured date picker that allows you to easily change year, month, and day. Also quickly set the date plus or minus a week, quarter, 6 months, 10 years, or a specified number of days. Color-coded for days of the week. Also collects TIME if desired

Form:

  • f_PopupCalendar All code needed to implement this form, read the value in the active control and write it back is behind the form.

Calendar Subform(s) (Access 2007 and up)

Put a calendar on a form as a subform -- or maybe more than one! This example uses one subform multiple times. This subform is modified from my Popup Calendar

Form:

  • f_Calendar_sub All code to implement the calendar as a subform is in the code behind the form. Customize the Update_ExternalForms procedure. Read comments in the code and web page for instructions on hooking in.


Calendar Report


Calendar Report in HTML format (Access 2000 and up)

Create a useful calendar that can be emailed or posted that can be read by anyone with a browser! Use data from any table or query with dates and descriptions.

Modules:

  • Crystal_GenerateHTML (always needed) — Code to generate web page with calendar report
  • CrystalCode_runSQL (always needed) — Helper code to execute SQL statements
  • crystal_code (needed for Form interface) — If form is being used to create calendar, this code is needed.

Form:

  • REPORTMENU Form interface to generate calendar -- serves as an example

Tables:

  • usys_Colors Color codes for form interface
  • usys_defaults Default values for form interface; version informaiton.

Other Tables and Queries:

  • various not needed, used as examples

HTML Calendar Simplified

special version of the code that is easy to call and use

-- ask me for the code to make it this easy
   Call Create_SimpleHTMLCalendar(sPathFile, sSql)
   Application.FollowHyperlink sPathFile
            


Excel


Delete Empty Columns that have no data after optional label rows (can be called by Access too)

Delete columns that are completely empty of data -- useful after exporting a big query to Excel and there is a header row that needs to be skipped.

Random Picker

Pick one or more random values from any column of data on a worksheet

Sort a Range (can be called by Access too)

Sort a range in Excel by columns or rows. Specify worksheet, row numbers, and column letters for easy calling from code.


Automation


Automation

Automation with Excel, Word, PowerPoint, and Outlook.


Tools by Others


Allen Browne's Issue Checker

"The utility is intended for developers to check their application during design, and consultants who examine others' databases."

Wayne Phillips' Accessing detailed file information provided by the Operating System

File Properties Viewer -- find out generic properties about a file such as the duration length of an MP3 file, or the date a JPEG picture file was taken on a digital camera

Bill Mosca's Properties Setter

Startup wizard that lets you connect to an Access ACCDB, ACCDE, MDB, MDE or MDA and see the database settings, startup form and whether or not it has an AutoExec Macro.

Vincent Billard's V-Tools

Handy developer tools including Total Search

Thomas Moeller's TM VBA-Inspector

Browse the entire VBA code of an application for potential errors. Thomas uses the Microsoft Translator on his pages.

Stephen Lebans's Downloads

Access downloads from the amazing mind of Stephen Lebans

Gunter Avenius' Ribbon Editor

Create and Edit Ribbons using a visual interface

Carlos Quintero's MZ-Tools

Developer tools to save you time


Contact Me


Thank you for all the nice emails telling me how you are using what I share. If you want me to connect to you and help you build your project, contact me strive4peace2011 then @ then yahoo then dot com


Copyright © 2013-2016 crystal long | Last updated August 2016