Jim Lawrence (AccessD)
accessd at shaw.ca
Sat Dec 20 13:08:07 CST 2003
Hi Steve:
I definitely sympathize with your situation. I have been there more times
that I wish to admit and I will be there again.
At the risk of flooding the DBA site, here is the simplist solution I can
think of. This is the method I would recommend for you. It is quick to setup
and requires a minimum of expense. The one issue is that each remote station
must be brought into the central office for initial configuration. When I
did this setup, it was on Access97 and there was some remote access hub
called a 'Rover' (I am not sure of the name but was a few years ago.)
First:
Get a copy of PCAnywhere and install a Host copy on the station that will be
used at the main office. It must have a dedicated phone line or access to
high speed connection. It does not have to be used as a dedicated Host
station but I would strongly recommend it. Setup the Host station for a
connection for each Remote user given the appropriate username and password.
The current versions of PCAnywhere have a very good simple to use security
system.
Second:
On each station, that will be used remotely, setup a PCAnywhere Remote and
setup a connection that matches an access to the Host station. I do not have
to say this but test each Remote station thoroughly, for a reliable
connection and login capabilities.
Third:
Once you are satisfied the connections from the stations, to be used
remotely to the Host station, is working properly then it is time to create
Replicates from the Master Access MDB. I will assume that you have, but it
is best to setup your Access application so that one MDB has the data (BE)
and one has the code (FE) and the two are linked together. If you do not you
must do this first...not a particularly difficult task. Replicates are easy
to spawn but there are a few things to consider. It is best to create them
in the same directory tree structure as they will be installed on the remote
stations. I would make a whole lot of extras because if/when a replicate
goes west it is almost impossible to rebuild and if there are extras, one
can just be synchronized and shipped/installed on the corrupted Remote. If
you want all the remotes to have the identical directory structures, which
makes sense, you will have to copy each created replicate off to a safe
location, after it is generated. I made a CDROM backup with nicely labeled
directory tree, of all the Replicate copies... and it served it purpose.
Fourth:
I created a couple of simple routines to handle the synchronization between
the Host station and the Remote stations as each logged in. When the
stations have connected remotely to the Host, the user would press a button
in Application and the process would begin. Below is the code:
' This is the code attached to the command button click event
Private Sub cmdSynchonize_Click()
Dim StartTime As Variant
Dim EndTime As Variant
Dim response As Integer
On Error GoTo Err_Sync
' Check to see if the MDB is where it is suppose to be
With Application.FileSearch
.LookIn = "c:\appl"
.FileName = "TheDatabase.mdb"
If .Execute() > 0 Then
if MsgBox("Do you wish to proceed with Synchronization?", vbQuestion +
vbOKCancel + vbDefaultButton2, "Synchronize") = vbYes then
StartTime = Time()
DoCmd.Hourglass True
' The c:\appl\TheDatabase is the remote station's 'Replicate mdb'
' and \\MyHostServer\appl\TheDatabase is the host station's 'Master mdb'
Call SynchronizeDB("c:\appl\TheDatabase.mdb",
"\\MyHostServer\appl\TheDatabase.mdb")
DoCmd.Hourglass False
EndTime = Time()
MsgBox "Synchronization Completed:" & StartTime & " to " & EndTime,
vbInformation + vbOKOnly, "Synchronization Complete"
End If
Else
MsgBox "Synchronization can not be used at this site.", vbInformation +
vbOKOnly, "Synchronization"
End If
End With
Exit_Sync:
Exit Sub
Err_Sync:
MsgBox Err.Number & " " & Err.Description, vbCritical + vbOKOnly
DoCmd.Hourglass False
Resume Exit_Sync
End Sub
' ...and the simple code that does all the work.
Public Function SynchronizeDB(strSource, strDestination)
Dim db1 As Database
Set db1 = DBEngine(0).OpenDatabase(strSource)
db1.Synchronize strDestination, dbRepImpExpChanges
db1.Close
End Function
In summary, this is not the optimum method for setting up a Host and Remote
network but the price is fairly reasonable and once things are in place a
new station can be rolled out real quickly...1 hour per. PCAnywhere, for all
its weirdnesses does create a suede VPN that is decently reliable and
handles a lot of the problem issues like Host servers, modems, security,
router etc. It even has a scripting language that can be automated for
anyother uploads or downloads. And did I mention, inexpensive. The
synchronization process is very quick, traditionally about two minutes per
station, given a medium level of changes to the central MDB and the remotes
connect fairly frequently. Note, that the data will not be complete until
all the remotes have all logged in and logged in again...a two pass
operation. I had a similar setup about six years and it ran very well for
almost five years.
If you decide on this route I will be able to help you navigate through some
of the computer reefs.
HTH
Jim
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Steven W.
Erbach
Sent: Friday, December 19, 2003 3:12 PM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] "Faked" replication
Eric,
>> With VB and ASP you can have the local Access db update a remote Access
db. <<
Well, yeah, but that doesn't help. I would need to re-write the entire
application from scratch and learn a new language to boot.
Regards,
Steve Erbach
Scientific Marketing
Neenah, WI
920-969-0504
Message created with Bloomba
Disclaimer: No tree was killed in the transmission of this message. However,
several coulombs of electrons were temporarily inconvenienced.
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com