changing SQL servers hurts Access query

[Rockitman31]

I have an Access database that has links to Microsoft SQL tables through ODBC.



We have 2 SQL servers and we are retiring one of them.

One of the queries in this Access database refers to a table on this soon to be retired SQL server.

I have already recreated this table onto the other SQL server and have changed the reference to it in Linked Table Manager, prompted for its new location.

Now when I run the query, it just appears to stall. The query usually takes about 15 seconds to complete. Now it runs forever and won't finish. I've let it go for over an hour once and it still didn't complete.

I have no clue why this is not working.

[rpeare]

Was the previous SQL table indexed? Is the new SQL table not indexed?

Have you tried rebuilding the query step by step to see what's causing it to stall? If you can find out where it starts to have problem you may be able to change your SQL statement to be a bit more efficient. Start with a very basic table using the 'new' table just a simple select query that lists all the data and see how long it takes to run vs the old server then go from there.

[Rockitman31]

thanks for the tip. I didn't have the primary key set and so the index was not working.