ACC: Exporting Tables to SQL Server Changes AutoNumber to Long (161862)
The information in this article applies to:
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q161862 Advanced: Requires expert coding, interoperability, and multiuser skills.
SYMPTOMS
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 you are familiar with creating tables in
a Microsoft SQL Server database:
- 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.
- Link the new SQL Server table to your Microsoft Access database.
- 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.
- 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
- Run the SQL pass-through query, and then close it. You do not need
to save the query.
- Run the append query to add data to the SQL Server table.
REFERENCES
For more information about interaction between Microsoft Access and
Microsoft SQL Server, search the Help Index for "SQL Server."
Modification Type: | Major | Last Reviewed: | 10/20/2003 |
---|
Keywords: | kbinterop kbprb KB161862 |
---|
|