Search code examples
sql-serversql-server-2008ms-accessautonumbertinyint

Access linked table to SQL Server: TinyInt incorrect mapping

I got a SQL Server PriceLists table:

CREATE TABLE [dbo].[PriceLists](
    [PriceListId] [tinyint] IDENTITY(1,1) NOT NULL,
    [PLName] [varchar](20) NULL,
CONSTRAINT [PK_PriceLists] PRIMARY KEY NONCLUSTERED

When I link it into Access 2007, Access sets the PriceListId to Long Integer (which is incorrect).
When I link other tables where the TinyInt is not "AutoNumber" (in Access jargon), it links correctly as a Byte field.
Any clue about properly attaching the table to have the PriceListId as an AutoNumber, Byte field?

Solution

  • I do not think you can, autonumber in Access is Long, and Help says that to link to an autonumber, you must use Long, which, I think, implies that that is all it will accept.

    Here is some mapping: http://office.microsoft.com/en-us/access-help/about-differences-between-data-types-in-an-access-database-and-access-project-HP005274573.aspx