[AccessD] Linking to Separate database

Thanks Stuart.  That has got me under way.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, 12 December 2012 6:39 p.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Linking to Separate database

I'd do it completely in a VBA function:

1. Create links to the network tables
2. Run update queries to update the network tables from the local tables 3.
Run update queries to rebuild the local tables from the updated network
tables 4. Delete the links.

To create the links, something like:

Const strBEName = "xxxxxxx"
Const strServerBEDir = "\\Server\Share"

Function LinkServerBE() As Long
Dim tdf As TableDef
If Dir$(strServerBEDir & "\" & strBENAme < " " Then
     MsgBox "Server Data file not found! ", vbCritical, "UpdateData Failed!"
     Exit Function
End If
Renewlink "tblOne", strServerBEDir & "\" & strBENAme Renewlink "tblTwo",
strServerBEDir & "\" & strBENAme Renewlink "tblThree", strServerBEDir & "\"
& strBENAme Renewlink "tblFour", strServerBEDir & "\" & strBENAme End
Function

Function renewlink(tablename As String, datafile As String) As Long 'Delete
link if it exists On Error Resume Next DoCmd.DeleteObject acTable, "SVR" &
tablename On Error GoTo 0 DoCmd.TransferDatabase acLink, "Microsoft Access",
datafile, acTable, tablename, "SVR" & tablename, False End Function

--
Stuart

On 12 Dec 2012 at 17:15, David Emerson wrote:

> I have an Access 2010 database that resides on a network.
> 
> The datafile is to be copied onto laptops for technicians to be able 
> to enter data out in the field with their own Front ends.  When they 
> return to the office the idea is to connect the laptop to the network 
> then update certain records onto the  main datafile.  The records will 
> already be created before the technicians get their copy.  All the 
> technicians will be doing is updating existing records.  They will not 
> create new records or delete any.  Records will only be updated by one 
> laptop between synchronizations so there is no need to manage 
> conflicts.  The updating is to be initiated from the laptop FE.
> 
> I need to be able to link the laptop FE to the laptop datafile but 
> also have a way to update the datafile on the network.  My initial 
> thought was to have the relevant tables also linked in the laptop FE 
> (but of course the link will only be valid if they are connected to 
> the network).  There are only 4 tables that will need to have records
copied over.
> 
> Am I going about this the right way, or is there an easier way within 
> code for creating the links when the synchronizing is to take place?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand