Code Snippets
+ Tips
Tips To Help You With Microsoft Access
Use SaveAsText to Edit a Macro in Microsoft Access
When you have a Microsoft Access database with a lot of macros, it can be a challenge to find all the places where an object is being used in the macros and replace it with a different object. These VBA functions will allow you to replace a value in a particular...
Delete Attachments from a Microsoft Access Table
You may store file attachments in Microsoft Access by utilizing the Attachment field in table design. Unlike other field types, the Value property of the field is actually a recordset of the attached files. These VBA functions allow you to delete the attachments by...
Determine the Default Email Client
Microsoft Access can automate sending emails, setting appointments, etc. in Microsoft Outlook. However, if the user has a different email client specified as their default, you may need to make adjustments in your logic. This VBA function returns the name, or...
Send the Values of Microsoft Access Query Properties to a Table
Trying to find out which queries in your Microsoft Access database are pass-through queries, which ones have parameters, or which ones have crosstab headings specified? This VBA function sends the values of 42 different query properties and attributes to a table. It...
Save an Excel File as a Tab Delimited Text File
Microsoft Access provides several ways to import the data from an Excel file. Sometimes, the way the Excel data was entered can create problems during the import. This VBA function will allow you to automatically save an Excel file to a tab delimited text file. ...
Use VBA to Close all Microsoft Access Objects of a Particular Type
Pass an object type to this VBA function, and it will automatically close all of the open objects of that type. It loops through an object collection, like AllForms, and examines the IsLoaded property of each AccessObject in the collection. For example, if you are...
Create a Folder if it is Missing
This VBA function uses the FileSystemObject to create a folder if it does not already exist. If the folder is several subfolders deep, it creates all of the subfolders as well. As an example, you could call this function to create an employee folder on the network...
Copy a Set of Folders from a Master Folder to Other Folders
This VBA function copies a set of subfolders from a master folder, and then adds those subfolders to all of the folders in a different location. For example, let’s say that you already have a folder for each employee under the Employees folder on the network. But...
Set and Retrieve User Settings in Microsoft Access
Microsoft Access database systems are structured so that each user has a dedicated front-end file installed on their computer. The front-end is the user interface for the system: data entry forms, logic, and reports. Along with reducing the opportunity for data...
Use VBA to Combine Multiple Excel Files into One Workbook
Microsoft Access to Microsoft Excel Microsoft Access provides several ways to export tables and queries to Microsoft Excel. Each object that gets exported creates one Excel file with a single worksheet. You can use the VBA function below to combine multiple files...
Change a Microsoft Access Color Value to a Number
Many controls used on a Microsoft Access form or report have color properties, like Back Color or Fore Color. Selecting a color value is easy – just use the color picker in the Property Sheet for the control. When you choose a custom color, the color value that...
Limitations of a Microsoft Access Database System
Microsoft Access is a powerful database platform, and perfect for many small and mid-size business applications. However, it does have some limitations. Below is a list of five things to consider before investing in a Microsoft Access system. There is a learning...
When to Use a Microsoft Access Database System
Microsoft Access does a masterful job at managing business data. Extra Mile Data has worked with scores of different companies across dozens of industries that use Microsoft Access to save time and make money. Below are five selling points for using a Microsoft...
Count Selected Check Boxes on a Microsoft Access Form
The VBA functions below can be used to count, and to clear, all of the selected check box controls on a Microsoft Access form. For example, a criteria form may present dozens of report options using check boxes, and each selected checkbox will open a report when a...
Build a Microsoft Access LIKE Statement from Lists
Microsoft Access SQL provides a Like operator that can be used in queries to compare values to a pattern. For example, let’s say that you have an Employee table with FirstName and Middle fields, and you would like to find all records that have “Bob” or “Robert” (or...
Change All Blanks in a Microsoft Access Table to Null
Microsoft Excel data is rarely managed to prevent a user from leaving blanks (just spaces or a zero-length string) in worksheet cells. When the Excel worksheet is imported into a Microsoft Access table, the blanks come with it. Those blanks may cause problems later...
Convert the Switch Function to a TRANSACT-SQL CASE Expression
Switch In Microsoft Access, there is a Switch function that can be used in SQL statements to evaluate a list of expressions and return a value. For example: Switch([BrakePad] = "1","OK",[BrakePad]="2","Needs Adj.",[BrakePad]="3","Bad") As Brake The expressions come...
Fill a Table with Control Info from All Forms and Reports
During the design process in a Microsoft Access database, you may need to find where a particular label Caption is being used. Or perhaps you need to compare the Row Source of combo boxes across multiple forms. The VBA functions below provide a way to capture some...
List Ascii Codes for All Characters in a Value
When manually copying data from a document and pasting into a Microsoft Access data entry form, it is possible to accidentally include unwanted characters, like Tab or other hidden characters. This may make the data hard to retrieve in a query, and a Find or Search...
Automatically Add Controls to a Form Using Table Fields
In a recent project, I needed to create a Microsoft Access datasheet form based on a table with a lot of fields. If I used the form wizard, there would be more text boxes than would easily fit on the form. The manual solution was to drag a set of fields from the...
What Customers are Saying about Extra Mile Data
Andrew H. Huffine
Ridge Funeral Home & Cremation Service, LLC
"Carl and Extra Mile Data was the easiest and quickest way to solve my MS Access problems. Our database is running properly, efficiently and faster after Carl made the necessary changes to fit our needs. He also taught me how to make any updates in the future making me more confident with our database. Extra Mile Data is highly recommended!"
Lynn Coleman-Kirk
Signature Companies
"Carl at Extra Mile Data has been extraordinarily helpful to our companies. He applied his vast knowledge and patience to match to solving all of our complex database needs that we were unable to handle ourselves. He and his company were a tremendous find and will be used again and again as needed."
Normand Menard, RPFT C-Tech
Upland Environmental Ltd
"I was looking for a database solution for my business and I'm glad I found Extra Mile Data. Carl was able to get me up and running with a database in no time at all and within budget. He has since helped me build this database into a very effective and efficient tool I use daily for my projects. I've very much enjoyed working with Carl and I will certainly continue to use him for any database programming in the future. 'Thanks Carl!'"
Jim Lipko, IT Administrator
The P.A. Hutchison Company
"My project “MS Access Salesperson Database” was at a standstill and then I found Extra Mile Data. With the professional help given to me by Carl my project was completed on time and on budget. Carl not only explained the programming as he went along, but he also showed me how I could add coding as well to achieve my desired results (Truly an Extra Mile). The database is an integral part of the day to day operations for our sales force and their supervisor."
William Green, Manger of IT/Marketing
Mascot Construction
“We hit a brick wall in the middle of our project, thankfully we found Extra Mile Data! Carl was great he took the time to understand our project, fix our problems, and help us with future Database endeavors."
Making Microsoft Access Work. Call (505) 369-1650.
Extra Mile Data, your source for Microsoft Access help
Making Microsoft Access Work
No matter what your MS Access needs are, Carl can provide live help to make sure you get the solution you need.