Create AutoNumber variable using SQL

[raf]

I’ve written an online database management tool for MsAccess where you can create and alter tables. It’s written in ASP with embedded SQL (JetSQL),. I’m wondering if it’s possible to create columns of datatype AutoNumber or Memo. Also, can you rename tables, using JetSQL ?

Anyone done this sort a things or knows how to do them?

[miranda]

yes use the following datatype in your alter table statement or in your create table statement

counter

[aCcodeMonkey]

Raf,

Had to dig in the archives for this on...

Fortunately there was an active reference link in the code..

Creating Access Tables ADO/DAO/ADOX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wp/htm/wpmigratingschema.asp)



Function ADOIncrementColumn(Newtable)

Dim oCatalog ' Db Table Collection
Dim oColumns 'Table Columns Collection
Set oCatalog = Server.CreateObject("ADOX.Catalog")
Set oColumns = Server.CreateObjects("ADOX.Column")


' Open the catalog
oCatalog .ActiveConnection = Application("DBAdmin")

' Create the new auto increment column
With oColumns
.Name = "IndexId"
.Type = adInteger
Set .ParentCatalog = oCatalog.Properties("AutoIncrement") = True
End With

' Bind the changes to the new table
oCatalog .Tables(NewTable).Columns.Append col

'Cleanup
Set oCatalog = Nothing

Return True

ADOXErrHandler:


End Sub


Hope this helps :cool:

[aCcodeMonkey]

Raf,

Sorry for the Repost. hit the tab key & enter by accident....

Had to dig in the archives for this on...

Fortunately there was an active reference link in the code..

Creating Access Tables ADO/DAO/ADOX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wp/htm/wpmigratingschema.asp)



Function ADOIncrementColumn(Newtable)

On Error GoTo ADOXErrHandler

Dim oCatalog ' Db Table Collection
Dim oColumns 'Table Columns Collection
Set oCatalog = Server.CreateObject("ADOX.Catalog")
Set oColumns = Server.CreateObjects("ADOX.Column")


' Open the catalog
oCatalog .ActiveConnection = oDBAdminConn

' Create the new Index Id column
With oColumns
.Name = "IndexId"
.Type = adInteger
Set .ParentCatalog = oCatalog.Properties("AutoIncrement") = True
End With

' Bind the changes to the new table
oCatalog .Tables(NewTable).Columns.Append col

'Cleanup
Set oCatalog = Nothing

Return True

ADOXErrHandler:
return False

End Sub


Hope this helps :cool:

[raf]

Thanks all.

I've worked the datatypes out (using "counter" for AutoNumer and "memo" for "memo" fields).

Instead of renaming table, i'm going to copy them into a new table + store the original table (for saftereasons) or delete is.

Thanks