Text search is too slow

[Dream.Van]

Hi,

I have search for documents form with 15 criterias. With that I build a where string in vba and apply it to a continuous form. It's source is a query.

There are 2 criterias that each search text in a different ConcatRelated Field that I build because of a many-to-many relation. I think this is where the problem is.
Because it has to create a concatrelated field for 12,000 (and still growing) reccords, then search for a part of text in them. It can take 5 min to find the results.

The Idea was to be able to search part of an information like a company name. You just have to write "Bob" and the results will be : Bob & Co, GreatBob inc, ...

A document can have many companies and a company can have many documents

Any ideas on how to speed things up?

Hop all is clear
Thanks
Vanessa

 

[jdraw]

If you really have Many to Many, that may very well be well the speed issue is.

Can you show us a jpg of your Tables and relationships?
Perhaps you could describe your application in a little more detail. Are the documents stored in Access? Structure?

 

[Dream.Van]

Sorry for taking so long to reply
Here is a view of my table relations

My database stores information about documentation we have. Kind of like a library database.

The main table is documents, it has 7 one to many relation and 4 many to many. 1 of those many to many field (township or Twp) I search in my form with a lookup list and foreign key then show a concatrelated field to show the information which is faster. The only problem is that the query gives me a copy of the reccord for every time a document has more than one twp because it show the document info with every different foreign key.


The other 3 are concatrelated before searching text in them.

Field NTS is a code of max 5 numbers and one letter ex: 32D02

Field Owner and Writer have the same structure and are controlled by the same textbox in the search form.

I was wondering if it is possible to have a first query with only the fields required for the search forms (with de primary and foreign keys and without the concatrelated fields) that gives us the primery key of each document that matches the search and take them to another query with the information that will be viewed by the user (with the concat related fields) ? I havent got a clue on how to program this in vba