Even if all your data is in Access itself, consider using linked
tables. Store all the data tables in one MDB or ACCDB file - the
data file - and the remaining objects (queries, forms, reports, macros, and modules) in a second MDB - the
application file.
In multi-user situations, each user receives a local copy of the
application file, linked to the tables in the single remote data file.
Why split?
There are significant advantages to splitting your application:
- Maintenance: To update the program, just replace the application file. Since the data is in a separate file, no data is overwritten.
- Network Traffic: Loading the entire application (forms, controls, code, etc) across the network increases traffic making your interface slower.
Access includes an
Add-In to perform the split:
| Access 95 - 97 |
Tools | Add-Ins | Database Splitter |
| Access 2000 - 2003 |
Tools | Database Utilities | Database Splitter |
| Access 2007 - 2010 |
Database Tools | Move Data | Access Back-End |
In some cases you will link
additional files:
- Static look-up data such as postal codes might be kept in its own file.
- Linked temporary tables might avoid the need to compact the application file.
Is the Data present?
Once split, the data may be unavailable to the application. This
happens if the network is down, the data file is moved, or
folder/network names are altered.
For a single-user set-up where the application and data files are in the same folder, see
Peter Vukovic's tip:
Function Reconnect ()
Dim db As Database, source As String, path As String
Dim dbsource As String, i As Integer, j As Integer
Set db = dbengine.Workspaces(0).Databases(0)
For i = Len(db.name) To 1 Step -1
If Mid(db.name, i, 1) = Chr(92) Then
path = Mid(db.name, 1, i)
Exit For
End If
Next
For i = 0 To db.tabledefs.count - 1
If db.tabledefs(i).connect <> " " Then
source = Mid(db.tabledefs(i).connect, 11)
For j = Len(source) To 1 Step -1
If Mid(source, j, 1) = Chr(92) Then
dbsource = Mid(source, j + 1, Len(source))
source = Mid(source, 1, j)
If source <> path Then
db.tabledefs(i).connect = ";Database=" + path + dbsource
db.tabledefs(i).RefreshLink
End If
Exit For
End If
Next
End If
Next
End Function
If the files are in different folders (e.g. across a network), see
Dev Ashish's article:
(Q) In my Front end database, I have several linked tables from
multiple back end databases. How can I make sure that all tables get connected when
the front end is opened?
(A) You can go through the TableDefs collection to see which tables
have the Connect property set. If Connect is populated, reconnect the table using
the database specified in the string.
Here's a function (
fRefreshLinks) that could be run
at db startup. The function looks at each table in the database in which the code is
running, and tries to find the datasource for that table if the Connect property is
populated.
If the database specified for the linked table is not present, the
code brings up the
GetOpenFileName dialog so that the user can
select an alternative source.
Note: You must include that code in your
application for this example to work
Function fRefreshLinks() As Boolean
Dim strMsg As String, collTbls As Collection
Dim i As Integer, strDBPath As String, strTbl As String
Dim dbCurr As DATABASE, dbLink As DATABASE
Dim tdfLocal As TableDef
Dim varRet As Variant
Dim strNewPath As String
Const cERR_USERCANCEL = vbObjectError + 1000
Const cERR_NOREMOTETABLE = vbObjectError + 2000
On Local Error GoTo fRefreshLinks_Err
If MsgBox("Are you sure you want to reconnect all Access tables?", _
vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL
Set collTbls = fGetLinkedTables
Set dbCurr = CurrentDb
strMsg = "Do you wish to specify a different path for the Access Tables?"
If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then
strNewPath = fGetMDBName("Please select a new datasource")
Else
strNewPath = vbNullString
End If
For i = collTbls.Count To 1 Step -1
strDBPath = fParsePath(collTbls(i))
strTbl = fParseTable(collTbls(i))
varRet = SysCmd(acSysCmdSetStatus, "Now linking '" & strTbl & "'....")
If Left$(strDBPath, 4) = "ODBC" Then
Else
If strNewPath <> vbNullString Then
strDBPath = strNewPath
Else
If Len(Dir(strDBPath)) = 0 Then
strDBPath = fGetMDBName("'" & strDBPath & "' not found.")
If strDBPath = vbNullString Then
'user pressed cancel
Err.Raise cERR_USERCANCEL
End If
End If
End If
Set dbLink = DBEngine(0).OpenDatabase(strDBPath)
strTbl = fParseTable(collTbls(i))
If fIsRemoteTable(dbLink, strTbl) Then
Set tdfLocal = dbCurr.TableDefs(strTbl)
With tdfLocal
.Connect = ";Database=" & strDBPath
.RefreshLink
collTbls.Remove (.Name)
End With
Else
Err.Raise cERR_NOREMOTETABLE
End If
End If
Next
fRefreshLinks = True
varRet = SysCmd(acSysCmdClearStatus)
MsgBox "All Access tables were successfully reconnected.", _
vbInformation + vbOKOnly, _
"Success"
fRefreshLinks_End:
Set collTbls = Nothing
Set tdfLocal = Nothing
Set dbLink = Nothing
Set dbCurr = Nothing
Exit Function
fRefreshLinks_Err:
fRefreshLinks = False
Select Case Err
Case 3059:
Case cERR_USERCANCEL:
MsgBox "No Database was specified, couldn't link tables.", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case cERR_NOREMOTETABLE:
MsgBox "Table '" & strTbl & "' was not found in the database" & _
vbCrLf & dbLink.Name & ". Couldn't refresh links", _
vbCritical + vbOKOnly, _
"Error in refreshing links."
Resume fRefreshLinks_End
Case Else:
strMsg = "Error Information..." & vbCrLf & vbCrLf
strMsg = strMsg & "Function: fRefreshLinks" & vbCrLf
strMsg = strMsg & "Description: " & Err.Description & vbCrLf
strMsg = strMsg & "Error #: " & Format$(Err.Number) & vbCrLf
MsgBox strMsg, vbOKOnly + vbCritical, "Error"
Resume fRefreshLinks_End
End Select
End Function
Function fIsRemoteTable(dbRemote As DATABASE, strTbl As String) As Boolean
Dim tdf As TableDef
On Error Resume Next
Set tdf = dbRemote.TableDefs(strTbl)
fIsRemoteTable = (Err = 0)
Set tdf = Nothing
End Function
Function fGetMDBName(strIn As String) As String
Dim strFilter As String
strFilter = ahtAddFilterItem(strFilter, _
"Access Database(*.mdb;*.mda;*.mde;*.mdw) ", _
"*.mdb; *.mda; *.mde; *.mdw")
strFilter = ahtAddFilterItem(strFilter, _
"All Files (*.*)", _
"*.*")
fGetMDBName = ahtCommonFileOpenSave(Filter:=strFilter, _
OpenFile:=True, _
DialogTitle:=strIn, _
Flags:=ahtOFN_HIDEREADONLY)
End Function
Function fGetLinkedTables() As Collection
'Returns all linked tables
Dim collTables As New Collection
Dim tdf As TableDef, db As DATABASE
Set db = CurrentDb
db.TableDefs.Refresh
For Each tdf In db.TableDefs
With tdf
If Len(.Connect) > 0 Then
If Left$(.Connect, 4) = "ODBC" Then
Else
collTables.Add Item:=.Name & .Connect, Key:=.Name
End If
End If
End With
Next
Set fGetLinkedTables = collTables
Set collTables = Nothing
Set tdf = Nothing
Set db = Nothing
End Function
Function fParsePath(strIn As String) As String
If Left$(strIn, 4) <> "ODBC" Then
fParsePath = Right(strIn, Len(strIn) _
- (InStr(1, strIn, "DATABASE=") + 8))
Else
fParsePath = strIn
End If
End Function
Function fParseTable(strIn As String) As String
fParseTable = Left$(strIn, InStr(1, strIn, ";") - 1)
End Function
Another Interesting Topics:Armen Stein has released a relinker that supports linking to multiple back ends :
J Street Access Relinker
