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
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE