Help -- autonumber with linked SQL table

[isdex1]

Please help, it is driving me crazy.

I realized that in order for the linked (SQL) table recordset to be updatable, there must be a primary key set at the SQL table. That's fine, I have the following column in SQL table

ID bigint <--- Primary Key
Name varchar
City varchar

On the Access side, how can I make ID to use AutoNumber? I really don't need this column at all, I would prefer if I can eliminate it. However, as I mentioned earlier, in order for the recordset to be updatable, there must be a primary key assigned to the table. Is there a better way to do this? I really don't want to assign a new unique ID everytime I add a new record from the access side. Please help!

 

[jsteph]

Is the Bigint an Identity column? This is sql-servers implentation of Autonumber. If not, then Access certainly won't generate the numbers for you.

Also, I think Bigint has an issue with ODBC--it translates as Text, because it's larger datatype than Access' longint.
--Jim

 

[JoeAtWork]

As jsteph said, set ID to be an Identity type rather than BigInt.

By the way, you really should have primary keys for all tables, not doing so is very poor database design.