Database only works for a week

[Ian]

I have an Access 2000 database that checks once every 4 seconds to see
if a text file exists, if it does then it opens the text file, reads an
ID, opens a report based on that ID, then deletes the text file and
continues to wait for another text file to appear. This text file
appears about 20 times a day on average, the database is open 24-7 and
never re-booted just waiting in the background for the next text file to
appear.

My problem is that it all works perfectly for about 6-7 days then I get
an Access error message “Access has encountered an error and needs to
close”. Possible it is gradually using the PC’s resources such as
virtual memory but I don’t see why, has anyone got any suggestions please?

Below is the code from the forms On Timer event with the Timer Interval
set to 4000
‘================================================= ==
'Find the text file to open
Dim FindConwyTxtLocation As String
FindConwyTxtLocation = Nz(DLookup("[TextFileImpLocation]",
"[DeafaultsTbl]", "[TextFileImpLocation]"))

'1st check Text file exists, if not exit sub now
If CheckForFile(FindConwyTxtLocation) = False Then
Exit Sub 'Exit if no text file
End If
'Make Conwy table from TXT File.
DoCmd.TransferText acImportDelim, , "ConwyTxtTbl", FindConwyTxtLocation,
False, ""

'===========

'Count number of recs in Temp Table
Dim CountTempTableRecs As Long
CountTempTableRecs = Nz(DCount("[F1]", "[ConwyTxtTbl]", "[F1]"))
'MsgBox "Number of records in Temp Table: " & CountTempTableRecs
'See if any matches in the Sites table using Conwy Query
Dim CountMatchingSites As Long
CountMatchingSites = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSitesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Sites Table: " & CountMatchingSites
'See if any matches in the Samples table using Conwy Query
Dim CountMatchingSamples As Long
CountMatchingSamples = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSamplesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Samples Table: " &
CountMatchingSamples
'See if any matches in the Notes table using 2x Conwy Queries
Dim FindMatchingNotes As Long
FindMatchingNotes = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingNotesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Notes Table: " & FindMatchingNotes

'============

'Print the correct report

'Print the NO RECS report if No matching Sites
'If CountMatchingSites < 1 Then 'No matching Sites
'DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
'Exit Sub 'Exit now or rep prints twice
'End If
'Print the NO RECS report if No matching Samples or Notes
'Now also prints if No Matching Site
If (CountMatchingSamples < 1 And FindMatchingNotes < 1) Or
CountMatchingSites < 1 Then 'No matching Sample - Notes
DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
End If
'Has matching Site and Samples, print standard report.
If CountMatchingSites 0 And CountMatchingSamples 0 Then 'Has Site
and Samples
DoCmd.OpenReport "ConwyAutoPrintRep", acNormal, "", "" 'Print standard
report
End If

'Added 06/10/2006 to print Notes Only report if has site with match
Notes but no Samples
'If Site matches, Match in Temp Tbl, Matching Notes
If CountMatchingSites 0 And FindMatchingNotes 0 And
CountMatchingSamples < 1 Then
DoCmd.OpenReport "ConwyAutoPrintNotesNoSamplesRep", acNormal, "", ""
'Notes no samples
End If
'Delete the records in temp table
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete records
DoCmd.SetWarnings True

'Delete the Text file that we imported from
Kill FindConwyTxtLocation
End Sub

[lyle fairfield]

Ian <ia********@ntlworld.comwrote in
news:Sr****************@newsfe4-win.ntli.net:

I have an Access 2000 database that checks once every 4 seconds to see
if a text file exists, if it does then it opens the text file, reads
an ID, opens a report based on that ID, then deletes the text file and
continues to wait for another text file to appear. This text file
appears about 20 times a day on average, the database is open 24-7 and
never re-booted just waiting in the background for the next text file
to appear.

I had a friend who bought a new Honda Accord with a manual transmission. It
was red-lined about 6800. When accelerating he drove in low until the tach
reached 6800. Then he shifted to second and drove in second to 6800; ditto
for third. Fourth and fifth were almost unnecessary he told me; being a
passenger at the time, I agreed with him immediately.

After a year of this abuse, his engine (yes a HONDA ENGINE) failed.

This and your post go to show that Honda engines under severe abuse may
last for fifty-two times longer than Microsoft Access/JET engines under
severe abuse.
--
lyle fairfield
-
Ceterum censeo Redmond esse delendam.
-

[Ian]

Thanks Lyle, I take it that you consider this to be an abuse of the jet
engine, I am not sure that I understand why?

Given that 99.9% of the time it just checks to see if the text file
exists, finds that it does not so exits the sub, how does this count as
revving up to 6800 rpm? The CPU usage never goes very high.

Are you saying that a database should not be left open for longer than a
specific period of time?

Are you saying that the timer event is too fast making the code run
continuously cause over revving?

Do you have any suggestions how to resolve the problem and prevent the
over revving of the jet engine or should I just buy a Toyota?

Regards

Ian wrote:

I have an Access 2000 database that checks once every 4 seconds to see
if a text file exists, if it does then it opens the text file, reads an
ID, opens a report based on that ID, then deletes the text file and
continues to wait for another text file to appear. This text file
appears about 20 times a day on average, the database is open 24-7 and
never re-booted just waiting in the background for the next text file to
appear.

My problem is that it all works perfectly for about 6-7 days then I get
an Access error message “Access has encountered an error and needs to
close”. Possible it is gradually using the PC’s resources such as
virtual memory but I don’t see why, has anyone got any suggestions please?

Below is the code from the forms On Timer event with the Timer Interval
set to 4000
‘================================================= ==
'Find the text file to open
Dim FindConwyTxtLocation As String
FindConwyTxtLocation = Nz(DLookup("[TextFileImpLocation]",
"[DeafaultsTbl]", "[TextFileImpLocation]"))

'1st check Text file exists, if not exit sub now
If CheckForFile(FindConwyTxtLocation) = False Then
Exit Sub 'Exit if no text file
End If
'Make Conwy table from TXT File.
DoCmd.TransferText acImportDelim, , "ConwyTxtTbl", FindConwyTxtLocation,
False, ""

'===========

'Count number of recs in Temp Table
Dim CountTempTableRecs As Long
CountTempTableRecs = Nz(DCount("[F1]", "[ConwyTxtTbl]", "[F1]"))
'MsgBox "Number of records in Temp Table: " & CountTempTableRecs
'See if any matches in the Sites table using Conwy Query
Dim CountMatchingSites As Long
CountMatchingSites = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSitesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Sites Table: " & CountMatchingSites
'See if any matches in the Samples table using Conwy Query
Dim CountMatchingSamples As Long
CountMatchingSamples = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSamplesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Samples Table: " &
CountMatchingSamples
'See if any matches in the Notes table using 2x Conwy Queries
Dim FindMatchingNotes As Long
FindMatchingNotes = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingNotesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Notes Table: " & FindMatchingNotes

'============

'Print the correct report

'Print the NO RECS report if No matching Sites
'If CountMatchingSites < 1 Then 'No matching Sites
'DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
'Exit Sub 'Exit now or rep prints twice
'End If
'Print the NO RECS report if No matching Samples or Notes
'Now also prints if No Matching Site
If (CountMatchingSamples < 1 And FindMatchingNotes < 1) Or
CountMatchingSites < 1 Then 'No matching Sample - Notes
DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
End If
'Has matching Site and Samples, print standard report.
If CountMatchingSites 0 And CountMatchingSamples 0 Then 'Has Site
and Samples
DoCmd.OpenReport "ConwyAutoPrintRep", acNormal, "", "" 'Print standard
report
End If

'Added 06/10/2006 to print Notes Only report if has site with match
Notes but no Samples
'If Site matches, Match in Temp Tbl, Matching Notes
If CountMatchingSites 0 And FindMatchingNotes 0 And
CountMatchingSamples < 1 Then
DoCmd.OpenReport "ConwyAutoPrintNotesNoSamplesRep", acNormal, "", ""
'Notes no samples
End If
'Delete the records in temp table
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete records
DoCmd.SetWarnings True

'Delete the Text file that we imported from
Kill FindConwyTxtLocation
End Sub

[lyle fairfield]

Ian <ia********@ntlworld.comwrote in
news:t4*******************@newsfe5-win.ntli.net:

Thanks Lyle, I take it that you consider this to be an abuse of the
jet engine, I am not sure that I understand why?

Given that 99.9% of the time it just checks to see if the text file
exists, finds that it does not so exits the sub, how does this count
as revving up to 6800 rpm? The CPU usage never goes very high.

Are you saying that a database should not be left open for longer than
a specific period of time?

Are you saying that the timer event is too fast making the code run
continuously cause over revving?

Do you have any suggestions how to resolve the problem and prevent the
over revving of the jet engine or should I just buy a Toyota?

Regards

I don't have any strong suggestions; I have seldom used the form timer
event and never recently because it seems to fail to operate properly
when (other) code is running; will it cause a problem eventually if its
code takes more than four seconds to run? I don't know. If I "had = was
required" to do this I would at least consider these things:

1. kill the text file immediately after getting the data from it;
2. use an api sleep call (http://www.ffdba.com/downloads/Wait.htm)
instead of the form timer event to effect the continual loop; api sleep
calls seem to be much more benign than form timers;
3. if the timer must be used; turn it off at the beginning of the
procedure and back on at its end so to lessen the possibility of the form
trying to call the timer procedure while the reports are still being
printed (but maybe under the laws of VBA this can't happen though I doubt
that even if it's gospel);
4. if there is some time when the text file is unlikely to appear, say
the early morning hours, giving the whole thing a rest each day;
5. running the app from another app which would close it, compact it (or
its backend as appropriate) and then reopen it with the form in question
being the startup form every few hours; the other app being run regularly
with the windows scheduler.

No, I would not recommend a Toyota unless you feel have a deep need to be
violated.

Ian wrote:

>I have an Access 2000 database that checks once every 4 seconds to
see if a text file exists, if it does then it opens the text file,
reads an ID, opens a report based on that ID, then deletes the text
file and continues to wait for another text file to appear. This text
file appears about 20 times a day on average, the database is open
24-7 and never re-booted just waiting in the background for the next
text file to appear.

My problem is that it all works perfectly for about 6-7 days then I
get an Access error message “Access has encountered an error and
needs to close”. Possible it is gradually using the PC’s resources
such as virtual memory but I don’t see why, has anyone got any
suggestions please?

Below is the code from the forms On Timer event with the Timer
Interval set to 4000
‘================================================ ===
'Find the text file to open
Dim FindConwyTxtLocation As String
FindConwyTxtLocation = Nz(DLookup("[TextFileImpLocation]",
"[DeafaultsTbl]", "[TextFileImpLocation]"))

'1st check Text file exists, if not exit sub now
If CheckForFile(FindConwyTxtLocation) = False Then
Exit Sub 'Exit if no text file
End If
'Make Conwy table from TXT File.
DoCmd.TransferText acImportDelim, , "ConwyTxtTbl",
FindConwyTxtLocation, False, ""

'===========

'Count number of recs in Temp Table
Dim CountTempTableRecs As Long
CountTempTableRecs = Nz(DCount("[F1]", "[ConwyTxtTbl]", "[F1]"))
'MsgBox "Number of records in Temp Table: " & CountTempTableRecs
'See if any matches in the Sites table using Conwy Query
Dim CountMatchingSites As Long
CountMatchingSites = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSitesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Sites Table: " &
CountMatchingSites
'See if any matches in the Samples table using Conwy Query
Dim CountMatchingSamples As Long
CountMatchingSamples = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSamplesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Samples Table: " &
CountMatchingSamples
'See if any matches in the Notes table using 2x Conwy Queries
Dim FindMatchingNotes As Long
FindMatchingNotes = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingNotesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Notes Table: " &
FindMatchingNotes

'============

'Print the correct report

'Print the NO RECS report if No matching Sites
'If CountMatchingSites < 1 Then 'No matching Sites
'DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
'Exit Sub 'Exit now or rep prints twice
'End If
'Print the NO RECS report if No matching Samples or Notes
'Now also prints if No Matching Site
If (CountMatchingSamples < 1 And FindMatchingNotes < 1) Or
CountMatchingSites < 1 Then 'No matching Sample - Notes
DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
End If
'Has matching Site and Samples, print standard report.
If CountMatchingSites 0 And CountMatchingSamples 0 Then 'Has Site
and Samples
DoCmd.OpenReport "ConwyAutoPrintRep", acNormal, "", "" 'Print
standard report
End If

'Added 06/10/2006 to print Notes Only report if has site with match
Notes but no Samples
'If Site matches, Match in Temp Tbl, Matching Notes
If CountMatchingSites 0 And FindMatchingNotes 0 And
CountMatchingSamples < 1 Then
DoCmd.OpenReport "ConwyAutoPrintNotesNoSamplesRep", acNormal, "", ""
'Notes no samples
End If
'Delete the records in temp table
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete
records DoCmd.SetWarnings True

'Delete the Text file that we imported from
Kill FindConwyTxtLocation
End Sub

--
lyle fairfield

Ceterum censeo Redmond esse delendam.

[Ian]

lyle fairfield wrote:

Ian <ia********@ntlworld.comwrote in
news:t4*******************@newsfe5-win.ntli.net:

>Thanks Lyle, I take it that you consider this to be an abuse of the
jet engine, I am not sure that I understand why?

Given that 99.9% of the time it just checks to see if the text file
exists, finds that it does not so exits the sub, how does this count
as revving up to 6800 rpm? The CPU usage never goes very high.

Are you saying that a database should not be left open for longer than
a specific period of time?

Are you saying that the timer event is too fast making the code run
continuously cause over revving?

Do you have any suggestions how to resolve the problem and prevent the
over revving of the jet engine or should I just buy a Toyota?

Regards

I don't have any strong suggestions; I have seldom used the form timer
event and never recently because it seems to fail to operate properly
when (other) code is running; will it cause a problem eventually if its
code takes more than four seconds to run? I don't know. If I "had = was
required" to do this I would at least consider these things:

1. kill the text file immediately after getting the data from it;
2. use an api sleep call (http://www.ffdba.com/downloads/Wait.htm)
instead of the form timer event to effect the continual loop; api sleep
calls seem to be much more benign than form timers;
3. if the timer must be used; turn it off at the beginning of the
procedure and back on at its end so to lessen the possibility of the form
trying to call the timer procedure while the reports are still being
printed (but maybe under the laws of VBA this can't happen though I doubt
that even if it's gospel);
4. if there is some time when the text file is unlikely to appear, say
the early morning hours, giving the whole thing a rest each day;
5. running the app from another app which would close it, compact it (or
its backend as appropriate) and then reopen it with the form in question
being the startup form every few hours; the other app being run regularly
with the windows scheduler.

No, I would not recommend a Toyota unless you feel have a deep need to be
violated.

>Ian wrote:

>>I have an Access 2000 database that checks once every 4 seconds to
see if a text file exists, if it does then it opens the text file,
reads an ID, opens a report based on that ID, then deletes the text
file and continues to wait for another text file to appear. This text
file appears about 20 times a day on average, the database is open
24-7 and never re-booted just waiting in the background for the next
text file to appear.

My problem is that it all works perfectly for about 6-7 days then I
get an Access error message “Access has encountered an error and
needs to close”. Possible it is gradually using the PC’s resources
such as virtual memory but I don’t see why, has anyone got any
suggestions please?

Below is the code from the forms On Timer event with the Timer
Interval set to 4000
‘=============================================== ====
'Find the text file to open
Dim FindConwyTxtLocation As String
FindConwyTxtLocation = Nz(DLookup("[TextFileImpLocation]",
"[DeafaultsTbl]", "[TextFileImpLocation]"))

'1st check Text file exists, if not exit sub now
If CheckForFile(FindConwyTxtLocation) = False Then
Exit Sub 'Exit if no text file
End If
'Make Conwy table from TXT File.
DoCmd.TransferText acImportDelim, , "ConwyTxtTbl",
FindConwyTxtLocation, False, ""

'===========

'Count number of recs in Temp Table
Dim CountTempTableRecs As Long
CountTempTableRecs = Nz(DCount("[F1]", "[ConwyTxtTbl]", "[F1]"))
'MsgBox "Number of records in Temp Table: " & CountTempTableRecs
'See if any matches in the Sites table using Conwy Query
Dim CountMatchingSites As Long
CountMatchingSites = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSitesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Sites Table: " &
CountMatchingSites
'See if any matches in the Samples table using Conwy Query
Dim CountMatchingSamples As Long
CountMatchingSamples = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSamplesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Samples Table: " &
CountMatchingSamples
'See if any matches in the Notes table using 2x Conwy Queries
Dim FindMatchingNotes As Long
FindMatchingNotes = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingNotesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Notes Table: " &
FindMatchingNotes

'============

'Print the correct report

'Print the NO RECS report if No matching Sites
'If CountMatchingSites < 1 Then 'No matching Sites
'DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
'Exit Sub 'Exit now or rep prints twice
'End If
'Print the NO RECS report if No matching Samples or Notes
'Now also prints if No Matching Site
If (CountMatchingSamples < 1 And FindMatchingNotes < 1) Or
CountMatchingSites < 1 Then 'No matching Sample - Notes
DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
End If
'Has matching Site and Samples, print standard report.
If CountMatchingSites 0 And CountMatchingSamples 0 Then 'Has Site
and Samples
DoCmd.OpenReport "ConwyAutoPrintRep", acNormal, "", "" 'Print
standard report
End If

'Added 06/10/2006 to print Notes Only report if has site with match
Notes but no Samples
'If Site matches, Match in Temp Tbl, Matching Notes
If CountMatchingSites 0 And FindMatchingNotes 0 And
CountMatchingSamples < 1 Then
DoCmd.OpenReport "ConwyAutoPrintNotesNoSamplesRep", acNormal, "", ""
'Notes no samples
End If
'Delete the records in temp table
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete
records DoCmd.SetWarnings True

'Delete the Text file that we imported from
Kill FindConwyTxtLocation
End Sub

Thank You Lyle

[engles]

On Mar 21, 5:57 am, Ian <[e-mail]:

lyle fairfield wrote:

Ian <[e-mail] in
news:t4*******************@newsfe5-win.ntli.net:

Thanks Lyle, I take it that you consider this to be an abuse of the
jet engine, I am not sure that I understand why?

Given that 99.9% of the time it just checks to see if the text file
exists, finds that it does not so exits the sub, how does this count
as revving up to 6800 rpm? The CPU usage never goes very high.

Are you saying that a database should not be left open for longer than
a specific period of time?

Are you saying that the timer event is too fast making the code run
continuously cause over revving?

Do you have any suggestions how to resolve the problem and prevent the
over revving of the jet engine or should I just buy a Toyota?

Regards

I don't have any strong suggestions; I have seldom used the form timer
event and never recently because it seems to fail to operate properly
when (other) code is running; will it cause a problem eventually if its
code takes more than four seconds to run? I don't know. If I "had = was
required" to do this I would at least consider these things:

1. kill the text file immediately after getting the data from it;
2. use an api sleep call (http://www.ffdba.com/downloads/Wait.htm)
instead of the form timer event to effect the continual loop; api sleep
calls seem to be much more benign than form timers;
3. if the timer must be used; turn it off at the beginning of the
procedure and back on at its end so to lessen the possibility of the form
trying to call the timer procedure while the reports are still being
printed (but maybe under the laws of VBA this can't happen though I doubt
that even if it's gospel);
4. if there is some time when the text file is unlikely to appear, say
the early morning hours, giving the whole thing a rest each day;
5. running the app from another app which would close it, compact it (or
its backend as appropriate) and then reopen it with the form in question
being the startup form every few hours; the other app being run regularly
with the windows scheduler.

No, I would not recommend a Toyota unless you feel have a deep need to be
violated.

Ian wrote:
I have an Access 2000 database that checks once every 4 seconds to
see if a text file exists, if it does then it opens the text file,
reads an ID, opens a report based on that ID, then deletes the text
file and continues to wait for another text file to appear. This text
file appears about 20 times a day on average, the database is open
24-7 and never re-booted just waiting in the background for the next
text file to appear.

>My problem is that it all works perfectly for about 6-7 days then I
get an Access error message "Access has encountered an error and
needs to close". Possible it is gradually using the PC's resources
such as virtual memory but I don't see why, has anyone got any
suggestions please?

>Below is the code from the forms On Timer event with the Timer
Interval set to 4000

>'================================================ ===
'Find the text file to open
Dim FindConwyTxtLocation As String
FindConwyTxtLocation = Nz(DLookup("[TextFileImpLocation]",
"[DeafaultsTbl]", "[TextFileImpLocation]"))

>'1st check Text file exists, if not exit sub now
If CheckForFile(FindConwyTxtLocation) = False Then
Exit Sub 'Exit if no text file
End If

>'Make Conwy table from TXT File.
DoCmd.TransferText acImportDelim, , "ConwyTxtTbl",
FindConwyTxtLocation, False, ""

>'===========

>'Count number of recs in Temp Table
Dim CountTempTableRecs As Long
CountTempTableRecs = Nz(DCount("[F1]", "[ConwyTxtTbl]", "[F1]"))
'MsgBox "Number of records in Temp Table: " & CountTempTableRecs

>'See if any matches in the Sites table using Conwy Query
Dim CountMatchingSites As Long
CountMatchingSites = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSitesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Sites Table: " &
CountMatchingSites

>'See if any matches in the Samples table using Conwy Query
Dim CountMatchingSamples As Long
CountMatchingSamples = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingSamplesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Samples Table: " &
CountMatchingSamples

>'See if any matches in the Notes table using 2x Conwy Queries
Dim FindMatchingNotes As Long
FindMatchingNotes = Nz(DCount("[SiteCode]",
"[ConwyCountMatchingNotesQuery]", "[SiteCode]"))
'MsgBox "Number of records Matching in Notes Table: " &
FindMatchingNotes

>'============

>'Print the correct report

>'Print the NO RECS report if No matching Sites
'If CountMatchingSites < 1 Then 'No matching Sites
'DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
'Exit Sub 'Exit now or rep prints twice
'End If

>'Print the NO RECS report if No matching Samples or Notes
'Now also prints if No Matching Site
If (CountMatchingSamples < 1 And FindMatchingNotes < 1) Or
CountMatchingSites < 1 Then 'No matching Sample - Notes
DoCmd.OpenReport "ConwyAutoPrintNoRecsRep", acNormal, "", "" 'Report
says No Data
End If

>'Has matching Site and Samples, print standard report.
If CountMatchingSites 0 And CountMatchingSamples 0 Then 'Has Site
and Samples
DoCmd.OpenReport "ConwyAutoPrintRep", acNormal, "", "" 'Print
standard report
End If

>'Added 06/10/2006 to print Notes Only report if has site with match
Notes but no Samples
'If Site matches, Match in Temp Tbl, Matching Notes
If CountMatchingSites 0 And FindMatchingNotes 0 And
CountMatchingSamples < 1 Then
DoCmd.OpenReport "ConwyAutoPrintNotesNoSamplesRep", acNormal, "", ""
'Notes no samples
End If

>'Delete the records in temp table
DoCmd.SetWarnings False
DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete
records DoCmd.SetWarnings True

>'Delete the Text file that we imported from
Kill FindConwyTxtLocation

>End Sub

Thank You Lyle- Hide quoted text -

- Show quoted text -

If it appears on average less than once/hour, then why check every 4
seconds? Lyle has a good suggestion in that regard to slow the thing
down. Every minutes ought to be plenty fast. If you have some user
sitting there waiting for the file to be loaded, that user needs some
other work to do!

With regard to form timers, I've used them extensively for data
gathering, so I can chip in what little I know.

I have one application that uses the form timer on a (coincidently)
4 second interval. It grabs data from an ODBC source and stores it.
It runs for 24 hours - mainly because at that time, close to 2GB of
data has been collected - the bulk of it in one table. My customers
have used Access 2000 and 2003 both successfully.

Hope this helps.

-- Larry Engles

[PeaceManGroove]

You know, when I had a routine that repeatedly called update and
deletion queries that where with the DoCmd method, I got weird errors
saying . . .
"Access has encountered an error and needs to close"

When I changed them to . . .

DBEngine.Workspaces(0).Databases(0).Execute "DELETE * FROM
SomeTable;", dbFailOnError

My Error problem mysteriously disappeared !!!!

Maybe changing your code . . .

DoCmd.OpenQuery "ConwyDeleteRecsQuery", acNormal, acEdit 'Delete records

Can Help ?

Good Luck !!

[Tony Toews [MVP]]

"PeaceManGroove" <gr*********@hotmail.comwrote:

>You know, when I had a routine that repeatedly called update and
deletion queries that where with the DoCmd method, I got weird errors
saying . . .
"Access has encountered an error and needs to close"

When I changed them to . . .

DBEngine.Workspaces(0).Databases(0).Execute "DELETE * FROM
SomeTable;", dbFailOnError

I've shortened that to CurrentDb.Execute etc

>My Error problem mysteriously disappeared !!!!

Now isn't that interesting. Thanks.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[CDMAPoster]

On Mar 21, 7:39 am, lyle fairfield <[e-mail]:

Ian <[e-mail] innews:Sr****************@newsfe4-win.ntli.net:

I have an Access 2000 database that checks once every 4 seconds to see
if a text file exists, if it does then it opens the text file, reads
an ID, opens a report based on that ID, then deletes the text file and
continues to wait for another text file to appear. This text file
appears about 20 times a day on average, the database is open 24-7 and
never re-booted just waiting in the background for the next text file
to appear.

I had a friend who bought a new Honda Accord with a manual transmission. It
was red-lined about 6800. When accelerating he drove in low until the tach
reached 6800. Then he shifted to second and drove in second to 6800; ditto
for third. Fourth and fifth were almost unnecessary he told me; being a
passenger at the time, I agreed with him immediately.

After a year of this abuse, his engine (yes a HONDA ENGINE) failed.

This and your post go to show that Honda engines under severe abuse may
last for fifty-two times longer than Microsoft Access/JET engines under
severe abuse.

--
lyle fairfield
-
Ceterum censeo Redmond esse delendam.
-

Lyle,

You've incidentally touched on one of the reasons people like manual
transmissions. I have two '94 Camaros, one a Z-28 (V8) automatic (red
with a black top) and the other a V6 manual (white above, silver
below). Both cars were in Florida until recently and spent their
lives there. The V6 had been owned by the owner of a trucking
company. Until I let my mechanic talk me into putting an OEM flywheel
on it, the custom flywheel accelerated the car like nothing I've ever
seen before. The OEM flywheel is much more sluggish but probably
keeps the engine from wearing out sooner. The 95's had a lot of
problems due to all the different busses being used simultaneously.

I never use the forms timer.

James A. Fortune
CD********@FortuneJames.com