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

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

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

schreef in bericht =
news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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"
>=20
> 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?
>=20
> Thanks
>=20
>=20
> 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

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

wrote in message
news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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.

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

"Stuart McCall" wrote in message
news:fis46u$6hm$1$8302bc10@news.demon.co.uk...
> wrote in message
> news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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.

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

On Dec 1, 11:33 am, Clif...@gmail.com 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.

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

On Dec 1, 8:50 am, "Arno R" wrote:
> schreef in berichtnews:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf .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

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

On Dec 1, 9:04 am, "Stuart McCall" wrote:
> wrote in message
>
> news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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

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

On Dec 1, 9:45 am, lyle wrote:
> On Dec 1, 11:33 am, Clif...@gmail.com 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

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

On Dec 1, 9:04 am, "Stuart McCall" wrote:
> wrote in message
>
> news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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

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

"CliffKing" wrote in message
news:66e2967d-9ead-4fa2-82b3-34a187510029@d61g2000hsa.google groups.com...
> On Dec 1, 9:04 am, "Stuart McCall" wrote:
>> wrote in message
>>
>> news:e6426e0e-ac44-4c59-ac3d-debfacfd2fbe@d21g2000prf.google groups.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.

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

> 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