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 (Like)
4. Click Search Selected Objects Only, and then click the Tables check box.
5. Click the Preview button.
6. After the search results are displayed, click the Sort by Property button.
7. Select and delete all references appearing before and after the group of references related to the Name property.
8. Print the remaining references for later reference.
9. Use SPEED Ferret to globally replace each invalid field name with a new name of your choice.

Tip

To delete a group of references, click the record selector of the first reference and then shift-click the record selector of the last reference in the group. Then select Delete from the Edit menu, or simply press DELETE.