Auto numbering in ADO

[WayneS]

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.
[CODE]
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
[CODE]
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.
Wayne

[hspc]

IDENTITY is called COUNTER in access:
use something like :

Create table ABC(MyID COUNTER primary key )

[WayneS]

I already tried that but didn't work. I get a sql syntax error. I found out that if I made the primary key a long then it worked. Don't know why but it does.
Thanks for your reply,
Wayne

[hspc]

Hi
this works great in Access XP:

CREATE TABLE League (ID counter PRIMARY KEY,
LeagueName Text,
BowlingCenter Text)

What version of access do you use ?
Do you get any Error messages ?

[WayneS]

The problem is I don't have Access. Wish I did. Had to do it all with ADO and SQL.
Thanks anyway,
Wayne