Update Table Designs

[KenInBrantford]

MS Access 97, 2000, 2002, 2003
New here - hope Iv'e got this in the right place.
This must be common knowlege, but I can't seem to find the answer.

As a developer, how do I send to a client, table design updates that will
update all the client's tables and data to the new table and field structures?

Hopefully not by replacing the MDB file and doing an export and re-import of
all data.
Thanks for any help.

 

[John Vinson]

MS Access 97, 2000, 2002, 2003
New here - hope Iv'e got this in the right place.
This must be common knowlege, but I can't seem to find the answer.

As a developer, how do I send to a client, table design updates that will
update all the client's tables and data to the new table and field structures?

Hopefully not by replacing the MDB file and doing an export and re-import of
all data.
Thanks for any help.

That's often exactly how you must do it. :-{(

You can write VBA (or with the 2002/2003 version of Access, DDL
queries) to make some changes to table and field structures; but
generally, you don't want to do this because it IS a huge hassle.
Ideally you've thought through and implemented all of the
normalization and the data requirements before deployment. In the real
world, you can't always do this (blankety-blank customers use the
database for a week and say "but it doesn't have a field for <fitb>"
which of course wasn't ever discussed in design or pilot testing...)

One thing I've done with some clients is to persuade them to install
Citrix (there are other similar programs) and actually log on to their
computers remotely, open Access, and make the changes directly. Oh
yes... BACK THINGS UP FIRST!!


John W. Vinson[MVP]

 

[Jerry Whittle]

What - you don't have the requirements perfectly nailed down at the
beginning? ;-) Welcome to my world.

I often add extra fields in tables such as a couple of text (255), number
(double), date/time, YesNo, and even Memo fields. This has saved my bacon
when the database is 400 miles away. It doesn't cure all the problems;
however, it helps. I just send a new FE taking advantage of these spare
fields.

Otherwise I've talked knowledgeable users through changes over the
telephone. There's also burning the midnight oil when users aren't on the
database to fix things. Email and FTP work here.

 

[KenInBrantford]

Thanks Guys;
Looks like we're in the same world.
I was hoping it would be easy, like it is in DataEase(usually).
You've been so good I may try this again.

 

[Duane Hookom]

Here is some DAO code that I have used. Normally I would run some code at
the start of the application to check to see if a table contains a field. If
I generate an error, I create the new field.

This entire process could be made more generic but I was billing by the hour
;-)

Watch for line wrapping in this code....

Function CheckVersion4() As Boolean
'============================================================
' Purpose: Check to see if table structures match latest version
' Programmer: Duane Hookom
' Called From:
' Date: 11/29/2000
' Parameters:
'============================================================
On Error GoTo CheckVersion4_Err
Dim strErrMsg As String 'For Error Handling

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strRemoteDB As String
Dim strTableName as String
strTableName = "tblSysConstants"
CheckVersion4 = True

Set db = OpenDatabase(GetRemoteMDB(strTableName))
Set rs = db.OpenRecordset(strTableName)
With rs
.MoveFirst
Debug.Print !AdminPWD
.Close
End With

CheckVersion4_Exit:
On Error Resume Next
Set rs = Nothing
Set db = Nothing
Exit Function

CheckVersion4_Err:
Select Case Err
Case 3265 'Item not found in this collection ie: field doesn't exist
CheckVersion4 = False
Resume CheckVersion4_Exit
Case Else
strErrMsg = strErrMsg & "Error #: " & Format$(Err.Number) &
vbCrLf
strErrMsg = strErrMsg & "Error Description: " & Err.Description
MsgBox strErrMsg, vbInformation, "CheckVersion"
Resume CheckVersion4_Exit
End Select
End Function


Function UpdateConstants() As Boolean
'============================================================
' Purpose: Add Fields to tblConstants
' Programmer: Duane Hookom
' 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
Dim strTableName as String
strTableName = "tblSysConstants"
Set dbRemote = OpenDatabase(GetRemoteMDB(strTableName))
Set tdef = dbRemote.TableDefs(strTableName)
With tdef
.Fields.Append .CreateField("DataPWD", dbText, 30)
.Fields.Append .CreateField("AdminPWD", dbText, 30)
.Fields.Append .CreateField("HideSplash", dbBoolean)
End With

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(pstrTable as String) As String
GetRemoteMDB = Mid(CurrentDb.TableDefs(pstrTable).Connect, 11)
End Function