AUTO_INCREMENT now causing error 1

[ADoozer]

im hoping this is a simple one.

im using ADODB.Execute to run an SQL command. it worked perfectly fine at work, but now on my home machine its kicking up an error "incorrect CREATE TABLE syntax"

Public Const sqlCreatePlayerTable As String = "CREATE TABLE PlayerData (PlayerID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, PlayerHandle char(25))"

Public Sub CreateStorageDB()
    Dim oADOX As Object
    Dim oADODB_CN As Object
    
    Set oADOX = CreateObject("ADOX.Catalog")
    oADOX.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Tracker.mdb"
    Set oADOX = Nothing
    
    Set oADODB_CN = CreateObject("ADODB.Connection")
    oADODB_CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\Tracker.mdb"
    oADODB_CN.Open
    oADODB_CN.Execute sqlCreatePlayerTable
    oADODB_CN.Close
    Set oADODB_CN = Nothing
End Sub

as i said, on my work machine (XP pro) the syntax works and does create the table, however on my home machine (2k pro) it errors unless i remove AUTO_INCREMENT from the sql string.

any input appreciated

If somethings hard to do, its not worth doing - Homer Simpson

 

[gmmastros]

You may have different versions of access. I always thought that access didn't have an AUTO_INCREMENT field. Instead, it has counters. Now counters cannot be null, and they must be integers, so you don't need to specify that. Also, primary keys are implemented as constraints, and contraints have names, so.... Here's what I came up with. It runs properly on an access 2000 database.

CREATE TABLE PlayerData (PlayerID counter constraint PlayerData_PrimaryKey PRIMARY KEY, PlayerHandle char(25))

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ADoozer]

hmm, wonder why it works in the office then?!

thanks anyways. thats the ticket *

(dont you just love the simple solutions )

If somethings hard to do, its not worth doing - Homer Simpson

 

[strongm]

Well, Jet does have an auto increment field, but it is known as AUTOINCREMENT rather than AUTO_INCREMENT, and is simply a synonym for COUNTER.

A quick google seems to suggest that AUTO_INCREMENT is predominantly a MySQL thing ...

 

[ADoozer]

still, its odd that it worked on my work PC. exact same code

If somethings hard to do, its not worth doing - Homer Simpson

 

[strongm]

Very odd, yes.