Article ID: 281950
Article Last Modified on 11/6/2003
APPLIES TO
- Microsoft Access 2002 Standard Edition
This article was previously published under Q281950
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies only to a Microsoft Access database (.mdb).
SYMPTOMS
When upsizing to an existing Access database, the tables are unexpectedly upsized to the Master database on your SQL Server.
CAUSE
When upsizing to an existing database, the Upsizing Wizard opens a Select Data Source dialog box to identify a data source pointing to the destination SQL Server. By default, the destination database in a NEW Data Sources is the SQL Server Master database. If not changed, the tables will be upsized to the Master database.
RESOLUTION
To move the objects from the Master database into a different SQL Server database, you can copy the objects by using Data Transform Services from SQL Server Enterprise Manager to a different SQL Server database. Then, you can delete the upsized tables, views, and stored procedures from the Master database.
If you do not have access to Enterprise Manager, you can create an Access project (.adp) that connects to the Master database. Then you can open that project and manually delete the tables, views, and stored procedures. Next, modify the Data Source to point to the database you want to upsize to instead of the Master database. Finally, run the Upsizing wizard again to create the tables, views, and stored procedures in the correct database.
To prevent this from happening again, change the default database before you finish upsizing. After selecting the SQL Server driver in the Select a Data Source dialog box, the Create a New Data Source to SQL Server dialog box is displayed. After the Data Source Name, Description, and SQL Server name are specified, clicking Next allows the security type to be selected. Clicking Next again, presents an option to change the default database. The SQL Server database Master is the selected default. To upsize to a different database, click to select the Change the default database to the check box, and select the appropriate SQL Server destination database.
STATUS
This is behavior is by design.
REFERENCES
For more information about the Data Transform Services, search for DTS Overview in the SQL Server Books Online.
Additional query words: prb incorrectly wrong
Keywords: kbprb kbclientserver KB281950
