linked table long query not working

[mirthcyy]

I have an access program that have been using linked table through ODBC connection to our SQL Server database. When I sent a query through Access, the Access sometimes just stop responding. It seems if I select too many columns, it will stop working. I'm not sure if the query string is too long (about 500/600 charaters). Could you please let me know how to fix that? Thanks.

[Icewolf0927]

I have actually seen this a couple of times. I have found out that even though it might say that it is not responding it really is still working in the back. I would leave it run for awhile and see if it work. The other thing is you might want to break up the queries into smaller ones this might help too.

[mirthcyy]

I have actually seen this a couple of times. I have found out that even though it might say that it is not responding it really is still working in the back. I would leave it run for awhile and see if it work. The other thing is you might want to break up the queries into smaller ones this might help too.

I've let it run for over 30 minutes and I also did the trace on sql server and didn't see the query's running. So I don't think it actually runs the query.

The query runs fine when I select a few columns but if I select more columns it just stops working. It's not a particular column that makes it stop since I tried different combinations.

[Icewolf0927]

How big is the table you are pulling from? I have to let things run over night in some cases. Have you tried importing the tables that you need to query from? I have been at some clients sites that had to switch to a different applications to pull queries from sql tables because access was too slow. If you are interested in switching to something else you could try Hyperion I believe that is free software that you could use. I know that Toad works pretty well too. There are also some sql programs out there you could use.

[mirthcyy]

How big is the table you are pulling from? I have to let things run over night in some cases. Have you tried importing the tables that you need to query from? I have been at some clients sites that had to switch to a different applications to pull queries from sql tables because access was too slow. If you are interested in switching to something else you could try Hyperion I believe that is free software that you could use. I know that Toad works pretty well too. There are also some sql programs out there you could use.

The table is big but I'm only pulling just few hundreds rows of data. If I select fewer columns, it will return in seconds. If I add a few more columns, it will not return. I don't think adding a few columns (simple data type columns, not xml or nvarchar(max)) should make such a difference in terms of query execution time. I just try to create a simple and quick interface (use access forms and some VBA scripts) to let non-technical people run some reports. Hyperion is good but I have to develop and deploy reports. Much more complex. For Toad, I can't create a nice interface. Thanks for suggestions though.