Solved

Remote Replication via phone (dialup)

Posted on 2001-09-03
18
328 Views
Last Modified: 2008-02-01
Hi,
I have an access db on my laptop which is replica of the db on my pc at office. How can replicate via phone, without incolve internet? I need to make a button to :
a. Call my office number
b. Make the connection throu single modem
c. Replicate
d. Close.

It is urgent to do that because my home from office is 185 km and it is realy hard to go there every day.

Thank you for understanding.
Elte
0
Comment
Question by:elte
  • 7
  • 4
  • 2
  • +3
18 Comments
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6451758
Easy enough to do the dial up thing. It's the answering at the other end that will cause you the most difficulties.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6451803
I guess a product like PC-Anywhere could help you to make a rather secure connection. One of the options is the possibility to connect and let the Office PC call you back. Thus only your homephone can be used to buld te connection (and the call is payed by the office...;-)

For the normal replication (when you have replication enabled for the database), you would only need to place your file in the workmap folder on the desktop to get things started.
BTW if you start with enabling your database for replication, I would personally create a "master"-copy to have a non-replicated original. I found that replication is adding fields to the tables and resets an autonumber index to randomly generate (+ and -) numbers. My application wasn't handling that in a correct way the first time...

Hope this helps a bit.

Nic;o)
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6452104
'Tis a bit of a bugger that one.

Having autonumbers when you have mroe han one copy of a database is a BIG problem.

Can't think of a solution off the top of my head.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6453789
Hi James,

You don't need autonumbers as you can create them yourself by using DMAX(<keyfield>) + 1 when inserting a new entry....

Nic;o)
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6454969
Nah, not true. This will still cause problems.

Take this for example....

Replicate application, with max in myID of 12345.

Local version Max+1 = 12346. Record 12346 written.

Remote version Max+1 = 12346. Record 12346 written.

Attempt to merge two versions.....

Record 12346 does not contain the same data, therefore needs to be updated so that both versions have the same data, but which one keeps the 12346 number?

Thought of a way of doing it though, it will mean having to force referential integrity on every join with a cascade update. Still VERY messy, but could work.
0
 
LVL 4

Expert Comment

by:archery
ID: 6455011
To James,

This won't help the original questioner at all, but just to clarify (otr correct) your comments :

you WON'T get ANY duplicated autonumbers generated when you are dealing with a REPLICATED database. As nico5038 stated earlier, the autonunmber field for every table is (automatically) changed to "random" when you initially create a "replica" (set) and there is an enormous algorithm used from then on when generating the (random) autonumber value from then on within each independent replica (the algorithm does use the "absolute" nanosecond & the actual Mac Address (which is guaranteed to be unique and will be for next 50 years) of the PC from where the record is being generated, plus other factors to generate the autonumber, which can be a positive or result.

I have a system used by over 250 users split into 8 different replicas over an entire state in Aust and I can assure you that there has NEVER been a duplicated autonumber generated yet.

What you do have to ensure is that you have don't have any code/processes etc based on whether an autonumber field is greater than (>) 0 (zero), as these will fail AFTER converting to a "replicated" system (been there, done that, so I have been burnt personally)
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6455073
There is still a finite number of long integers.
So you do still need to cater for the fact that you MAY have a duplicate. It only takes one duplicate to stuff the whole thing.
0
 
LVL 4

Expert Comment

by:archery
ID: 6455133
James, yes, you are absolutely correct. There are only 4,294,967,325 (negative limit to positive limit) long integers available, but you point me to a SINGLE table in any database that has the need for THAT many individual records and I'll point you to a system that needs to be really looked at.
0
 

Author Comment

by:elte
ID: 6458730
Hi to all,

I'm watching the discussion about autonumbering and I'm still waiting a solution of my problem.
About my db :
a. There is no autonumber in any field on my db.
b. Where necessary, I made a string unique field

The problem is that I need code to connect the the replica with the my office's db without any programm (PC-Anywhere).

If it is possible with one "click"..

Thank you

Elte
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6462908
I believe the only dialing that Access can do on it's own is for voice (I might be wrong).

But, to do the replication, you'll need a TCP/IC connection.  Depending on the machine at work (I know Win98/NT can), you can set up Dial-Up Networking Server.  Then set up a Dial-up Networking Connection on your laptop and dial the computer.

You should have access to all shared drives that way and can then start up the replication.

dill
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6462949
No, no, access can do dialing for data.

I will put code here over the weekend!

You just reminded me of some I have in an old application!
0
 
LVL 4

Expert Comment

by:dilligaffuq
ID: 6462950
Glad I could help  ;)
0
 

Author Comment

by:elte
ID: 6477577
Hi to all,

Hello James , I did put all my expectations on you and i hope you find that you have in an old application.

If anyone wants can send me email in :
           elte@otenet.gr

Thank you again
Elte
0
 
LVL 2

Expert Comment

by:JamesMayfield
ID: 6478016
Sorry. I've been very buys with work.
I'll try to do it tonight.
0
 

Author Comment

by:elte
ID: 6480909
Thank you very much James

Elte

0
 
LVL 2

Accepted Solution

by:
JamesMayfield earned 300 total points
ID: 6484476
OK, here's your phone code.

You need to reference mscomm32.ocx, comdlg32.ocx, dao350.dll, or their more recent equivalents.

Here's the code. You'll have to sift through it sorry, I don't have time to explain it fully right now.

Option Compare Database
Option Explicit
Dim Ret As Integer      ' Scratch integer.
Dim Temp As String      ' Scratch string.
Dim hLogFile As Integer ' Handle of open log file.
Dim StartTime As Date   ' Stores starting time for port timer

Private Sub Form_Load()
    Dim CommPort As String, Handshaking As String, Settings As String
    Dim Echo As String
    On Error Resume Next
    txtTerm.SelLength = Len(txtTerm)
    txtTerm.SelText = ""
    txtTerm.ForeColor = vbBlue
       
   
    If CommPort <> "" Then MSComm1.CommPort = 2
   
End Sub


Private Sub Form_Unload(Cancel As Integer)
    Dim counter As Long

    If MSComm1.PortOpen Then
       counter = Timer + 10
       Do While MSComm1.OutBufferCount
          Ret = DoEvents()
          If Timer > counter Then
             Select Case MsgBox("Data cannot be sent", 34)
                Case 3
                   Cancel = True
                   Exit Sub
                Case 4
                   counter = Timer + 10
                Case 5
                   Exit Do
             End Select
          End If
       Loop

       MSComm1.PortOpen = 0
    End If

    If hLogFile Then mnuCloseLog_Click
    End
End Sub

Private Sub mnuCloseLog_Click()
    Close hLogFile
    hLogFile = 0
End Sub

Private Sub mnuDial_Click()
    On Local Error Resume Next
   
    If Not MSComm1.PortOpen Then
        OpenLogFile
        OpenComm
       If Err Then Exit Sub
    End If
     
   
             
    'MSComm1.Output = "ATz" '& vbCrLf
   
    MSComm1.Output = "ATDT" & Num & vbCrLf 'Num here is the phone number to dial.
   
End Sub

Sub OpenComm()
    On Error Resume Next
    Dim OpenFlag

    MSComm1.PortOpen = Not MSComm1.PortOpen
    If Err Then MsgBox Error$, 48
   
    MSComm1.InputLen = 0
   
End Sub

Private Sub mnuHangup_Click()
    On Error Resume Next
   
    MSComm1.Output = "ATH"      ' Send hangup string
    Ret = MSComm1.DTREnable     ' Save the current setting.
    MSComm1.DTREnable = True    ' Turn DTR on.
    MSComm1.DTREnable = False   ' Turn DTR off.
    MSComm1.DTREnable = Ret     ' Restore the old setting.
    If MSComm1.PortOpen Then MSComm1.PortOpen = False
    If hLogFile Then mnuCloseLog_Click
   
    If Err Then MsgBox Error$, 48
   
    On Error GoTo 0
End Sub




Private Sub OpenLogFile()
   Dim replace, P As String
   On Error Resume Next
   OpenLog.Flags = cdlOFNHideReadOnly Or cdlOFNExplorer
   OpenLog.CancelError = True
     
   OpenLog.DialogTitle = "Open Communications Log File"
   OpenLog.Filter = "Log Files (*.LOG)|*.log|All Files (*.*)|*.*"
   P = WorkingFolder
   Do
      OpenLog.Filename = P & Format(Now, "dd-mm-yy") & ".log"
      OpenLog.ShowOpen
      If Err = cdlCancel Then Exit Sub
      Temp = OpenLog.Filename

      Ret = Len(Dir$(Temp))
      If Err Then
         MsgBox Error$, 48
         Exit Sub
      End If
      If Ret Then
         replace = MsgBox("Replace existing file - " + Temp + "?", 35)
      Else
         replace = 0
      End If
   Loop While replace = 2

   If replace = 6 Then
      Kill Temp
      If Err Then
         MsgBox Error$, 48
         Exit Sub
      End If
   End If

   hLogFile = FreeFile
   Open Temp For Binary Access Write As hLogFile
   If Err Then
      MsgBox Error$, 48
      Close hLogFile
      hLogFile = 0
      Exit Sub
   Else
      Seek hLogFile, LOF(hLogFile) + 1
   End If

   Me.Caption = "Fibre Direct Download Terminal - " + OpenLog.FileTitle
End Sub

Private Static Sub MSComm1_OnComm()
    Dim EVMsg$
    Dim ERMsg$
   
    Select Case MSComm1.CommEvent
        Case comEvReceive
            Dim Buffer As Variant
            Buffer = MSComm1.Input
            Debug.Print "Receive - " & StrConv(Buffer, vbUnicode)
            ShowData txtTerm, (StrConv(Buffer, vbUnicode))
        Case comEvSend
        Case comEvCTS
            EVMsg$ = "Change in CTS Detected"
        Case comEvDSR
            EVMsg$ = "Change in DSR Detected"
        Case comEvCD
            EVMsg$ = "Change in CD Detected"
        Case comEvRing
            EVMsg$ = "The Phone is Ringing"
        Case comEvEOF
            EVMsg$ = "End of File Detected"

        Case comBreak
            ERMsg$ = "Break Received"
        Case comCDTO
            ERMsg$ = "Carrier Detect Timeout"
        Case comCTSTO
            ERMsg$ = "CTS Timeout"
        Case comDCB
            ERMsg$ = "Error retrieving DCB"
        Case comDSRTO
            ERMsg$ = "DSR Timeout"
        Case comFrame
            ERMsg$ = "Framing Error"
        Case comOverrun
            ERMsg$ = "Overrun Error"
        Case comRxOver
            ERMsg$ = "Receive Buffer Overflow"
        Case comRxParity
            ERMsg$ = "Parity Error"
        Case comTxFull
            ERMsg$ = "Transmit Buffer Full"
        Case Else
            ERMsg$ = "Unknown error or event"
    End Select
   
    If Len(EVMsg$) Then
    ElseIf Len(ERMsg$) Then
ERMsg$
       
        Beep
        Ret = MsgBox(ERMsg$, 1, "Click Cancel to quit, OK to ignore.")
       
        If Ret = 2 Then
            MSComm1.PortOpen = False    ' Close the port and quit.
        End If
       
    End If
End Sub

Private Sub mnuSendText_Click()
Dim S As String, S2 As String, S3 As String

MSComm1.Output = S & Chr$(13) & Chr$(10)
MSComm1.Output = S2 & Chr$(13) & Chr$(10)
MSComm1.Output = S3 & Chr$(13) & Chr$(10)

End Sub



Private Static Sub ShowData(Term As Control, Data As String)
    On Error GoTo Handler
    Const MAXTERMSIZE = 2048
    Dim TermSize As Long, i
    txtTerm.SetFocus
    ' Make sure the existing text doesn't get too large.
    TermSize = Len(Term.Text)
    If TermSize > MAXTERMSIZE Then
       Term.Text = Mid$(Term.Text, 2048)
       TermSize = Len(Term.Text)
    End If

    ' Point to the end of Term's data.
    Term.SelStart = TermSize

    ' Filter/handle BACKSPACE characters.
    Do
       i = InStr(Data, Chr$(8))
       If i Then
          If i = 1 Then
             Term.SelStart = TermSize - 1
             Term.SelLength = 1
             Data = Mid$(Data, i + 1)
          Else
             Data = Left$(Data, i - 2) & Mid$(Data, i + 1)
          End If
       End If
    Loop While i

    ' Eliminate line feeds.
    Do
       i = InStr(Data, Chr$(10))
       If i Then
          Data = Left$(Data, i - 1) & Mid$(Data, i + 1)
       End If
    Loop While i

    ' Make sure all carriage returns have a line feed.
    i = 1
    Do
       i = InStr(i, Data, Chr$(13))
       If i Then
          Data = Left$(Data, i) & Chr$(10) & Mid$(Data, i + 1)
          i = i + 1
       End If
    Loop While i

    ' Add the filtered data to the SelText property.
    Term.SelText = Data
 
    ' Log data to file if requested.
    If hLogFile Then
       i = 2
       Do
          Err = 0
          Put hLogFile, , Data
          If Err Then
             i = MsgBox(Error$, 21)
             If i = 2 Then
                mnuCloseLog_Click
             End If
          End If
       Loop While i <> 2
    End If
    Term.SelStart = Len(Term.Text)
Exit Sub

Handler:
    MsgBox Error$
    Resume Next
End Sub


Private Sub txtTerm_KeyPress(KeyAscii As Integer)
    ' If the port is opened...
    If MSComm1.PortOpen Then
        ' Send the keystroke to the port.
        MSComm1.Output = Chr$(KeyAscii)
       
       ' If Not Echo Then
            ' Place position at end of terminal
            txtTerm.SelStart = Len(txtTerm)
            KeyAscii = 0
       ' End If
    End If
     
End Sub


Private Sub close_Click()
On Error GoTo Err_close_Click


    DoCmd.close

Exit_close_Click:
    Exit Sub

Err_close_Click:
    MsgBox Err.Description
    Resume Exit_close_Click
   
End Sub



Hope this is of some use. This code is currently used in one of the systems I administer (although I didn't write this code.) It dials out to a remote computer and sends and receives some information.

-James
0
 

Author Comment

by:elte
ID: 6486202
Thank you very very much James. I'll try to put it in my db.
0
 

Expert Comment

by:philmellor
ID: 7129752
I had a problem with auto numbering many moons ago. Wasn't using replica datbases though!

I got around it by creating a custom "autonumber" function in VB. It took the time and date (right down to milliseconds) and created a serial number, then for good measure I found in function on MSDN that would get a serial number of a hard disk on which the application was located.

Add the strings together to get a long, but very unique ID number like:
0407200213350101-HVD02-1234

Hope this help someone coz it took me days to figure it out!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now