Query Keeps Redisplaying Results

[EddieN1]

I have a simple Select query that contains some Function Calls to get data from SQL Server. The query should return about 30,000 rows. When I open it is datasheet view, it displays about 20-30 rows then starts over at the beginning and re-displays the rows again. It's not always the exact same number of rows to display. I have set the output type to Snapshot so it wouldn't try to retrieve new data but that doesn't help. Also, I am using Remote Desktop to connect to the workstation where this is happening. Any ideas I could try? Thanks.

[pbaldy]

I'm not sure it's a direct answer to your question, but my first thought is moving the query to SQL Server (presuming all the data is there). If this query is pulling data and within it are function calls that pull other data, I can't imagine it performing very well. There are several tools available in SQL Server that may perform better (stored procedure, table-valued function, CTE's, etc).

[EddieN1]

I'm not sure it's a direct answer to your question, but my first thought is moving the query to SQL Server (presuming all the data is there). If this query is pulling data and within it are function calls that pull other data, I can't imagine it performing very well. There are several tools available in SQL Server that may perform better (stored procedure, table-valued function, CTE's, etc).

Paul, this is just a one-time type of query used to convert data from one system to another... not for frequent, production usage...

[pbaldy]

Sorry then, I'm not sure why it would keep rerunning itself. Access will always show you the first x rows but it doesn't calculate the rest right away. How about turning this into an append or make table query so it dumps the results out to where you can review them without the calculation issue?

[NoellaG]

More performant option: create a SSIS package on the SQL server for data conversion. There you can do any calculations you want and pump the converted data from 1 system to the other. SSIS is made for that.