I looking for way to reliably automate the process of upgrading users to a
new version of an Access MDE application. I've tried this code a few times
and it seems to work (gets custom menubars, too) but was hoping I could get
a sanity check.
Public Function ImportData() As Boolean
On Error Go To Exit_Here
Dim db As DAO.Database
Dim cdb As DAO.Database
Dim tdf As DAO.TableDef
Dim rel As DAO.Relation
Dim nrel As DAO.Relation
Dim strTDef As String
Dim strRName As String
Dim strTName As String
Dim fld As DAO.Field
Dim strFTName As String
Dim varAtt As Variant
Dim strFName As String
Dim strFFName As String
Dim strOldver As String
strOldver = Oldver 'Oldver is a function that gets the name of the old
version
Set cdb = CurrentDb
Set db = DBEngine.Workspaces(0).OpenDatabase(Oldver, True)
For Each tdf In db.TableDefs
strTDef = tdf.Name
If Left(strTDef, 4) <> "MSys" Then
DoCmd.TransferDatabase acImport, "Microsoft Access", strOldver,
acTable, _
strTDef, strTDef, False
End If
Next
cdb.TableDefs.Refresh
For Each rel In db.Relations
With rel
'get properties of relation to copy
strRName = .Name
strTName = .Table
strFTName = .ForeignTable
varAtt = .Attributes
'create relation in current db with same properties
Set nrel = cdb.CreateRelation(strRName, strTName, strFTName,
varAtt)
For Each fld In .Fields
strFName = fld.Name
strFFName = fld.ForeignName
nrel.Fields.Append nrel.CreateField(strFName)
nrel.Fields(strFName).ForeignName = strFFName
Next
cdb.Relations.Append nrel
End With
Next
cdb.Relations.Refresh
ImportData = True
Exit_Here:
db.Close
Set db = Nothing
Set cdb = Nothing
End Function
The idea is that users would get a new MDE file with no tables - when first
launched, a form would appear with this code behind it. The user types in
the previous version number (of the database with all his precious data) and
clicks "Upgrade Now". Hopefully, everything gets imported properly... and
the new version is deployed. Sound reasonable? (I know... split the
database... that's a work in progress)
Thanks in advance.
