Doug Murphy
dw-murphy at cox.net
Sat Dec 20 14:11:48 CST 2003
Very Cool!!!
Doug
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
(AccessD)
Sent: Saturday, December 20, 2003 11:08 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] "Faked" replication
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com