> > Bottom line: if you want to follow best practices in a multi-user
environment, split the database; in a single-user environment, don't
split.
The final decision is yours to make. At least now you are aware of the
option.
I still need a way to for users to easily upgrade to new versions. This
code imports all tables and relationships from the previous version, which
seems to do the trick.
When distributing a new version of the mde, I delete all the tables and set
the Startup Form to "frmUpgrade", which has code to set the Startup Form
back to what it should be after the upgrade is completed. When the new mde
is first opened, the user is prompted for the previous version number - so I
can add code to accommodate upgrades from different versions.
[Form_frmUpgrade]
Private Sub cmdImportData_Click()
Dim db As Object
If Not IsNumeric(Me.txtOldver) Or IsNull(Me.txtOldver) Then Exit Sub
'add code here to accommodate different versions, or if
'data structures need to be changed, or other
'version-specific stuff needs to be done to upgrade
If modUpgrade.ImportData Then
MsgBox ("Data Import Successful")
DoCmd.Close acForm, "frmUpgrade"
Set db = Application.CurrentDb
db.Properties("StartupForm") = "frm0"
DoCmd.OpenForm "frm0"
Else
MsgBox ("Data Import Failed")
End If
End Sub
ImportData does all the work - code adapted from a Microsoft KB article.
[modUpgrade]
Public Function ImportData() As Boolean
On Error GoTo HandleErr
Dim db As DAO.Database
Dim cdb As DAO.Database
Dim tdf As TableDef
Dim rel As Relation
Dim nrel As Relation
Dim r, t As Integer
Dim strTDef As String
Dim strRName As String
Dim strTName As String
Dim fld As Field
Dim strFTName As String
Dim varAtt As Variant
Dim strFName As String
Dim strFFName As String
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", Oldver,
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
Set db = Nothing
Set cdb = Nothing
End Function
Oldver takes the version number entered on frmUpgrade
[modUpgrade]
Private Function Oldver() As String
On Error GoTo HandleErr
Dim strDBPath As String
Dim strDBFile As String
Dim strOldver As String
strOldver = "data" & Forms("frmUpgrade").txtOldver & ".mde"
strDBPath = CurrentDb.Name
strDBFile = Dir(strDBPath)
Oldver = Left$(strDBPath, Len(strDBPath) - Len(strDBFile)) & strOldver
End Function
