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.
An Experts Exchange subscription includes unlimited access to online courses.Get Started
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