Background

You can use the Microsoft Access Upsizing Wizard (supplied by Microsoft) to export Access tables to a Microsoft SQL Server database. During this process, the Upsizing Wizard inspects field names to make certain that they are valid SQL Server field names.

SQL Server allows only the following characters in field names:

1. lowercase letters (a-z)
2. uppercase letters (A-Z)
3. decimal digits (0-9)
4. underscores (_)
5. dollar signs ($)
6. pound signs (#)

If the Upsizing Wizard detects a field name containing an invalid character, it performs the following steps:

• It replaces all invalid characters with underscores when creating the server table.
• It appends the suffix "_remote" to the attached table.
• It creates an aliasing query having the same name as the original table so that forms, reports, macros, modules, and other queries will work properly with the new server table.

Problem

The aliasing query imposes no significant performance penalty, however the aliasing approach does have drawbacks:

• The total number of database objects is increased.
• Multiple names are used for the same fields at different places.

Together these factors increase the cost and complexity of maintaining the application.

Solution

You can use SPEED Ferret to create a list of invalid field names used in your database. Then you can use SPEED Ferret to globally replace each of these names before exporting your tables.

1. Open your database and start SPEED Ferret.
2. Enter the following search string in the Find What field: *[!A-Za-z0-9_$#]*
3. In the Match field, select Pattern (Wildcard)
4. Create or select an object set containing only tables.
5. Create or select a property set containing only the Name property.
6. Click the OK button.
7. Sort the search results by Value so that multiple occurrences of the same name are clustered together.
8. Print the search results for later reference.
9. Use SPEED Ferret to globally replace each invalid field name with a new name of your choice.