Its easy enough to do, but I don't see that it offers any advantages.
In fact there are some things which don't work with linked tables
which do with local tables, so you could accidentally find your self
including some functionality at home which won't work back at the
office. I'll deal first with leaving it split, then with
'unsplitting' it:
1. All you need to do is copy the front end file and the back end
file onto your hone system. They can both go in the same folder or in
different folders. Then open the front end and use the built in
Linked Table Manager to refresh the links to the new location of the
back end file on your home system.
When you copy the front end back to the office system you'll need to
use the Linked Table Manager to refresh the links back to the network
location of course, but it only takes a few moments to do this. Or
you can automate the process. Some years ago I did post a means of
doing this by checking for invalid links at start-up for both single
and multiple back ends at:
http://community.netscape.com/n/pfx/forum.aspx?msg=19444.1&nav=messages&webtag=ws-msdevapps
2. To 'unsplit' a database you delete all the linked tables from the
front end; this deletes only the links not the tables in the back
end. Then import all the tables from the back end via the File |Get
External data | Import menu item on the main database menu bar (or the
equivalent in 2007). Or you can automate it with the following
function:
Public Function CopyTables(dbsSource As Database, strSourcedb As
String)
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field, newfld As DAO.Field
Dim rel As DAO.Relation, newrel As DAO.Relation
Dim lngCount As Long, lngN As Long
Dim retVal As Variant
Set dbs = CurrentDb
'loop through tabledefs collection and copy tables
lngCount = dbsSource.TableDefs.Count
retVal = SysCmd(acSysCmdInitMeter, "Copying Tables", lngCount)
lngN = 1
For Each tdf In dbsSource.TableDefs
If Left(tdf.Name, 4) <> "MSys" Then
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
DoCmd.TransferDatabase acImport, "Microsoft Access", _
strSourcedb, acTable, tdf.Name, tdf.Name
End If
Next tdf
' rebuild relationships
lngCount = dbsSource.Relations.Count
retVal = SysCmd(acSysCmdInitMeter, "Building Relationships",
lngCount)
lngN = 1
For Each rel In dbsSource.Relations
retVal = SysCmd(acSysCmdUpdateMeter, lngN)
lngN = lngN + 1
' Create new Relation object
Set newrel = dbs.CreateRelation(rel.Name, rel.Table,
rel.ForeignTable)
' Set attributes
newrel.Attributes = rel.Attributes
For Each fld In rel.Fields
' Create field in Relation object.
Set newfld = newrel.CreateField(fld.Name)
' Specify field name in foreign table.
newfld.ForeignName = fld.ForeignName
' Append Field object to Fields collection of Relation
object.
newrel.Fields.Append newfld
Next fld
' Append Relation object to Relations collection.
dbs.Relations.Append newrel
dbs.Relations.Refresh
Next rel
Exit_Here:
' clear status bar
retVal = SysCmd(acSysCmdClearStatus)
Set dbsSource = Nothing
Exit Function
Err_Handler:
MsgBox Err.Description
Resume Exit_Here
End Function
The function is called like so:
Dim dbs As DAO.Database, dbSource As DAO.Database
Dim strSourcedb As String
strSource db = <path to database from which tables to be imported>
Set dbSource = OpenDatabase(strSourcedb)
CopyTables dbSource, strSourcedb
Ken Sheridan
Stafford, England
