Requery just ONE row from sql server

[CedarTree]

This is the standard query...

RecordSource = "SELECT usysServer_tblPersons.* FROM usysServer_tblPersons WHERE (((usysServer_tblPersons.Group)=fngsGroup())) ORDER BY usysServer_tblPersons.ID;"

usysServer_tblPersons is a Server table linked to Access with Group/ID as the index.

fngsGroup() is a constant selected earlier in the process.

The other standard query just referred to a View instead.

The passthrough was very similar, but of course had a connection string to the server.

 

[CedarTree]

Maybe another way to ask the question is this... because really all I'm trying to do is find the Row where ID = 12345 (for example) and go to that row. If I use recordset / find, it's slow. I can of course query where ID = 12345 but that won't give me the row. How do I get the row number, quickly (without looping through all words), that meets a criteria?

 

[CedarTree]

I came up with a "cheat"... I created a pass-through query that mimics the Form recordsource, but also returns Row_Number. Then I run another query off the P-T query which grabs the RowNum that matches the criteria, then I go to that RowNum on the form. It runs relatively quickly. I don't need a View, or indexing, etc.

 

[CedarTree]

Ah. It might be 10,000 records. I see what you mean. I guess I assumed that once the query found the records, doing a search should be fast, but I guess that "criteria" slows things down quite a bit. Since I found a work-around, I'm going to just use that.

 

[CedarTree]

P.S. Just out of curiosity, I fixed the query to be hard-coded SELECT usysServer_tblPersons.* FROM usysServer_tblPersons WHERE Group = 'XYZ' ORDER BY usysServer_tblPersons.ID;
And the search/find still took forever.

 

[sonic8]

I created a pass-through query that mimics the Form recordsource, but also returns Row_Number. Then I run another query off the P-T query which grabs the RowNum that matches the criteria, then I go to that RowNum on the form. It runs relatively quickly.

Interesting idea. That simulates what Access/ODBC does automatically when using a linked table (or view) with a standard query in Access.

I don't need a View, or indexing, etc.

I agree on you not needing a view. - When I suggested that, I assumed a complex query across multiple tables. - That is a very common reason for using a Pass-Through-Query.


However, I think indexing is the one thing you most urgently need! Put an index on tblPersons.Group in SQL Server and then check how your queries behave. - I guess this might solve your problem completely.