AutoNumber Conundrum

enterbase · January 27th, 2004, 04:25 PM

My next little stumbling block if anyone here has come accross it.

Using an sql statement to create a table(recordset) with ado (sorry - I was reared on wrox ado in my asp days...) and defining fields and types works just fine until I try to set the first field as type 'autonumber'. It won't wear it. I appreciate I can create this manually and populate it, but I'd prefer to do it dynamically and solve the problem - I can also not figure how to set the first field in the sql to be a primary key - im sure one of you pro's will probably laugh at this!:

Example statement:
localconnection.Execute _
"CREATE TABLE imported(" & _
"trackid AutoNumber," & _ ' tried AUTONUMBER and autonumber too
"Artist VARCHAR(75)," & _
"title VARCHAR(75)," & _
"BPM INTEGER," & _
"Genre VARCHAR(50)," & _
"Track_Year INTEGER," & _
"Version VARCHAR(75)," & _
"Comment VARCHAR(75)," & _
"Album VARCHAR(75)," & _
"Karaoke VARCHAR(75))"

BethMoffitt · January 27th, 2004, 04:31 PM

This is DAO, but maybe you can find the equivalent if this is what you are looking for:

Code:

'*  Create a field; set its properties; add it to the tabledef
    Set fld = tb.CreateField("ID", dbLong)
        fld.OrdinalPosition = 1
        fld.Attributes = dbAutoIncrField
    tb.Fields.Append fld

'*  Create an index and set its properties
    Set idx = tb.CreateIndex("PrimaryKey")
        idx.Primary = True
        idx.Required = True
        idx.Unique = True

'*  Add an index to a field
    Set fld = idx.CreateField("ID")
        idx.Fields.Append fld

'*  Add the index to the tabledef
        tb.Indexes.Append idx

HTH,

Beth M

enterbase · January 27th, 2004, 07:27 PM

Thanks for taking the time but the DAO route is not of interest to me. I'll dig out my old sams VB books from the attic and find an answer with sql and ado in that.

again - thanks for your time :-)

pgtips · January 28th, 2004, 06:00 AM

Here you go, example of creating table and setting autonumber using ADOX:
http://msdn.microsoft.com/library/en...asp?frame=true

enterbase · January 28th, 2004, 05:29 PM

I found it half in a sams book (TY database programming with vb6 and half in a forum(http://www.utteraccess.com/forums/ac...ess562187.html): this works! (yipeeeee!!))

localconnection.Execute _
"CREATE TABLE imported(" & _
"trackid INT IDENTITY CONSTRAINT PKimported PRIMARY KEY," & _
"Artist VARCHAR(75)," & _
"title VARCHAR(75)," & _
"BPM INTEGER," & _
"Genre VARCHAR(50)," & _
"Track_Year INTEGER," & _
"Version VARCHAR(75)," & _
"Comment VARCHAR(75)," & _
"Album VARCHAR(75)," & _
"Karaoke VARCHAR(75))"

But hell did I get trouble for my sins. Books were in my loft. Halfway through recovering them the snowstorm and wind took the power out and getting my fat butt down in the dark was fun... suffering for fun....