Reading an AutoNum fieldname and changing its datatype to Number

[LF]

The vb code snipet below opens a recordset of a given table variable and
loops through the recordset fields testing each field type until an
autonumber is found. Is there a shorter method to find the name of any
tables' unique id field?

Also I want to change the datatype of this field from AutoNumber to Number;
I would greatly appreciate any advice on how to do so.

Set db = CurrentDb()
strSQL = "SELECT * FROM [" & strTbl & "];"
Set rs = db.OpenRecordset(strSQL)
For Each fld In rs.Fields
If fld.Type = AutoNumber Then
Debug.Print fld.Name & " = " & fld.Value
strID = fld.Name
Else
Debug.Print fld.Type & " is not an autonumber"
End If
Next fld
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

 

[Allen Browne]

Looping through the fields until you find the the autonumber is fine. You
would normally do this on the TableDef, rather than on a query opened into
the table.

You cannot change the autonumber to a Number, so the steps are:

1. Make sure Name AutoCorrect is off under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Create a new Number field in the TableDef, using CreateField() and Append
to the Fields collection. Example:
http://allenbrowne.com/func-DAO.html#ModifyTableDAO

3. Execute an Update query to populate the new field with the values from
the AutoNumber field.

4. If the AutoNumber has matching foreign key fields in other tables, record
those relations and their attributes, and delete the relations.

5. Record any index(es) that use the AutoNumber (typically "PrimaryKey"),
and delete them.

6. Create the same index(es) on the new Number field.

7. Create the relationso to the other tables.

8. Delete the AutoNumber field from the TableDef.

9. Rename the new field so it uses the same name.

10. Compact the database.

 

[LF]

Thank you Allen you are a wealth of information. Your explanation,
GetAutoNumDAO and a derivative of your ModifyTableDAO functions provided my
solution. I havn't tested it yet, but my attempt to change the datatype is
below.

Can you please suggest details on how to compact the database?

Sub undoAutoNum(strTbl As String, strFld As String)
'Convert an Autonumber datatype to a Number datatype
Dim db As DAO.Database, tdf As DAO.TableDef, fld As DAO.field
Dim strSQL As String

'open the table's definition
Set db = CurrentDb()
Set tdf = db.TableDefs(strTbl)

'create a new field
tdf.Fields.Append tdf.CreateField("Num", Number)

'run an update query to populate ID values from existing ID values
strSQL = "UPDATE " & strTbl & " INNER JOIN " & strTbl
strSQL = strSQL & " AS " & strTbl & "_1" & " ON " & strID & "." &
strFld
strSQL = strSQL & " SET " & strID & ".Num = " & strID & "." & strFld
& ";"
DoCmd.RunSQL strSQL

'delete the old field
tdf.Fields.Delete strFld

'rename the new field to the old field name
tdf.Fields(Num).Name = strFld

'compact the db
'??????

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing
End Sub

 

[Allen Browne]

The compact is optional.
You need to do it manually.

 

[LF]

Thank you.
--
LF

The compact is optional.
You need to do it manually.