Troubleshoot copying, transferring, and linking to a SQL Server database

Copying and transferring a SQL Server database

I'm having problems copying a SQL database.

Possible reasons are:

• Other users are connected to the Microsoft Access project.

  Disconnect all applications and users before you copy the database file. Be careful doing this. Make sure you warn users before disconnecting them to prevent possible data loss.

• One or more database objects are currently open.

  Close all windows and views except the database window.

• The current database is replicated. You cannot copy a replicated SQL Server database.

  You can use Microsoft SQL Server Enterprise Manager to remove publications and subscriptions before copying the database file.

• Copying an SQL Server database requires Microsoft SQL Server version 7.0 or SQL Server 2000 Desktop Engine on the local computer. To resolve this, you can do the following:
  • If you want to make a copy of a database on a remote server, point to Database Utilities on the Tools menu, and then click Transfer Database.
  • You can install  Microsoft SQL Server 2000 Desktop Engine on your local computer from the \MSDE2000 folder on the Microsoft Office XP CD-ROM.
  • If you are connected to a SQL Server 6.5 database, you can use SQL Server Enterprise Manager to detach the database, copy it to the new server, and then reattach it.

• The SQL Server database has more than one data file associated with the current database. You can only copy a single file database.

• You are not a member of the sysadmin role. You must be a member of the sysadmin role on both the source and destination server to copy a SQL Server database.

• A  database with the same name already exists on the destination server. Provide a new name for the database.
• You don't have enough disk space. Access cancels a copy database operation if you don't have enough disk space to create the new database.

  Make sure you have adequate disk space on the destination server before doing a copy database operation.

If you encounter errors during the database copy operation, open the transfer log files created in the same folder as the Access project for more details.

I'm having problems transferring a SQL database.

Possible reasons are:

• Either the source or destination database is SQL Server version 6.5. The current SQL server connected to the Access project and the destination server you are transferring the database to must be SQL Server version 7.0 or later.
• You must be a member of the sysadmin role on the destination server. Although being a member of the sysadmin role is not required on the source server, you must be a member of the sysadmin role on the destination server to transfer a SQL Server database.
• A  database with the same name already exists on the destination server. Provide a new name for the database.
• You don't have enough disk space. Access cancels a transfer database operation if you don't have enough disk space to create the new database.

  Make sure you have adequate disk space on the destination server before doing a transfer database operation.

If you encounter errors during the database transfer operation, open the transfer log files created in the same folder as the Access project for more details.

My extended properties were not copied when I transferred my SQL Server database.

To copy extended properties, Access requires Microsoft Data Access Components (MDAC) 2.6 on your local computer. If you don't have SQL Server 2000 or SQL Server 2000 Desktop Engine installed on your local computer, you may only have MDAC 2.5.

To install MDAC 2.6, you can install SQL Server 2000 Desktop Engine on your local computer from the \MSDE2000 folder on the Microsoft Office XP CD-ROM.

Linking to a SQL Server database

I'm having problems linking to text files using the Linked Table Wizard.

You cannot link fixed-width text files to SQL Server using the Linked Table Wizard. If you can, convert the text file to a delimiited text file. You might consider importing the fixed width text file on a regular basis instead. Importing the text file will also improve performance, especially if the table you create has indexes.

The Link Table Wizard always uses the comma (,) character as a list separator with delimited text files. You may have changed the list separator on your machine by changing the Regional Settings through Windows Control Panel. For example, the German list separator is a semicolon (;) character. If you can, convert the list separator to a comma (,) character before linking.

When I link to a spreadsheet, text file, or HTML file, performance is very slow.

You may have selected Linked SQL in the first screen of the Link Table Wizard, which creates a linked server. If you select Transact SQL, Access creates an OLE DB data source that uses Transact SQL functions to create an ad hoc read-only connection to the external data source. Access uses either the OPENDATASOURCE (SQL Server 2000) or OPENROWSET (SQL Server 7.0) Transact SQL functions. If you are linking to a spreadsheet, text file, or HTML file, consider using this option because the performance is generally better than creating a linked server. You might consider importing the data source on a regular basis instead. Importing the data source will also improve performance, especially if the table you create has indexes.

I'm getting the message "You cannot link to file '<filename>'."

Your Access project is connected to a SQL Server database on a remote computer. You can only link to tables when your Access project is connected to a SQL Server version 7.0 or later database or SQL Server 2000 Desktop Engine running on your local computer.

When I link to a spreadsheet, text file, or HTML file my first row of data is missing.

When you link to a spreadsheet, text file, or HTML file, Access assumes that the first row contains field names, not data, and uses the first row of data to make the column headers. Make sure the spreadsheet, text file, or HTML file has column headers, and then re-link the data source.

My extended properties are not being recognized or saved in my linked table.

Extended properties are ignored in a linked table. Consider transferring or or copying the SQL Server database.