ACC: Exporting Tables to SQL Server Changes AutoNumber to Long

• Microsoft Access versions 7.0, 97

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you export a Microsoft Access table to Microsoft SQL Server, any AutoNumber fields in the table become Long Integer fields in the SQL Server table.

RESOLUTION

The following steps assume that your are familiar with creating tables in a Microsoft SQL Server database:

1. Create a table in Microsoft SQL Server to store the data from your Microsoft Access table. Use equivalent or compatible data types for the columns in the new SQL Server table and use an Identity column to store the AutoNumber field from the Microsoft Access table.

2. Link the new SQL Server table to your Microsoft Access database.

3. Create an Append query based on your Microsoft Access table that appends the data to the linked SQL Server table. Save the query, but do not run it.

4. Create an SQL pass-through query that sets Identity_Insert ON for the SQL Server table. This allows you to update the Identity column with data from your Microsoft Access table.

   NOTE: You must log on to SQL Server as the database owner (dbo) or the owner of the table in order to set Identity_Insert.

   A sample SQL statement to set Identity_Insert ON for the Jobs table in the Pubs sample database in Microsoft SQL Server is:

         Set Identity_Insert dbo.Jobs ON
   

5. Run the SQL pass-through query, and then close it. You do not need to save the query.

6. Run the Append query to add data to the SQL Server table.

MORE INFORMATION

The following steps assume that you have an ODBC data source that connects to a Microsoft SQL Server database.

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Select the Products table in the Database window. The Products table contains a ProductID field that is of data type AutoNumber.

3. On the File menu, click Save As/Export.

4. In the Save As dialog box, click "To an External File or Database," and then click OK.

5. In the "Save Table Products In" dialog box, select ODBC Databases in the Save as type box.

6. In the Export dialog box, type Products.

   NOTE: If your SQL Server database already contains a table named Products, the export will fail; if that happens, use a different table name.

7. In the Select Data Source dialog box (or SQL Data Sources dialog box in version 7.0), select the data source to connect to your SQL Server database, and then click OK. The table is exported to Microsoft SQL Server.

8. On the File menu, point to Get External Data, and then click Link Tables.

9. In the Link dialog box, click ODBC Databases in the Files Of Type box.

10. In the Select Data Source dialog box (or SQL Data Sources dialog box in version 7.0), click the data source to connect to your SQL Server database, and then click OK.

11. In the Link Tables dialog box, click the Products table, and then click OK.

12. In the Select Unique Record Identifier dialog box, click ProductID, and then click OK.

13. Open the linked Products table in Design view; click Yes to the prompt that you cannot modify all properties of a linked table. Note that the ProductID data type is Number, and its FieldSize is Long Integer.

REFERENCES

For more information about interaction between Microsoft Access and Microsoft SQL Server, search the Help Index for "SQL Server."