I need a query for fast search inside mdb file

Asked By Jack Reacher 10 points N/A Posted on - 11/20/2012

I need a query for fast search inside mdb file.

What I initially did is perform an open database then loop inside each record to compare the keyword variable.

Any optimization to make this search faster?

• Status: Open
• Question Views: 858
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS Access

Best Answer by Toni Linscomb

Go To Solution

Best Answer

Best Answer

Answered By Toni Linscomb 5 points N/A #135776

I need a query for fast search inside mdb file

You can use a set based search query instead of a loop. See the below SQL query.

;with dset as(select CustomerID from tblCustomer where Name='abc')

select *  from tblCustomer c inner join tblCus_Room cr on cr.CustomerID=c.CustomerID

innerjoin tblRoom r on r.RoomID=cr.RoomID inner join dset ss

on ss.CustomerID=c.CustomerID

What the query does is first it select a dataset which satisfy a specified condition.

Then it selects the data from the query according to the dataset selected previously.

Result will be similar to what you get from using a loop but this method is really faster than the looping query.

 

About Toni Linscomb

Questions
0

Answers
197

Best Answers
86

• Vote Up 0 Vote Down

• Posted on - 11/20/2012
• Question Category: MS Access

Answered By Ramonag jenniferf 0 points N/A #135777

I need a query for fast search inside mdb file

All you have to do is to set a unique keyword or address in each of every record in your database.

Make a short description in each every record so that if you want to make a query in your database, it is easier for you to locate the one that you are searching for.

If you use multiple form on you database.

Make sure that they are properly related to each other.

About Ramonag jenniferf

Questions
1

Answers
109

Best Answers
29

• Vote Up 0 Vote Down

• Posted on - 11/25/2012
• Question Category: MS Access