How to add new fields to linked table in backend MDB using code.

[Huayang]

I want to add new fields to the linked table in the backend MDB
Using VBA code, How to do it?
Thanks

 

[Jeff Boyce]

Is there a reason you aren't simply opening the backend MDB and entering the
fields there?

 

[Duane Hookom]

Here are a couple DAO functions that I have used. It isn't "generic" in that
you need to hard-code your field and table names in the code.

Function GetRemoteMDB() As String
'substitute one of your attached tabled in the next line
GetRemoteMDB = Mid(CurrentDb.TableDefs("tblDownTime").Connect, 11)
End Function


Function UpdateConstants() As Boolean
'============================================================
' Purpose: Add Fields to tblConstants
' Programmer:
' Called From:
' Date: 11/29/2000
' Parameters:
'============================================================
On Error GoTo UpdateConstants_Err
Dim strErrMsg As String 'For Error Handling
UpdateConstants = True
Dim dbRemote As DAO.Database
Dim tdef As DAO.TableDef
Dim fld As DAO.Field
Set dbRemote = OpenDatabase(GetRemoteMDB())
Set tdef = dbRemote.TableDefs("tblSysConstants")
With tdef
.Fields.Append .CreateField("DataPWD", dbText, 30)
.Fields.Append .CreateField("AdminPWD", dbText, 30)
.Fields.Append .CreateField("HideSplash", dbBoolean)
End With
dbRemote.Execute ("Update tblSysConstants SET AdminPWD ='xxxx', DataPWD
='xxxx'")

UpdateConstants_Exit:
On Error Resume Next
Set tdef = Nothing
Set dbRemote = Nothing
Set fld = Nothing
Exit Function

UpdateConstants_Err:
Select Case Err
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description
MsgBox strErrMsg, vbInformation, "UpdateConstants"
UpdateConstants = False
Resume UpdateConstants_Exit
End Select
End Function