Remotely start import on text file update.

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • aas4mis
    New Member
    • Jan 2008
    • 97

    #1

    Remotely start import on text file update.

    Is it possible to remotely start an import on machine "A" when a text file on machine "B" is modified? I can setup linked tables and run the following,...

    qry_ManifestUpd ateAndInsert:
    [CODE=sql]UPDATE tbl_tempManifes t AS tm LEFT JOIN tbl_Manifest AS m ON tm.tracking=m.t racking SET m.name = tm.name, m.store = tm.store, m.orderNum = tm.orderNum, m.tracking = tm.tracking;[/CODE]

    but not sure how to setup a "trigger" if you will to let machine "A" know the text file has been updated. Open for suggestions, even more open for suggestions that would not require a linked table. I would like machine "B" to have control, machine "B" can be windows XP or linux.

    Thanks!
  • ADezii
    Recognized Expert Expert
    • Apr 2006
    • 8834

    #2
    Assuming Machine B can 'see' the Text File on Machine A:
    1. Create a Form in a Database on Machine B.
    2. Create the following 2 Form Level Variables:
      Code:
      Dim strFileToCheck As String
      Dim varFileDateTime As Variant
    3. Open the newly created Form either Hidden or Minimized, and initialize the 2 previously Declared Variables in the Open() Event:
      Code:
      Private Sub Form_Open(Cancel As Integer)
        strFileToCheck = "T:\Stuff\Test.txt"       'a Shared Drive on Machine 'A'
        varFileDateTime = FileDateTime(strFileToCheck)
      End Sub
    4. Set the Form's Timer Interval to 30000 (30 seconds), and in the Timer() Event place the following code:
      Code:
      Private Sub Form_Timer()
        If varFileDateTime <> FileDateTime(strFileToCheck) Then
          'initiate UPDATE Operation here
          varFileDateTime = FileDateTime(strFileToCheck)    'reset
        End If
      End Sub
    5. When the Form Opens, the Variables will be initialized, the File's (Test.txt) Date/Time Stamp will be stored in the Variable. Every 30 seconds, the File's Date/Time Stamp will be checked against the previously stored Value. If they differ, the File was Modified, perform the Update Operation, Reset the Variable (varFileDateTim e), and then start all over with the 30 second cycle.
    6. Make the necessary adjustments to suit your specific needs.
    7. The following was only Theory, will it work? I'll leave that up to you.

    Comment

    • aas4mis
      New Member
      • Jan 2008
      • 97

      #3
      Sweet! Thanks for the help ADezii. I'll give this a shot next chance I get.

      Comment

      • aas4mis
        New Member
        • Jan 2008
        • 97

        #4
        Another job well done. Client has a front end, Server (code below) monitors and updates on filechange. Here's the code...
        [CODE=vba]
        Option Compare Database
        Dim strFileToCheck As String
        Dim varFileDateTime As Variant


        Private Sub Form_Open(Cance l As Integer)

        strFileToCheck = "c:\inetpub\ftp root\inhouseman .txt"
        varFileDateTime = FileDateTime(st rFileToCheck)

        End Sub

        Private Sub Form_Timer()
        Dim strSQL As String

        If varFileDateTime <> FileDateTime(st rFileToCheck) Then
        DoCmd.SetWarnin gs False
        DoCmd.TransferT ext acImportDelim, "InHouseManImpo rtSpecs", "tbl_tempManife st", "c:\inetpub\ftp root\inhouseman .txt", False
        DoCmd.OpenQuery "qry_ManifestUp dateAndInsert"

        strSQL = "DELETE * FROM tbl_TempManifes t"
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnin gs True

        Me.txtUpdated.V alue = Now()
        'MsgBox "The file has been modified!"
        varFileDateTime = FileDateTime(st rFileToCheck)
        Else
        Me.txtActivity. Value = Now()
        'MsgBox "The file is the same!"
        End If


        End Sub
        [/CODE]

        Thanks for pointing me in the right direction ADezii!

        Comment

        Working...