Once a table is created, you may want to modify
it to add or remove columns, change the validation rule or refresh the link for
a linked table.
The following listings demonstrate how to add a
new auto-increment column to an existing table.
DAO
Sub DAOCreateAutoIncrColumn()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
' Get the Contacts table
Set tbl = db.TableDefs("Contacts")
' Create the new auto increment column
Set fld = tbl.CreateField("ContactId", dbLong)
fld.Attributes = dbAutoIncrField
' Add the new table to the database.
tbl.Fields.Append fld
db.Close
End Sub
ADOX
Sub ADOCreateAutoIncrColumn()
Dim cat As New ADOX.Catalog
Dim col As New ADOX.Column
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
' Create the new auto increment column
With col
.Name = "ContactId"
.Type = adInteger
Set .ParentCatalog = cat
.Properties("AutoIncrement") = True
End With
' Append the column to the table
cat.Tables("Contacts").Columns.Append col
Set cat = Nothing
End Sub
In the ADOX example, notice that the ParentCatalog property of the Column
object is set before the AutoIncrement property in the Properties collection is set to True. In order to access any
property in the Properties collection, the Column
object must be associated with a provider.
The next example shows how to update an existing
linked table to refresh the link. This involves updating the connection string
for the table and then resetting the Jet OLEDB:CreateLink property
to tell Microsoft Jet to reestablish the link.
DAO
Sub DAORefreshLinks()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
' Open the database
Set db = DBEngine.OpenDatabase(".\NorthWind.mdb")
For Each tbl In db.TableDefs
' Check to make sure table is a linked table.
If (tbl.Attributes And dbAttachedTable) Then
tbl.Connect = "MS Access;PWD=NewPassWord;" & _
"DATABASE=.\NewPubs.mdb"
tbl.RefreshLink
End If
Next
End Sub
ADOX
Sub ADORefreshLinks()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
' Open the catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\NorthWind.mdb;"
For Each tbl In cat.Tables
' Check to make sure table is a linked table.
If tbl.Type = "LINK" Then
tbl.Properties("Jet OLEDB:Create Link") = False
tbl.Properties("Jet OLEDB:Link Provider String") = _
";pwd=NewPassWord"
tbl.Properties("Jet OLEDB:Link Datasource") = _
".\NewPubs.mdb"
tbl.Properties("Jet OLEDB:Create Link") = True
End If
Next
End Sub
EmoticonEmoticon