Hit a Jet index limit?

#11

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

Quote:

paii, Ron wrote:
"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:cuadnXBuUYU-SrLWnZ2dnUVZ_sOdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:IbidncWLgeTCU7LWnZ2dnUVZ_jqdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

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?

This is from A97 help because it's easy for me to find (topic
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

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

It appears you have plenty of room to grow. Who knows, maybe there was
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.

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.

Who knows. Maybe a recent Windows update caused some glitch.

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.

I modified the query as such and get a "Invalid use of Null" if I put
anything in for a criteria for the calculated Tag field.

SELECT Left([tblGLPost].[Comment],3) AS InvCheck1,
Right([tblGLPost].[Comment],5) AS InvCheck2, CLng([tblGLPost].[Tag]) AS
InvCheck3
FROM tblGLpost
WHERE (((Left([tblGLPost].[Comment],3))="INV") AND
((Right([tblGLPost].[Comment],5))="ENTER") AND
((CLng([tblGLPost].[Tag]))=14266));

#12

paii, Ron wrote:

Quote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:6KCdnTPMooIQb7LWnZ2dnUVZ_gudnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:cuadnXBuUYU-SrLWnZ2dnUVZ_sOdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:IbidncWLgeTCU7LWnZ2dnUVZ_jqdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

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?

This is from A97 help because it's easy for me to find (topic
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

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

It appears you have plenty of room to grow. Who knows, maybe there was
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.

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.

Who knows. Maybe a recent Windows update caused some glitch.

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.

I modified the query as such and get a "Invalid use of Null" if I put
anything in for a criteria for the calculated Tag field.

SELECT Left([tblGLPost].[Comment],3) AS InvCheck1,
Right([tblGLPost].[Comment],5) AS InvCheck2, CLng([tblGLPost].[Tag]) AS
InvCheck3
FROM tblGLpost
WHERE (((Left([tblGLPost].[Comment],3))="INV") AND
((Right([tblGLPost].[Comment],5))="ENTER") AND
((CLng([tblGLPost].[Tag]))=14266));

What happens if you create a new db, import tblGLPost, and run the query?

#13

On Dec 21, 3:15*pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7b7ea22e-e118-405f-bf46-c62c674de641 (AT) p19g2000vbq (DOT) googlegroups.com...
On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

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 witha
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?
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 ?

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.- Hide quoted text -

- Show quoted text -

so on the 900K record table
based on your original post,
this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") )

and this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE ((tblGLPost.Tag)="0014266")

but the original query doesn't
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

so it's got nothing to do with indexing and it got nothing to do with
wildcards

what happens if you create a query with the first WHERE clause, we
know that works
and then create a second query, using the first query and the 2nd
WHERE clause, does that work ?

#14

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

Quote:

paii, Ron wrote:
"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:6KCdnTPMooIQb7LWnZ2dnUVZ_gudnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:cuadnXBuUYU-SrLWnZ2dnUVZ_sOdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

"Salad" <salad (AT) oilandvinegar (DOT) com> wrote in message
news:IbidncWLgeTCU7LWnZ2dnUVZ_jqdnZ2d (AT) earthlink (DOT) com...

paii, Ron wrote:

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?

This is from A97 help because it's easy for me to find (topic
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

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

It appears you have plenty of room to grow. Who knows, maybe there
was
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.

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.

Who knows. Maybe a recent Windows update caused some glitch.

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.

I modified the query as such and get a "Invalid use of Null" if I put
anything in for a criteria for the calculated Tag field.

SELECT Left([tblGLPost].[Comment],3) AS InvCheck1,
Right([tblGLPost].[Comment],5) AS InvCheck2, CLng([tblGLPost].[Tag]) AS
InvCheck3
FROM tblGLpost
WHERE (((Left([tblGLPost].[Comment],3))="INV") AND
((Right([tblGLPost].[Comment],5))="ENTER") AND
((CLng([tblGLPost].[Tag]))=14266));

What happens if you create a new db, import tblGLPost, and run the query?

I did create a new db and imported the tblGLPost. Most of my testing was
done on the new mdb. Only reducing the record count fixed the query.

#15

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote

Quote:

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

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

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/

Per Roger's post, I dropped all indexes and the query did not work.

#16

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

On Dec 21, 3:15 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7b7ea22e-e118-405f-bf46-c62c674de641 (AT) p19g2000vbq (DOT) googlegroups.com...
On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

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?
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 ?

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.-
Hide quoted text -

- Show quoted text -

so on the 900K record table
based on your original post,
this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") )

and this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE ((tblGLPost.Tag)="0014266")

but the original query doesn't
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

so it's got nothing to do with indexing and it got nothing to do with
wildcards

what happens if you create a query with the first WHERE clause, we
know that works
and then create a second query, using the first query and the 2nd
WHERE clause, does that work ?

If I make 2 queries one pulling by tblGLPost.Comment and the other by
tblGLPost.Tag then a 3rd using the 1st 2; I get the correct records.

#17

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

Quote:

paii, Ron wrote:
"Roger" <lesperancer (AT) natpro (DOT) com> wrote in message

news:7b7ea22e-e118-405f-bf46-c62c674de641 (AT) p19g2000vbq (DOT) googlegroups.com...
On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

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?

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 ?

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.

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

Thank you for the code. I ran it on tblGLPost. It reported all recs OK.

#18

On Dec 22, 6:11*am, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7fb718ee-a295-4bd1-9832-2162359a2742 (AT) m38g2000yqd (DOT) googlegroups.com...
On Dec 21, 3:15 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7b7ea22e-e118-405f-bf46-c62c674de641 (AT) p19g2000vbq (DOT) googlegroups.com....
On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

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?
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 ?

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.-
Hide quoted text -

- Show quoted text -
so on the 900K record table
based on your original post,
this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") )

and this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE ((tblGLPost.Tag)="0014266")

but the original query doesn't
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

so it's got nothing to do with indexing and it got nothing to do with
wildcards

what happens if you create a query with the first WHERE clause, we
know that works
and then create a second query, using the first query and the 2nd
WHERE clause, does that work ?

If I make 2 queries one pulling by tblGLPost.Comment and the other by
tblGLPost.Tag then a 3rd using the 1st 2; I get the correct records.- Hide quoted text -

- Show quoted text -

um... I wonder if your original query uses temp storage, and it is
running out of it ?
I think access uses the folder pointed to the TEMP environment
variable, usually pointing to c:\temp

do you have enough free space for the temp folder ?

#19

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

On Dec 22, 6:11 am, "paii, Ron" <n... (AT) no (DOT) com> wrote:

Quote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7fb718ee-a295-4bd1-9832-2162359a2742 (AT) m38g2000yqd (DOT) googlegroups.com...
On Dec 21, 3:15 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

"Roger" <lesperan... (AT) natpro (DOT) com> wrote in message

news:7b7ea22e-e118-405f-bf46-c62c674de641 (AT) p19g2000vbq (DOT) googlegroups.com...
On Dec 21, 12:07 pm, "paii, Ron" <n... (AT) no (DOT) com> wrote:

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?
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 ?

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.-
Hide quoted text -

- Show quoted text -
so on the 900K record table
based on your original post,
this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") )

and this returns the correct data
SELECT tblGLPost.*
FROM tblGLPost
WHERE ((tblGLPost.Tag)="0014266")

but the original query doesn't
SELECT tblGLPost.*
FROM tblGLPost
WHERE (((tblGLPost.Comment) Like "INV*ENTER") AND
((tblGLPost.Tag)="0014266"));

so it's got nothing to do with indexing and it got nothing to do with
wildcards

what happens if you create a query with the first WHERE clause, we
know that works
and then create a second query, using the first query and the 2nd
WHERE clause, does that work ?

If I make 2 queries one pulling by tblGLPost.Comment and the other by
tblGLPost.Tag then a 3rd using the 1st 2; I get the correct records.- Hide
quoted text -

- Show quoted text -

um... I wonder if your original query uses temp storage, and it is
running out of it ?
I think access uses the folder pointed to the TEMP environment
variable, usually pointing to c:\temp

do you have enough free space for the temp folder ?

I have tested the query on 3 computers

I have 113 GB on the computer being used for testing.

#20

"paii, Ron" <none (AT) no (DOT) com> wrote

Quote:

"David W. Fenton" <XXXusenet (AT) dfenton (DOT) com.invalid> wrote in message
news:Xns9CE8B28D2F23Bf99a49ed1d0c49c5bbb2 (AT) 74 (DOT) 209.136.92...
"paii, Ron" <none (AT) no (DOT) com> wrote in
news:hgoh1d$sv0$1 (AT) news (DOT) eternal-september.org:

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

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/

Per Roger's post, I dropped all indexes and the query did not work.

You haven't hit any limits that I can think of. Access allows 32 indexes per
table, but that includes 1 for each relationship, so you don't see them all.
Try dropping all the relationships as well. Now do a repair, then a compact.
It doesn't hurt to compact several times.Now recreate the indexes and the
relationships.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com