CODEWITHSUNDEEP
sqlms-accessasp-classic
Clay
Clay

Reputation: 53

Create mdb table field and set to autoincrement

I am totally unfamiliar with SQL but I am trying from ASP to create a new table in a database and set three fields with ‘field3’ being set as a primary key auto_increment field.

I have tried many code snippets that I have found online but all fail with syntax errors. The CREATE TABLE works fine until I try to make a field auto_increment.

Also when trying to use ALTER TABLE I have managed to set a field as a primary key but again when trying to also make it auto_increment it fails with a syntax error.

Below is one example of what I am trying.

Any help to achieve what I need to accomplish would be much appreciated. Thanks in advance. 

<%

'returns: Microsoft JET Database Engine error '80040e14' Syntax error in ALTER TABLE statement.

set conn = server.CreateObject ("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("databases\db1.mdb")

Dim strSQL

strSQL = "CREATE TABLE table1 (field1 INT, field2 CHAR(10))"

conn.Execute strSQL

strSQL = "ALTER TABLE table1 ADD field3 INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (field3)"

conn.Execute strSQL

conn.Close

%>

Upvotes: 0

Views: 3733

Answers (2)

Dev N00B
Dev N00B

Reputation: 218

I personally would create the table all in one go rather than create and alter, something like,

"CREATE TABLE table1(field 1 int, field 2 CHAR(10), field 3 INT PRIMARY KEY AUTOINCREMENT(1,1))

EDITED, originaly postey MySQL without reading properly 2nd edit. now have seen ms access so above should be correct syntax

Upvotes: 0

Kul-Tigin
Kul-Tigin

Reputation: 16950

You can create the table with a query like this : 

CREATE TABLE table1 (field1 INT, field2 CHAR(10), field3 Identity(1,1), Primary Key (field3))

Upvotes: 2

Related Questions