adding a clumn to every table access2003

[Guest]

I can access every table and i can add a column using ADOX. How do I get the
2 to work together?
Function testFindTables() As Boolean
Dim tblSub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim okey As ADOX.Key
Set okey = New ADOX.Key
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = CurrentProject.Connection
'"Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & strDBPath

Set tbl = New ADOX.Table
With tbl
.Name = "Contacts"
Set .ParentCatalog = catDB ' Create fields and append
them to the
' Columns collection of the
new Table object.
With .Columns
.Append "ContactId", adInteger ' Make the ContactId field
auto-incrementing.
.Item("ContactId").Properties("AutoIncrement") = True
End With
catDB.Tables.Append tbl
With okey
.Name = "PrimaryKey"
.Type = adKeyPrimary
.RelatedTable = "contacts"
.Columns.Append "contactID"
End With
catDB.Tables("contacts").Keys.Append okey
End With

' Add the new Table to the Tables collection of the database.
'catDB.Tables.Append tbl

Set catDB = Nothing
End Sub1 As ADODB.TableDef
Dim result As VbMsgBoxResult

For Each tbl In CurrentDb.TableDefs

result = MsgBox("Do you want to delete " & tbl.Name, vbYesNo,
"Delete table...")
If result = vbYes Then
result = MsgBox("Are you sure you want to PERMANENTLY delete " &
tbl.Name & "'from the database???", vbYesNo, "Confirm delete...")
If result = vbYes Then
DoCmd.CopyObject , tbl.Name + 1, acTable, tbl1.Name
End If
End If
Next tbl

testFindTables = True 'Function completes OK

End Function

 

[Guest]

I got it narrowed down to this
Sub callcreate()
CreateAutoNumberField ("c:")
End Sub


Sub CreateAutoNumberField(strDBPath As String)
Dim catDB As ADOX.Catalog
Dim tbl As ADOX.Table
Dim okey As ADOX.Key
Set okey = New ADOX.Key
Set catDB = New ADOX.Catalog
' Open the catalog.
catDB.ActiveConnection = CurrentProject.Connection
For Each tbl In catDB.Tables
' Create fields and append them to the
With tbl.Columns ' Columns collection of the Table object.
.Append "TableId", adInteger
End With
With tbl.Columns("TableID")
.ParentCatalog = catDB
.Properties("AutoIncrement") = True ' Make the ContactId field
auto-incrementing
End With
With okey
.Name = "PrimaryKey"
.Type = adKeyPrimary
.RelatedTable = tbl.Name
.Columns.Append "TableID"
End With
catDB.Tables(tbl).Keys.Append okey

' Add the new Table to the Tables collection of the database.
'catDB.Tables.Append tbl
Next tbl
Set catDB = Nothing
End Sub

BUT I have to assign an access catalog to the column before i can change the
property to autoincrementing and it doen't like my assignment.
..parentCatalog= catdb is failing.
Help Please...
Lewie