DataWright Information Services
Consulting and Resources for Excel and Access
Access Resources
This is a page of resources that we hope you will use and enjoy. The resources will be added to over time, so please come back to check for new pages.
You are welcome to use the samples from this site, as long as you cite this site as the source. If you want help with implementing any of the concepts found here, please contact DataWright to discuss your requirements.
Working with Recordsets in VBA
If you want to manipulate or loop through records in code, Access requires you to create a recordset first. You can use a table or a query as the source of the recordset data. Here is a collection of samples to help you out.
Advanced queries
Once you have created queries and become comfortable with using calculations in Access queries, you can do a lot of useful stuff. Sometimes, however, you need some more techniques. This collection (some simple, some not so simple) will help you extend your toolbox.
Avoiding clashes in a booking system
At some stage you will probably need to build a booking system. Often you will need to find a way to avoid double-bookings; this article shows one approach.
Documenting your database
Documenting your database usually gets left to the last minute. The database documenter built into Access will create a report for you, but you get no control over the formatting and it's not particularly easy to sort or search the information. By adding some tools to do the job you can save yourself a lot of time, and create useful and relevant documentation for yourself and others to use. This series of articles will grow over time to provide some of that toolset for you.
- Documenting query names and SQL -- updated 29 Oct 2008
- Documenting query relationships
- Documenting table fields and indexes -- updated 21 Dec 2007
- Checking to see if a table exists
Listboxes and Combo boxes
Listboxes and Combo boxes are very versatile. You can use them to reduce data entry, to filter forms, queries and reports, and (in the case of listboxes) you can make multiple selections.
Here is a collection of tips for working with listboxes and combo boxes
- A menu for launching forms and reports
- Creating combo boxes
- Filtering a form with a combo box
- Using a combo box to search records in a form
- Creating dependent (cascading) combo boxes Updated 11 Nov 2009
- Allowing users to select any item from a dependent combo box
- Dependent combo boxes on a continuous form
- Many-to-many data entry using a pop-up form
- Adding new items to a combo box
- Adding items to a value list: Using a callback fuction
- Adding items to a value list: Using the AddItem method
- Adding items to a value list: Manipulating the RowSource as a string
- Updating the RowSource: Adding a new record to a table
Database Normalization
If you build, or inherit, a database with the wrong structure, you will find yourself fighting your data to get useful reports. This section has articles that will help you to design your tables correctly, and reorganise tables into better structures.
General Tips
- Creating an MDE version of your database
- Setting a reference to a code library in the VBE
- Booting users out of the database -- updated 31 Jan 2009
- Converting Access macros to VBA code
- Processing text files using the FileSystemObject
- Data types in DAO
- Data types in ADOX
- Data types in SQL Data Definition Language (DDL)
- Recovering a corrupt database table
- Relinking to a password-protected back end database
- Recovering a corrupted form or report
Access VBA: Working with events
Logging users in your database
Importing and exporting files using Access
Often you will find yourself importing text files or Excel worksheets into Access. If you only do a few a week, you can consider doing the process manually. But if you import multiple files at a time, some automation is handy. Take a look at these tips.
Working With Forms
- Creating Basic Forms
- Creating And Linking Subforms
- Using Tabbed Forms
- Calculations in Forms
- Speedier Forms: How To Improve Performance
- Controlling What The User Sees: Hiding And Showing Controls
- Using Controls: Add-To Combo Boxes
- Using Controls: Data Validation
- Using Controls: List Boxes
Working With Reports
- Using The Wizard
- Understanding Report Sections
- Printing Reports: Moving Controls Around At Print Time
- Printing Reports: Conditional Formatting
- Printing Reports: Variable Height Controls with Background Colors
- Printing Reports: Creating Page Totals
- Filtering Reports: Filtering Reports Using Values on Forms
- Creating a Report Picker
Working With Dates
- Calculate Working Days Using International Date Formats
- Using international date formats in SQL expressions
- Fix scheduling inconsistencies with the DblToDate function
- Cleaning up date formats in exported csv files
