By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Remotely start import on text file update.

P: 97
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,...

Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl_tempManifest AS tm LEFT JOIN tbl_Manifest AS m ON tm.tracking=m.tracking SET =, =, m.orderNum = tm.orderNum, m.tracking = tm.tracking;
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.

Feb 26 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 5K+
P: 8,638
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim strFileToCheck As String
    2. 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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2.   strFileToCheck = "T:\Stuff\Test.txt"       'a Shared Drive on Machine 'A'
    3.   varFileDateTime = FileDateTime(strFileToCheck)
    4. End Sub
  4. Set the Form's Timer Interval to 30000 (30 seconds), and in the Timer() Event place the following code:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Timer()
    2.   If varFileDateTime <> FileDateTime(strFileToCheck) Then
    3.     'initiate UPDATE Operation here
    4.     varFileDateTime = FileDateTime(strFileToCheck)    'reset
    5.   End If
    6. 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 (varFileDateTime), 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.
Feb 27 '10 #2

P: 97
Sweet! Thanks for the help ADezii. I'll give this a shot next chance I get.
Mar 2 '10 #3

P: 97
Another job well done. Client has a front end, Server (code below) monitors and updates on filechange. Here's the code...
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Dim strFileToCheck As String
  3. Dim varFileDateTime As Variant
  6. Private Sub Form_Open(Cancel As Integer)
  8.     strFileToCheck = "c:\inetpub\ftproot\inhouseman.txt"
  9.     varFileDateTime = FileDateTime(strFileToCheck)
  11. End Sub
  13. Private Sub Form_Timer()
  14. Dim strSQL As String
  16.     If varFileDateTime <> FileDateTime(strFileToCheck) Then
  17.         DoCmd.SetWarnings False
  18.         DoCmd.TransferText acImportDelim, "InHouseManImportSpecs", "tbl_tempManifest", "c:\inetpub\ftproot\inhouseman.txt", False
  19.         DoCmd.OpenQuery "qry_ManifestUpdateAndInsert"
  21.         strSQL = "DELETE * FROM tbl_TempManifest"
  22.         DoCmd.RunSQL strSQL
  23.         DoCmd.SetWarnings True
  25.         Me.txtUpdated.Value = Now()
  26.         'MsgBox "The file has been modified!"
  27.         varFileDateTime = FileDateTime(strFileToCheck)
  28.     Else
  29.         Me.txtActivity.Value = Now()
  30.         'MsgBox "The file is the same!"
  31.     End If
  34. End Sub
Thanks for pointing me in the right direction ADezii!
Mar 5 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.