Sorry for earlier messed up post, here is results from free tool that
automates creation of the code you want. You can find the tool at
http://home.gci.net/~mike-noel/CompareEM.htm
Option Explicit
'******************************************
'* UpgradeDB subroutine generated by *
'* Compare'EM on 11/14/2005 *
'******************************************
'* Requires Microsoft DAO Object Library *
'****************************************** OLD
'* I:\db1.mdb *
'****************************************** NEW
'* I:\db2.mdb *
'******************************************
'* Compare'EM version 1.0c (PRO) *
'* Copyright © 2005, Mike Noel *
'******************************************
Private Sub UpgradeDB()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim idx As DAO.Index
Dim rel As DAO.Relation
'********************************************
' GIVE SERIOUS THOUGHT! - is next line OK ??
' 'db' defines the database to which changes
' will be applied. Do you really want that
' to be the same as the one where this code
' will run??
'
Set db = CurrentDb
'
'********************************************
' drop index PrimaryKey of table Table1
set tdf = db.TableDefs ("Table1")
tdf.Indexes.Delete "PrimaryKey"
' drop ID field of table Table1
set tdf = db.TableDefs ("Table1")
tdf.Fields.Delete ("ID")
' Create new field ID2 of table Table1
set tdf = db.TableDefs ("Table1")
set fld = tdf.CreateField("ID2", dbLong)
SetPro fld, "Attributes", dbLong, 17
tdf.Fields.Append fld
SetPro fld, "AllowZeroLength", dbBoolean, False
SetPro fld, "DefaultValue", dbText, ""
SetPro fld, "OrdinalPosition", dbLong, 1
SetPro fld, "Required", dbBoolean, False
' create index ID2 of table Table1
set tdf = db.TableDefs ("Table1")
set idx = tdf.CreateIndex("ID2")
set fld = idx.CreateField("ID2")
idx.Fields.Append fld
tdf.Indexes.Append idx
End Sub
'******************************************
'* SetPro subroutine supporting VBA code *
'* generated by Compare'EM *
'******************************************
'* Compare'EM version 1.0c (PRO) *
'* Copyright © 2005, Mike Noel *
'******************************************
Private Sub SetPro(o As Object, s As String, t As DataTypeEnum, v As
Variant)
On Error GoTo Problems
o.Properties(s) = v
Exit Sub
Problems:
If Err = 3270 Then
o.Properties.Append o.CreateProperty(s, t, v)
Resume ProblemsX
End If
On Error GoTo 0
Resume
ProblemsX:
End Sub
No Spam wrote:
Dear Access 2003 users,
Can anyone assist me with creating either code (preferred) or a query
that would remove a single field (called ID) from a table? And as a
bonus question, can anyone assist with adding a field (called ID) to a
table and then make it an Autonumber data type? I've been successful
in adding the field via code, but cannot make it an autonumber field.
Thanks a million in advance!
Kevin