Autonumber automaticcaly?

[Leen]

My goal is to automate a process via Macros, without that the user of the database has to touch the design view etc.

I have one table met 1 field = Road_id, but know, I'd like to add a second field (ex. auto_id) and have this one in autonumber, for example:

road_id & auto_id
5 & 1
8 & 2
99 & 3
852 & 4

The problem is, I want to add this second automatically (without having to go in designview and add a field "auto_id" with datatype AutoNumber).
I tried via an sql-specific-query but the datatype 'AutoNumber' is not recognised. Then I tried via Code, but again, no datatype 'Autonumber' is possible:

------------
Sub CreateTable()

Dim dbs As Database
Dim tbl As TableDef

Set dbs = CurrentDb
Set tbl = dbs.TableDef("New_ManoeuvreR")

With tbl
.Fields.Append .CreateField("auto_id", ????)
dbs.TableDefs.Append tbl
End With

End Sub
-----------

Can someone help me to resolve this? Thanks a lot in advance!

 

[llkhoutx]

you're going to a lot of trouble to do something Access will do for you, just make the second field Autonumber data type. Millions do it every day.

 

[Leen]

I do still want to find a solution to add a column with datatype "AutoNumber" without having to go in designview (so via macro/...)

In the help i found the following:
---
Sub CreateAutoIncrColumn()

Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\Program Files\" & _
"Microsoft Office\Office\Samples\Northwind.mdb;"
Set cat.ActiveConnection = cnn

With tbl
.Name = "MyContacts"
Set .ParentCatalog = cat
' Create fields and append them to the new Table object.
.Columns.Append "ContactId", adInteger
' Make the ContactId column and auto incrementing column
.Columns("ContactId").Properties("AutoIncrement") = True
.Columns.Append "CustomerID", adVarWChar
.Columns.Append "FirstName", adVarWChar
.Columns.Append "LastName", adVarWChar
.Columns.Append "Phone", adVarWChar, 20
.Columns.Append "Notes", adLongVarWChar
End With

cat.Tables.Append tbl

Set cat = Nothing

End Sub
---
However, even when changing the path of my connection:
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= c:\Program Files\" & _
"Microsoft Office\Office\Samples\Northwind.mdb;" into:
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= d:\Leen\test.mdb\"&_
(whereas test.mdb is the database I want to create the table with the column AutoIncrement)

I'm not at all familiar with ADO-objects, but can somebody tell me why this doesn't work?

Thanks alot!

 

[unclejoe]

Hi,

Is this code in the current database?
Is this code in a form's module or a standard module?

I do still want to find a solution to add a column with datatype "AutoNumber" without having to go in designview (so via macro/...)

In the help i found the following: snip.....

I'm not at all familiar with ADO-objects, but can somebody tell me why this doesn't work?

Thanks alot!

 

[stopher]

The jet keyword is AUTOINCREMENT not the more obvious AUTONUMBER. So the following SQL will add an autonumber column:

ALTER TABLE myTable ADD COLUMN myAutoColumn AUTOINCREMENT

hth
Stopher