[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
> 
>  
> 
>  
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>