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

View Code Snippets & Tips

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

Use VBA to return a column or row from a MS Excel range address

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

This VBA function returns the first row, last row, first column, or last column of a Microsoft Excel range address. It is also a good example of using a custom enumerated type.

Comments (0)

MS Excel and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

SQL differences between MS SQL Server and MS Access

By Extra Mile Data on 4/27/2009 10:56 AM

Here are some differences in how SQL is written in Microsoft SQL Server versus Microsoft Access.

Comments (0)

MS SQL Server

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA

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)

MS Access and VBA