ACC: TransferDatabase Macro Action Does Not Replace Table

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

If you use a TransferDatabase macro action to transfer a table with the same name as an existing table in the destination database, the existing table will not be overwritten by the transferred table.

CAUSE

If the destination database contains a table with the same name as the table you are trying to transfer, Microsoft Access will give the transferred table a new name in the destination database. The new name will consist of the original table name plus a sequential number. For example, if you try to transfer a table named Categories to a database that already has a table with the same name, the newly transferred table will be named Categories1.

RESOLUTION

If you want to replace an existing table using a TransferDatabase macro action, you first need to rename or delete the existing table in the destination database.

STATUS

This behavior no longer occurs in Microsoft Access 97.

MORE INFORMATION

Steps to Reproduce Behavior

NOTE: The following example assumes that Microsoft Access is installed in the C:\MsOffice\Access folder. If Microsoft Access is installed in a different folder on your computer, modify the example to refer to your Microsoft Access folder.

1. Before starting Microsoft Access, in Windows 95 use Windows Explorer (or in earlier versions of Microsoft Windows, use File Manager) to make a copy of the Northwind.mdb (or NWIND.MDB in version 1.x or 2.0) file. Name the copy Northwind2.mdb, and place it in the Microsoft Access folder.

2. Start Microsoft Access. Open the sample database Northwind.mdb.

3. Create the following new macro, and save it as mcrTest1:

         Macro Name   Action
         -----------------------------
         mcrTest1     TransferDatabase
   
         mcrTest1 Actions
         --------------------------------------------
         Transfer Type: Import
         Database Type: Microsoft Access
         Database Name: C:\MsOffice\Access\Nwind2.mdb
         Object Type: Table
         Source: Categories
         Destination:  Categories
         Structure Only: No
   
   

4. Run the macro by clicking Run on the Macro menu, and then close the macro.

5. In the Database window, click Table. Note that the original Categories table still exists, and that a new Categories1 table has been added.

REFERENCES