My access search on 15000 records keeps crashing - help!

[Maggie24]

Hi all,

I have a query where by the user can enter in a description to bring back a number of records, when they enter in a single letter (which brings back about 14000) records my database crashes.

This is my search query:
SELECT tblProducts.[Product No], tblProducts.Updated, tblProducts.[Item No], tblProducts.[Element Description], tblProducts.Primary_Material_Cat, tblProducts.Secondary_Material_Cat, tblProducts.[Materal Type], tblProducts.[Base UOM]
FROM tblProducts
WHERE (((tblProducts.[Element Description]) Like "*" & [forms]![Search_Form]![txtSearch] & "*"));


Can access return 15000 plus records? Can anyone give me advice as to how to enable my search to return 15000 records as I need this functionality in my project.

Cheers,

Maggie


"Work is the curse of the drinking classes

 

[lupins46]

Not a normal Access problem.
Is your disk full?
Have you reached max size for an mdb file?
Make sure you have a backup first!

Compact your database and see if the problem goes away.

If not just copy the sql into a new query and see if that resolves the problem.

If not then create a new empty database, import everything from the old(File>GetExternalData) and see if that makes the problem go away.

If not then I think you might have an Access config problem and it may be a re-install job. But see if there are any other suggestions before doing that.

 

[scriverb]

One more thing beyond what lupins46 has suggested. When this query is run what are you attempting to do with it. Is it a RowSource for a listbox or combobox, etc. Tell us just what you are doing with it when you say run it. Since there is a reference to a forms control in the criteria statement you must be trying to execute something. Just what is that?

First do all of the things lupins46 has suggested. Then address this possibility.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[Crowley16]

yes, I have a query that returns 2millon+ records...

May I also add to Lupins46 suggestions that it may be that you're running out of RAM?