SQL question using ASP/ADO 2

[kor]

objConn.Execute("CREATE TABLE Users&quot
objConn.Execute("ALTER TABLE Users ADD COLUMN userID INTEGER UNIQUE&quot
objConn.Execute("ALTER TABLE Users ADD COLUMN userFirstName VARCHAR[50]&quot
objConn.Execute("ALTER TABLE Users ADD COLUMN userLastName VARCHAR[50]&quot
objConn.Execute("ALTER TABLE Users ADD COLUMN userInfo TEXT&quot

This is a simplified version of my table. The database is Access 97, code is ASP/ADO. What I want to know is how to modify the table with keywords so I can write an insert query with just the name fields and have the userID field auto increment and the userInfo field default to NULL. A sample of that query would be useful as well. Thanks for your help.

Oh, and I'd also like to know how include the field descriptions in the create table command so I don't have to use all these alter table statements. They make my code look less concise.

 

[Qaroven]

objConn.Execute("CREATE TABLE Users (userID counter, userFirstName text(50), userLastName text(50), userInfo TEXT(255), constraint PK_Users primary key (UserID))&quot

That should work for Access backends .. added a primary key for you. Because UserID is now a counter (Autonumber in Access), if you don't try to assign it a value in an insert (ie just ignore it), it will automatically become the next number.

Ben
+61 403 395 052

 

[RobV]

Qaroven! Thanks a bunch. I've been looking for a way to create an autonumber field programmatically for a very long time. You've got my vote! Yours,

Rob.

 

[RoadRacer]

Same here. Great tip! I was about to jump knee deep into creating tables and that was something I had wondered about but hadn't investigated thoroughly yet. You just saved me a lot of time. THANKS!!!
Ed (RoadRacer) Holguin

"I Hate Computers!"

 

[Qaroven]

Another small tip for ya's then w.r.t. create table statements in Access .

Yes/No - True/False - On/Off datatype in Access is called a bit field when you define it in a create table statement. Ben
+61 403 395 052

 

[RobV]

Cool. I almost had to resort to ADOX to make that happen. Yours,

Rob.