Discussion Locked

Searching a linked table in access 97

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I am looking for Code to do a fast search on a linked table. I have a table on the network that has 359,375 records that are indexed based on the two fields that I am serching on. I know that you cannot use the Seek command on a linked table and the find first command moves too slow. Does anyone have any Ideas?

5 replies

by mholton

I had this problem moving from an access store to a SQL store. We needed to replace seeks, and find firsts. We realized that all we needed the seeks for was to vefify we had data that matched a key combination to work with. For that we made a collection that stored those values. The rest we made into select queries, insert and update queries.

I do not have enough room to paste sample code here.

by charles_r_conic

I guess I should have said that I am attaching to an access database on the backend and that I want to use VBA code to retrieve this information. I currently use queries, but I was hoping I could find code to run instead of queries.

by kimm

To do this in VBA code or VB code you have to first dimension a record set then you use a SQL statement to pull the records in from the database into the recordset. It's a fairly lenghty process but it is the same with for whatever type of databasethat you are linking to.
You can use the same SQL statement in VBA that is used in your query. You just have to base then source of your recordset on the SQL statement. If you would like I can provide you with code but it is much to lengthy to post here.

by charles_r_conic

Thank you very much. I am actually linking to another access Database on a file server. Can you send the Code to Charles_R_CONIC@fanniemae.com.

This discussion is closed to new replies.