I have the following query, that has been in use for 3 years that extracts
GL positing from a Jet/Access97 table that has existed for 10 years. For
some reason the query quit returning records.

SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

tblGLPost.Comment is an indexed 30 character text field, duplicates allowed
tblGLPost.Tag is an indexed 15 character text field, duplicates allowed

The Tag criteria is added with each run of the query. Removing the "Tag"
criteria, returns records. Replacing "INV*ENTER" with "INV*", returns
records. During testing I created and run the query from the BE file.

I did a Compact and Repair on the Backend MDB. I Created a new MDB and
imported the tables from the old. I created a new table and copied with a
append query the records from the old.

After reducing the number of records in tblGLPost, the original query works.
The table had over 900,000 records, I archived a few years and it now has
569,826 records.

Has anyone seen this in Access?
Have I missed something else to try?

paii, Ron wrote:

Specifications). The following are the limits for Database, Table, and
Query. The size, I'll assume, grew in future versions but then you have
to take into consideration Unicode. With 900K records, even if the size
has increased sin A97, it's possible you hit a limit.

Database (.mdb) file size 1 gigabyte. However, because your database can
include linked tables in other files, its total size is limited only by
available storage capacity.

Table:
Table size 1 gigabyte

Query:
Recordset size 1 gigabyte

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

The file size of the BE MDB file containing this table is 88,852 KB as
reported by Windows.

paii, Ron wrote:

a #Error in one of those fields. It would have been hard to track down
if there was one. I don't believe a compact/repair would remove/fix a
corrupt value.

Did you keep a backup prior to archiving/removing records. If so, what
happened if you removed the filter. Then put in the first part of the
filter (((tblGLPost.Comment) Like "INV*ENTER"). Then again on the 2nd
part of the filter ((tblGLPost.Tag)="0014266")).

If it worked for no filter and for one of the filters but not the other
then there could have been a corrupt record...but that's just guessing.

On Dec 21, 12:07*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
what happens if you delete the 2 indexes, does your query work (albeit
slowly)
what happens if you then recreate the indexes, does the query still
work ?

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote

Thank you for you reply

The query works if remove the ((tblGLPost.Tag)="0014266")) part or if I
change the comment filter to (((tblGLPost.Comment) Like "INV*"). It looks
like having the "*" wildcard in the middle cause the problem. Using
(((tblGLPost.Comment) Like "INV*ENTER*" AND ((tblGLPost.Tag)="0014266")))
does not work.

paii, Ron wrote:

What happens if you try
InvCheck1:Left(tblGLPost.Comment,3) (criteria "INV")
InvCheck2:Right(tblGLPost.Comment,5) (criteria "ENTER")
InvCheck3:Clng(tblGLPost.Tag) =14266 (criteria True)

Any difference if you use % instead of *?

The tag field seems to contain a problem in a record if it doesn't work.
Same with Comment. If that's the case, it's most likely a recent
error. Maybe look at records from the last month or two to limit your
search and see if you see any field that contains #Error in it.

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:
I deleted all indexes on tblGLPost and the query did not work.
I then recreated the indexes and the query did not work.
I duplicated the table structure then copied all the records using a append
query to the new table. The query on the new table also did not work.

"paii, Ron" <none (AT) no (DOT) com> wrote in
news:hgoh1d$sv0$1 (AT) news (DOT) eternal-september.org:

Perhaps one of the indexes got corrupted. Normally a compact would
reveal that, but maybe not. Also, keep in mind that there are three
indexes involved, the two on the fields you are searching and the
primary key index (which is used to get the actual data pages where
the data is stored). A corruption of any of those three could cause
unexpected results.

I would try dropping all the indexes on the table and recreating
them and seeing if that fixed the issue.

Certainly 900K records oughtn't be a problem in a database under
100MBs in size.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

paii, Ron wrote:
This will probably take a while to run but it might shed some light if
there's some error in the file. It reads every record in the table and
every field in the table. If a field/rec is in error, it will fire the
error routine.

Sub testing()
On Error GoTo ErrTesting

Dim lngCnt As Long
Dim rst As Recordset
Dim var As Variant
Dim intFlds As Integer
Dim intFor As Integer

Set rst = CurrentDb.OpenRecordset("AllOps", dbOpenSnapshot)
intFlds = rst.Fields.Count

rst.MoveFirst
Do While Not rst.EOF
lngCnt = lngCnt + 1
For intFor = 0 To intFlds - 1
var = rst(intFor)
Next
rst.MoveNext
Loop
MsgBox "done. All recs OK."

ExitTesting:
Exit Sub

ErrTesting:
MsgBox "Broke on the " & lngCnt & " record."
Resume ExitTesting

End Sub