Access VBA Rename column in backend database
I am using the following code given to me by an expert to add a column to a linked Access table. How would I alter the code if I wanted to rename an existing column?
Thanks
Sub Add_Column_to_Backend(ByVal oTable As String, ByVal oColumn As String, ByVal oType As String)
Dim strDbName As String 'Database name
strDbName = GetLinkedDBName(oTable)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim blnFieldExists As Boolean
''Set dbs = CurrentDb()
Set dbs = OpenDatabase(strDbName)
Set tdf = dbs.TableDefs(oTable)
For Each fld In tdf.Fields
If fld.Name = oColumn Then
blnFieldExists = True
Exit For
End If
Next
If Not blnFieldExists Then
If oType = "Date" Then
Set fld = tdf.CreateField(oColumn, dbDate)
fld.DefaultValue = "=Now()"
ElseIf oType = "Text" Then
Set fld = tdf.CreateField(oColumn, dbText)
Else
Set fld = tdf.CreateField(oColumn, dbText)
End If
tdf.Fields.Append fld
MsgBox "New column & '" & oColumn & "' added to table '" & oTable & "'"
End If
End Sub
Public Function GetLinkedDBName(TableName As String)
Dim db As DAO.Database, Ret
On Error GoTo DBNameErr
Set db = CurrentDb()
Ret = db.TableDefs(TableName).Connect
GetLinkedDBName = Right(Ret, Len(Ret) - (InStr(1, Ret, "DATABASE=") + 8))
Exit Function
DBNameErr:
GetLinkedDBName = 0
End Function
If you want to perform rename via code you need Jet SQL
ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)
Public Sub RenameColumn(ByVal tableName As String, ByVal oldName As String, ByVal newName As String)
Dim dbName As String
dbName = GetLinkedDBName(tableName)
Dim db As DAO.database
Set db = OpenDatabase(dbName)
Dim tdf As DAO.TableDef
Set tdf = db.tabledefs(tableName)
If (ExistInCollection(oldName, tdf.Fields)) Then
Dim field As DAO.field
Set field = tdf.Fields(oldName)
field.Name = newName
End If
End Sub
Public Function ExistInCollection(ByVal key As String, ByRef col As Object) As Boolean
ExistInCollection = ExistInCollectionByVal(key, col) Or ExistInCollectionByRef(key, col)
End Function
Private Function ExistInCollectionByVal(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
Dim item As Variant
item = col(key)
ExistInCollectionByVal = True
Exit Function
Error:
ExistInCollectionByVal = False
End Function
Private Function ExistInCollectionByRef(ByVal key As String, ByRef col As Object) As Boolean
On Error GoTo Error
Dim item As Variant
Set item = col(key)
ExistInCollectionByRef = True
Exit Function
Error:
ExistInCollectionByRef = False
End Function
Obviously renaming a Field in your database table could result in issues with objects that are dependent on that Field. You'd need to replace all instances of the old Field with the new Field in your database.
There's a new Find and Replace out that seems to be pretty robust:
https://accessdevtools.com/product/find-and-replace/?wmc-currency=USD
Seems like a good replacement for the older Rick Fisher product (that seems to no longer be in development).
Also, I don't think you can rename a column with the ALTER COLUMN DDL syntax. I believe you can only alter datatypes.
You could add a new column, update the new column with the contents of the old, and then drop the old column:
ALTER TABLE YourTable ADD NewColumn Text(50)
UPDATE YourTable SET NewColumn=OldColumn
ALTER TABLE YourTable DROP OldColumn
Or just use DAO:
Dim tbl As DAO.TableDef
Set tbl = Currentdb.TableDefs("YourTable")
tbl.Fields("OldColumn") = tbl.Fields("NewColumn")
but of course you can add a column ..
ALTER TABLE Employees ADD COLUMN ZipCode2 TEXT(10)
Update the new Column from the Old column
DROP the old column
To my knowledge, dependant objects (I mean objects living under the Field object) are not an issue, they'll update accordingly.
But if you're thinking about objects or code using the Field, that's a different issue (more related to side impacts and debugging) wich should be taken into consideration before renaming.
As for adding a new column, copying the old one, deleting the old one via SQL, that doesn't look efficient to me.
I'm referring to objects, like a Form that has a control on it with that field as the ControlSource. If you rename that field, you'd have to modify the ControlSource value for that control to reflect the new name.
As for adding a new column, copying the old one, deleting the old one via SQL, that doesn't look efficient to me.
I'm pretty sure that's what SQL Server and almost every other database does behind the scenes. I haven't run a trace (and I'm not sure a trace would even show that), but there's ample evidence to support it online (at least for DBs like MSSQL, MySQL, Oracle, etc).
That said, the DAO process seems to be simpler (at least to me).
Premium Content
You need an Expert Office subscription to comment.Start Free Trial