Updating a table in backend database

[Guest]

I have an application that is in 50 different places. When updating the appl
I can send the updated mde file vie e-mail or other methods. But what if I
need to add a column to a database table? Is there a way that through code I
can test for the new column and if it isn't in the table to be able to add
it.
Thanks for any help.

 

[Steve Schapel]

Tom,

Here's one possibility - you can put code along these lines in the
distributed frontend file...

-------------------------
Private Sub Form_Load()
Dim dbs As Database
Dim strSQL As String
If FieldExists("TheTable", "TheField") Then
' do nothing
Else
Set dbs = OpenDatabase("C:\PathToBackend\YourBackend.mdb")
strSQL = "ALTER TABLE [TheTable] ADD COLUMN [TheField] TEXT(50)"
dbs.Execute strSQL, dbFailOnError
Set dbs = Nothing
End If
End Sub
------------------
Public Function FieldExists(strTable As String, strField As String) As
Boolean
Dim strName As String
On Error GoTo fe_err
strName = CurrentDb.TableDefs(strTable).Fields(strField).Name
FieldExists = True
Exit Function
fe_err:
If Err.Number = 3265 Then
FieldExists = False
Else
MsgBox Err.Description
End If
End Function