Identify or add a field to a remote table

I have a FE/BE database setup. The front end could link to many different
backend databases with similar strutctures. The applications have been
deployed to customers and I continue to modify the front end. On occassion
I need to add fields to the backend files that they maintain.
At the main switchboard form, on open, I would like to verify that a field
(as an example: txtName) exists in the linked backend table. If it doesn't
exist, I would like the code to automatically add the field to the backend
database that the user maintains.

Thanks in advance. This has been a sticky issue for me and I can't have
customers send me their datafiles just to add a field.

Beth

I use a function when the application is opened to find a value in a new
field and trap for error 3265. If this error occurs, I use DAO code to
modify the structure of a linked table.

This is some old code to add some fields to a linked table.

Function UpdateConstants() As Boolean
'================================================= ===========
' Purpose: Add Fields to tblConstants
' 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
'GetRemoteMDB() finds the linked mdb path
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 ='Admin', DataPWD
='data'")

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

Function GetRemoteMDB() As String
GetRemoteMDB = Mid(CurrentDb.TableDefs("tblDownTime").Connect, 11)
End Function

--
Duane Hookom
MS Access MVP

"Beth" wrote in message
. com...
I have a FE/BE database setup. The front end could link to many different
backend databases with similar strutctures. The applications have been
deployed to customers and I continue to modify the front end. On occassion
I need to add fields to the backend files that they maintain.
At the main switchboard form, on open, I would like to verify that a field
(as an example: txtName) exists in the linked backend table. If it
doesn't exist, I would like the code to automatically add the field to the
backend database that the user maintains.

Thanks in advance. This has been a sticky issue for me and I can't have
customers send me their datafiles just to add a field.

Beth

Awesome! That will work perfectly for my situation. Thanks
Beth


"Duane Hookom" DuaneAtNoSpanHookomDotNet wrote in message
...
I use a function when the application is opened to find a value in a new
field and trap for error 3265. If this error occurs, I use DAO code to
modify the structure of a linked table.

This is some old code to add some fields to a linked table.

Function UpdateConstants() As Boolean
'================================================= ===========
' Purpose: Add Fields to tblConstants
' 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
'GetRemoteMDB() finds the linked mdb path
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 ='Admin',
DataPWD ='data'")

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

Function GetRemoteMDB() As String
GetRemoteMDB = Mid(CurrentDb.TableDefs("tblDownTime").Connect, 11)
End Function

--
Duane Hookom
MS Access MVP

"Beth" wrote in message
. com...
I have a FE/BE database setup. The front end could link to many different
backend databases with similar strutctures. The applications have been
deployed to customers and I continue to modify the front end. On
occassion I need to add fields to the backend files that they maintain.
At the main switchboard form, on open, I would like to verify that a
field (as an example: txtName) exists in the linked backend table. If it
doesn't exist, I would like the code to automatically add the field to
the backend database that the user maintains.

Thanks in advance. This has been a sticky issue for me and I can't have
customers send me their datafiles just to add a field.

Beth