Check if a linked table in the database has been updated/changed

[Cliff72]

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks
Cliff

[Arno R]

<Cl*****@gmail.comschreef in bericht news:e6**********************************@d21g2000 prf.googlegroups.com...

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks
Cliff

If 'changed' means 'new records added' ... then you could try something like

VBA:
If DCount("*","LedgerTemp") DCount("*","Ledger") then RunYourMacro

Macro
Or call your macro at startup and put this in the condition of your macro
DCount("*";"LedgerTemp") DCount("*";"Ledger")

Arno R

[Stuart McCall]

<Cl*****@gmail.comwrote in message
news:e6**********************************@d21g2000 prf.googlegroups.com...

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks
Cliff

You can use the FileDateTime function to check when the file was last
updated. Set up a form, opened hidden if need be, and use its timer to poll
the date/time at an interval of your choice (say 300,000 - which is 5
minutes in milliseconds), and trigger your macro in the Form_Timer event.

[Stuart McCall]

"Stuart McCall" <sm*****@myunrealbox.comwrote in message
news:fi*******************@news.demon.co.uk...

<Cl*****@gmail.comwrote in message
news:e6**********************************@d21g2000 prf.googlegroups.com...

>Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks
Cliff

You can use the FileDateTime function to check when the file was last
updated. Set up a form, opened hidden if need be, and use its timer to
poll the date/time at an interval of your choice (say 300,000 - which is 5
minutes in milliseconds), and trigger your macro in the Form_Timer event.

Addendum: I should have finished that with:

and trigger your macro in the Form_Timer event if the date/time has changed.

[lyle]

On Dec 1, 11:33 am, [e-mail] wrote:

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks

Cliff

If you just want to check to se if the text file has changed you
checkand set the archive attribute of the file:

Public Sub RemoveArchiveAttribute(ByVal FullPath$)
SetAttr FullPath, Not vbArchive And GetAttr(FullPath)
End Sub

Public Function HasChanged(ByVal FullPath$) As Boolean
HasChanged = GetAttr(FullPath) And vbArchive
End Function

You could remove the attribute when you import.
It will be set (back on) when/if the file is changed.
You could check to see if it is set.
If so then import and remove again.

Of course, the Windows NTBackup (and other) utility removes the
attribute, but unless you are backing up frequently this is unlikely
to be a big problem.

[CliffKing]

On Dec 1, 8:50 am, "Arno R" <[e-mail]:

<[e-mail] in berichtnews:e6**********************************@d 21g2000prf.googlegroups.com...

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks

Cliff

If 'changed' means 'new records added' ... then you could try something like

VBA:
If DCount("*","LedgerTemp") DCount("*","Ledger") then RunYourMacro

Macro
Or call your macro at startup and put this in the condition of your macro
DCount("*";"LedgerTemp") DCount("*";"Ledger")

Arno R

Thanks for your help

[CliffKing]

On Dec 1, 9:04 am, "Stuart McCall" <[e-mail]:

<[e-mail] in message

news:e6**********************************@d21g2000 prf.googlegroups.com...

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks

Cliff

You can use the FileDateTime function to check when the file was last
updated. Set up a form, opened hidden if need be, and use its timer to poll
the date/time at an interval of your choice (say 300,000 - which is 5
minutes in milliseconds), and trigger your macro in the Form_Timer event.

Thanks for your help

[CliffKing]

On Dec 1, 9:45 am, lyle <[e-mail]:

On Dec 1, 11:33 am, [e-mail] wrote:

Is there a way in VBA to check if a linked table in the database has
been updated?
Example:
I have a table "LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from the linked "LedgerTemp" table to a local table "Ledger"

Is there a way I can check to see if my linked table has changed and
if so trigger my macro that appends the new data?

Thanks

Cliff

If you just want to check to se if the text file has changed you
checkand set the archive attribute of the file:

Public Sub RemoveArchiveAttribute(ByVal FullPath$)
SetAttr FullPath, Not vbArchive And GetAttr(FullPath)
End Sub

Public Function HasChanged(ByVal FullPath$) As Boolean
HasChanged = GetAttr(FullPath) And vbArchive
End Function

You could remove the attribute when you import.
It will be set (back on) when/if the file is changed.
You could check to see if it is set.
If so then import and remove again.

Of course, the Windows NTBackup (and other) utility removes the
attribute, but unless you are backing up frequently this is unlikely
to be a big problem.

Thanks for your help

[CliffKing]

On Dec 1, 9:04 am, "Stuart McCall" <[e-mail]:

<[e-mail] in message

news:e6**********************************@d21g2000 prf.googlegroups.com...

Is there a way in VBA tocheckifalinkedtablein thedatabasehas
beenupdated?
Example:
I have atable"LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from thelinked"LedgerTemp"tableto a localtable"Ledger"

Is there a way I cancheckto seeifmylinkedtablehaschanged and
ifso trigger my macro that appends the new data?

Thanks

Cliff

You can use the FileDateTime function tocheckwhen the file was last
updated. Set up a form, opened hiddenifneed be, and use its timer to poll
the date/time at an interval of your choice (say 300,000 - which is 5
minutes in milliseconds), and trigger your macro in the Form_Timer event.- Hide quoted text -

- Show quoted text -

Thanks all for your help. I made some progress.

I store the result of FileDateTime("LedgerTemp ") into a
local table then I have a form with an "On Load" event, which sets
this value into a field on this form.
The forms timer event is set to 5 minutes (in milliseconds) and in the
"On Timer" event I have this code:

Private Sub Form_Timer()
If FileDateTime("C:\Ledger_dl\Ledger.txt") <Me.LedgerStamp Then
DoCmd.RunMacro ("ImportFile")
End If
End Sub

All the macro does is just basically appends data from the linked
"LedgerTemp" table to a local table "Ledger" then run a couple reports
and also I have it close and open the form so that the new value from
FileDateTime("LedgerTemp ") can be set. The code of the macro is
below....

I have tested this and it seems to be working fine. If I update/change
the file on the LAN the macro runs the and prints the reports but its
kind of freezing it doesn't close and reopen the form so it can set
the new value! The database just freezes with the hourglass on like
something is running in the background??? I have failed to figure out
what going on here and also if the forms timer occurs about the same
time that the file on the LAN updates/changes the macro fails I get an
error message that the query cant run because the file is in use.

Is there a way I can overcome these issues?

Thanks

Cliff

Function ImportFile()
On Error GoTo ImportFile_Err

DoCmd.Echo False, "Importing latest Ledger Data........."
DoCmd.SetWarnings False
' LastLedgerStampQuery1
DoCmd.OpenQuery "LastLedgerStampQuery1", acNormal, acEdit
' LedgerQuery1
DoCmd.OpenQuery "LedgerQuery1", acNormal, acEdit
' LedgerQuery2
DoCmd.OpenQuery "LedgerQuery2", acNormal, acEdit
' C:\Archive\LedgerDB.mdb LedgerDateBatch
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Archive
\LedgerDB.mdb", acTable, "LedgerDateBatch", "LedgerDateBatch", False
' LedgerReport
DoCmd.OpenReport "LedgerReport", acNormal, "", ""
' RejectsReport
DoCmd.OpenReport "RejectsReport", acNormal, "", ""
' MAIN MENU
DoCmd.Close acForm, "MAIN MENU"
' MAIN MENU
DoCmd.OpenForm "MAIN MENU", acNormal, "", "", , acNormal
Exit Function

ImportFile_Exit:
Exit Function
ImportFile_Err:
MsgBox Error$
Resume ImportFile_Exit
End Function

[Stuart McCall]

"CliffKing" <Cl*****@gmail.comwrote in message
news:66**********************************@d61g2000 hsa.googlegroups.com...

On Dec 1, 9:04 am, "Stuart McCall" <[e-mail]:

><[e-mail] in message

news:e6**********************************@d21g200 0prf.googlegroups.com...

Is there a way in VBA tocheckifalinkedtablein thedatabasehas
beenupdated?
Example:
I have atable"LedgerTemp" which is a direct link to a text file on
the LAN "Ledger.txt"
This text file is periodically updated (overwritten) through out the
day and night by some mainframe jobs.
Right now I just manually run a macro that just basically appends data
from thelinked"LedgerTemp"tableto a localtable"Ledger"

Is there a way I cancheckto seeifmylinkedtablehaschanged and
ifso trigger my macro that appends the new data?

Thanks

Cliff

You can use the FileDateTime function tocheckwhen the file was last
updated. Set up a form, opened hiddenifneed be, and use its timer to poll
the date/time at an interval of your choice (say 300,000 - which is 5
minutes in milliseconds), and trigger your macro in the Form_Timer
event.- Hide quoted text -

- Show quoted text -

Thanks all for your help. I made some progress.

I store the result of FileDateTime("LedgerTemp ") into a
local table then I have a form with an "On Load" event, which sets
this value into a field on this form.
The forms timer event is set to 5 minutes (in milliseconds) and in the
"On Timer" event I have this code:

Private Sub Form_Timer()
If FileDateTime("C:\Ledger_dl\Ledger.txt") <Me.LedgerStamp Then
DoCmd.RunMacro ("ImportFile")
End If
End Sub

All the macro does is just basically appends data from the linked
"LedgerTemp" table to a local table "Ledger" then run a couple reports
and also I have it close and open the form so that the new value from
FileDateTime("LedgerTemp ") can be set. The code of the macro is
below....

I have tested this and it seems to be working fine. If I update/change
the file on the LAN the macro runs the and prints the reports but its
kind of freezing it doesn't close and reopen the form so it can set
the new value! The database just freezes with the hourglass on like
something is running in the background??? I have failed to figure out
what going on here and also if the forms timer occurs about the same
time that the file on the LAN updates/changes the macro fails I get an
error message that the query cant run because the file is in use.

Is there a way I can overcome these issues?

Thanks

Cliff

Function ImportFile()
On Error GoTo ImportFile_Err

DoCmd.Echo False, "Importing latest Ledger Data........."
DoCmd.SetWarnings False
' LastLedgerStampQuery1
DoCmd.OpenQuery "LastLedgerStampQuery1", acNormal, acEdit
' LedgerQuery1
DoCmd.OpenQuery "LedgerQuery1", acNormal, acEdit
' LedgerQuery2
DoCmd.OpenQuery "LedgerQuery2", acNormal, acEdit
' C:\Archive\LedgerDB.mdb LedgerDateBatch
DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\Archive
\LedgerDB.mdb", acTable, "LedgerDateBatch", "LedgerDateBatch", False
' LedgerReport
DoCmd.OpenReport "LedgerReport", acNormal, "", ""
' RejectsReport
DoCmd.OpenReport "RejectsReport", acNormal, "", ""
' MAIN MENU
DoCmd.Close acForm, "MAIN MENU"
' MAIN MENU
DoCmd.OpenForm "MAIN MENU", acNormal, "", "", , acNormal
Exit Function

ImportFile_Exit:
Exit Function
ImportFile_Err:
MsgBox Error$
Resume ImportFile_Exit
End Function

That's quite a batch of work going on there. I think what you need to do to
solve the 'freezing' problem is to switch off the timer while the ImportFile
procedure runs:

Function ImportFile()
On Error GoTo ImportFile_Err
Me.TimerInterval = 0

....

Me.TimerInterval = 300000
DoCmd.OpenForm "MAIN MENU", acNormal, "", "", , acNormal
Exit Function
(that's assuming the code exists in your form's module. If it doesn't,
you'll need to replace Me with Forms!MyFormName)

Let's see if that cracks your 1st problem, then move on to the file locking
problem.

[Stuart McCall]

DoCmd.Echo False, "Importing latest Ledger Data........."

DoCmd.SetWarnings False

I just spotted these lines in your function. The first line is probably what
is making your app 'freeze'. You need to switch Echo and SetWarnings on
again before your function exits:

ImportFile_Exit:
DoCmd.Echo True
DoCmd.SetWarnings True
Exit Function