Updating backend MDB tables, fields, relationships and indexes
Short posting
I like using the Compare'Em utility which creates the necessary VBA code to create/update tables, fields, relationships and indexes. It works by comparing your last version of the BE MDB with the latest development version of the BE MDB. While I don't need the Pro version nevertheless I paid the $10 for it. It does have it's quirks about which I've emailed the author.
Long posting
I have a one record table in the front end and back end MDB with the FE and BE version number. Whenever I open the FE MDB I run code which compares the version numbers in the current FE MDB and BE MDB. If different the code then executes subroutines based on the version on the BE. As each version of the is updated I update the version field to the successfully updated version. I also run the code if the user switches from one BE MDB to another.
I also execute queries in the midst of the above created VBA code to clean up data as required. For example I recently created a service techs table in the Granite Fleet Manager. In the past the service techs fields was just a string field on the service order table.
' Append records to ServiceRecords table
strSQL = "INSERT INTO ServiceTech ( stServiceTech ) " & _
" IN '" & strDatabasePathandName & "' " & _
"SELECT srServicePerson FROM ServiceRecords " & _
" IN '" & strDatabasePathandName & "' " & _
"GROUP BY srServicePerson " & _
"HAVING (srServicePerson Is Not Null);"
CurrentDb.Execute strSQL, dbFailOnError
Note the IN statement which allows you to update tables in MDBs which aren't linked. Of course I had to then update the service order with the autonumber ID in the above just created service techs table and then delete the field from the service order table.
As you well imagine there are many variations of John Smith in the various service orders. John, John S, John S., and so forth. So I gave the users a form to clean up the data. (The High Rate record was for testing a high rate of pay and invoicing to ensure there was lots of room on various forms and reports.)
Mechanics aren't paid for their typing or spelling. <smile>