mdb field maintenance

  • Thread starter Thread starter Bill
  • Start date Start date
B

Bill

Assuming I'll eventually need some code, I've
chosen to post this forum. For starters, I would
appreciate a few suggestive words of wisdom
as regards field maintenance of split configurations.

First, what seems to work well for marking the
backend mdb as regards compatibility? My first
thought is to somehow use the database properties
to mark the database at a given level. Then, when
the front-end mde opens, I can check the level of
the database and perform any required updates
to table definitions, etc.. and then mark the mdb
to the updated level.

I've never had an occasion to perform table
definition updates from code nor write anything
into the database property sheet. So, I thought
it best here to ask about what scheme is generally
accepted as the norm.

Any guidance will be appreciated.

Thanks,
Bill
 
Allen,
Thanks for your reply. Perhaps I wasn't quite as clear as
I had intended. Yes, the application updates usually pertain
to the front-end with the advent of enhancements to its
functionality and/or embellishments of some sort.... maybe
even the rare occurrence of a "bug-fix". :-)

What I was trying to get at was when a change in the front-end
imposes a corresponding change in table(s) and/or table-fields
in the back-end, I need for the front-end application to quickly
check the back-end at startup to see if "one-time" utility code
needs to run to augment or otherwise apply a change to an
out-dated back-end mdb.

I will read Toews' utility.

Thanks again,

Bill
 
Bill said:
Assuming I'll eventually need some code, I've
chosen to post this forum. For starters, I would
appreciate a few suggestive words of wisdom
as regards field maintenance of split configurations.

First, what seems to work well for marking the
backend mdb as regards compatibility? My first
thought is to somehow use the database properties
to mark the database at a given level. Then, when
the front-end mde opens, I can check the level of
the database and perform any required updates
to table definitions, etc.. and then mark the mdb
to the updated level.

I've never had an occasion to perform table
definition updates from code nor write anything
into the database property sheet. So, I thought
it best here to ask about what scheme is generally
accepted as the norm.
Click to expand...


That approach works ok for very simple changes such as
adding a new field. For more complex structure changes,
safety requires you to make absolutely certain that all
users are locked out while you modify the back end. Also,
you really should make two backup copies before doing the
update, one for archive purposes and the other for you to
modify. The point of that is to preface the idea of using
an AppUpdate mdb file to do all the management and
modifications to the back end.

To help me create the procedure to do the updates, I never
edit my test copy of the back end by using the table design
UI features. Instead, when I determine a structure change
is needed, I add the VBA code to make the change using DAO
and/or SQL DDL.

If you try to pack all that into the front end you have the
additional complication of determining if it's safe to
proceed during normal user operations plus the further
messiness of the front end trying to determine if it's a new
version that needs to update the back end, the current
version that should do nothing, or the old version that
should replace itself.

As Allen pointed out, once the back end is modified, Tony's
FEupdater can be used to distribute the new front end.
 
Bill,

Here is an example of code I have used recently for this purpose...

Private Sub Form_Load()
Dim strSQL As String
Dim dbs As DAO.Database
If FieldExists("SchoolDetails", "SMTPServer") Then
' do nothing
Else
Set dbs = OpenDatabase(fGetLinkPath("Courses") &
"Matrices_be.mdb")
strSQL = "ALTER TABLE SchoolDetails ADD COLUMN SMTPServer
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
 
Steve,
I think that what you've offered with your code is essentially
all I need to do the simple table-field additions. If the time
comes where more complex types of back-end updating
is required, I will heed Marshall's words of caution.

As an aside, I attempted to lookup "TableDef" in the A2K VBA
Help TOC. I get several matches but get no response when
I click on any of the related topics. I've made several attempts
in the past to answer why this occurs, including re-installing
O2K and re-applying the SR1 and SP2/SP3 updates. Do I
need to download VBA appendages or something like that?

Thanks,
Bill
 
Back
Top