Automatic Increment

waynespangler
10-27-2004, 01:30 PM
Hi,
I am new to database programing and have this problem I can't find an answer to. This is the code I am using.

Set conData = New ADODB.Connection
conData.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\data.mdb"

strSQL = "CREATE TABLE League (" & _
"ID Integer NOT NULL IDENTITY(1,1) PRIMARY KEY," & _
"LeagueName Varchar(50)," & _
"BowlingCenter Varchar(50));"
conData.Execute strSQL
Set conData = Nothing

However when I add data the ID (primary key) does not increment. All my ID's are 1.
What am I doing wrong?
Thanks for your help.

VBJoe
10-27-2004, 03:45 PM
Here's the SQL generated when I created a table in SQL Server. I'm not sure if Access and SQL use the exact same form of SQL:

CREATE TABLE [LargeWords] (
[WordID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Word] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

I do know that Identity fields in Access have their own special data type. I think it's called AutoNumber. Maybe that's what you're looking for.

waynespangler
10-27-2004, 04:04 PM
It's been a major problem finding something. All databases use a different SQL format. MyScl, MSAccess, ADODB, SQL Server, ect. use SQL but nothing is standard. I have tried about all of them but I get a syntax error in the sql string.
I know there must be a way of doing it but I am so new at it I can't figure it out.
AutoNumber didn't work but I will keep looking.
Thanks for your help,

loquin
10-27-2004, 06:12 PM
See THIS (http://www.visualbasicforum.com/showthread.php?t=195261) thread

webbone
10-27-2004, 06:26 PM
In addition to the thread loquin cited, you can use the following for ACCESS:

strSQL = "CREATE TABLE League (" & _
"ID Long IDENTITY(1,1) PRIMARY KEY," & _
"LeagueName Varchar(50)," & _
"BowlingCenter Varchar(50));"

Also, if I remember correctly (been awhile since I've done this in code and I'm not at home to check) you can change the START value for Access {IDENTITY(Start,Step} but NOT the STEP value (putting a different step value still increments by 1) - Not totally sure on that though.

For a handy reference (even though it doesn't address Access) I keep SQL In a Nutshell (http://www.oreilly.com/catalog/sqlnut2/) from O'Reilly Books handy on my desk. MANY of the SQLServer constructs work for Access (or give a good starting point for a google search!).

waynespangler
10-27-2004, 07:56 PM
That was it. I changed it from Integer to Long and it now works.
Thanks so much,

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum