This took me longer to find on Google than I wanted. If you want to have an auto-incrementing primary key in MS Access (sucks), then you want something like the following in the your create table statement:
CREATE TABLE tablename (
id COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,
intColumn INTEGER,
description MEMO
)
The primary key is ‘id’ and it’s of type COUNTER. Consider the above opposed to the SQL Server 2000 CREATE statement:
CREATE TABLE tablename (
id int identity (1, 1) NOT NULL PRIMARY KEY,
intColumn int,
description varchar(2048)
)
Hopefully, with SQL Server 2005 Express coming out, MS Access (sucks) might go the way of the dodo in 10 years.
While I do agree that MS Access has its quirks, I dont think its use as a host db is what is keeping it ticking. Instead, many companies use it as a front end into larger data stores for its scripting, GUI creation and charting/graphing abilities (this is where access adds value in my mind). However, I will be interested to see how this role is maintained in the future as the VSTO tools are expanded to meet the current feature set provided by access.
finaly, been searching for hours, access sql sucks ass…
10x dude