Code snippets and tips for MS Access and VBA from Extra Mile Data

View Code Snippets & Tips

Author:	Extra Mile Data	Created:	3/10/2009 2:35 PM
Code snippets for Microsoft Access and Visual Basic for Applications.

How to use VBA to minimize the Office 2007 Ribbon

By Extra Mile Data on 11/27/2009 2:27 PM

If you've ever wished you could keep the Office Ribbon, but automatically minimize it to conserve space, this VBA function is for you. The same technique works for Microsoft Access, Microsoft Word, etc.

Comments (7)

Use VBA to get the maximum lengths of values in a MS Access table

By Extra Mile Data on 11/27/2009 11:40 AM

If you've ever imported a text file or a Microsoft Excel worksheet into Microsoft Access, you have seen how Access defaults the text field lengths to 255 characters. This VBA function will display the maximum length of the values currently in each field of a table so that you can decide how to correctly set the field lengths. It also shows the data type of each field.

Comments (0)

Put your MS Access database on autopilot

By Extra Mile Data on 10/6/2009 11:48 AM

This example shows how to use a command line switch and a scheduled task to start a Microsoft Access application unattended and run some logic automatically.

Comments (0)

Remove replication from a MS Access database

By Extra Mile Data on 8/4/2009 3:04 PM

This VBA function copies or imports all the objects and database startup properties from a Microsoft Access replicated database into an un-replicated database. It removes tablename_Conflict tables and removes replication-related fields like s_GUID, etc.

Comments (19)

Use VBA to copy MS Access PivotTable values to the clipboard

By Extra Mile Data on 7/7/2009 11:21 AM

This function uses a combination of Microsoft Access, VBA, and Microsoft Excel to transfer the values from an Access form in PivotTable view to the clipboard. It is a good example of automation using late binding.

Comments (4)

Find duplicates in a VBA array

By Extra Mile Data on 6/17/2009 4:04 PM

This VBA function looks for duplicate values in an array. If it finds some, it produces a list of the duplicated values. It is a good example of how to loop through an array.

Comments (2)

Use a VBA function to open/get a Microsoft Word document and Find a string

By Extra Mile Data on 6/12/2009 3:07 PM

This VBA function opens/gets a MS Word document and finds the next occurrence of a specific string. The function provides a good example of using late binding automation and the AppActivate() function.

Comments (1)

Using VBA to fill and manage a Microsoft Access list box

By Extra Mile Data on 6/3/2009 8:46 AM

This set of VBA functions and MS Access example show how to use code to fill a list box, clear a list box, and retrieve selected items from a list box.

Comments (0)

Get an age in weeks using VBA

By Extra Mile Data on 5/18/2009 8:38 AM

This VBA function returns the number of weeks that one date is away from another, in whole weeks. For example, any date in the third week from the starting date will return a value of 3.

Comments (0)

Generate a random set of characters using VBA function Rnd()

By Extra Mile Data on 5/15/2009 8:12 AM

This VBA function uses the Randomize() and Rnd() functions to generate a random string of characters with a specific length. The included Microsoft Access example applies the function to a simple form that can be used to create a password.

Comments (10)

Use a VBA collection to store messages

By Extra Mile Data on 5/4/2009 2:39 PM

This set of procedures can be used in Microsoft Access to collect messages during a process to display to the user after the process is complete. It provides a good example of how to use collections in VBA.

Comments (0)

Use VBA to get the date of the last occurance of a weekday

By Extra Mile Data on 4/29/2009 11:39 AM

This VBA function passes back the last date that a particular weekday occurred.

Comments (0)

Convert a number to a date using VBA

By Extra Mile Data on 4/27/2009 9:59 AM

This VBA procedure converts a number, in yyyymmdd format, to a regular date like mm/dd/yyyy. For example, a number like 20090427 will get converted to 4/27/2009.

Comments (0)

Get the name and description of all MS Access database objects

By Extra Mile Data on 4/24/2009 8:55 AM

This VBA procedure shows you how to create a simple list of the objects in a Microsoft Access database. The list it creates includes the object type, object name, and object description, which can be parsed using the Excel file in the download.

Comments (0)

Count non-Null values across columns in a Microsoft Access record

By Extra Mile Data on 3/10/2009 3:25 PM

Need to get a count of the number of columns in the results of a MS Access query that are not Null? This VBA code will do it.

Comments (0)

Find where an object or code is used in a Microsoft Access database

By Extra Mile Data on 3/10/2009 2:42 PM

There are very sophisticated search programs to help you find occurrences of an object name or a piece of code within a MS Access database. I usually find that this VBA code is sufficient to do the job. It looks in tables, queries, code modules, form and report modules, record sources for forms and reports, and control sources for controls. These procedures are also good examples of how to enumerate through collections of Access objects.

Comments (0)