| MS Access hangs when accessing the Charge table |
| Author |
Message |
|
|
| Posted : 2006-11-14 02:21:59 |
| I am trying to access the Charge table (this is a large file for us) and MS Access hangs every time. I can access the table using VB demo. I can also access smaller tables (Customer, Company) using MS Access. Is there something that I can do? |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-14 08:29:33 |
|
|
|
|
|
| Posted : 2006-11-15 06:04:27 |
I tried to use the prompted data ranges. I let the query run for one hour and it still appeared to hang. Is there anything else I could try?
Ultimatly, I want to read an exported Crystal report and use the data to update the Charge file. Is there a better choice than MS Access?
Thanks! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-15 08:13:42 |
| Yes, don't do SELECT *, limit the query to only the columns you are really interested in only. |
|
|
|
|
|
| Posted : 2006-11-16 01:22:22 |
| I did that also. Same result. Help! |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-11-16 08:57:50 |
| Because of the large record sets being used here, the Iterator or Chunk Size in the Advanced Tab in the QODBC Setup Screen should be setup up to accomodate the largest returned record set to speed up your imports. The default is 500, and it can be set to any value up to 100,000. Increasing the Iterator or Chunk Size will allow the large amount of records in your CHARGE table to accessed using MS Access. |
|
|
|
|
|
| Posted : 2006-12-09 06:05:23 |
| We tried a variety of settings for Chunk size and the result was the same. Any other ideas? |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-09 07:43:46 |
| I really need to see the actual SQL of the query to get a better feal why this is taking so long. How long does the query take using VB Demo? |
|
|
|
|
|
| Posted : 2006-12-12 04:46:52 |
Here is the query that I am trying to run in MS access:
SELECT Charge.TxnID, Charge.TxnNumber
FROM Charge
where Charge.TxnNumber < 1000
It will run for several hours and hang.
I run the same query in VB demo and it returns results within 1-2 minutes. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2006-12-12 08:27:08 |
Try running this pass-through query as per: How do I create a Pass-Through Report using Microsoft Access 2003? :
SELECT Charge.TxnID, Charge.TxnNumber
FROM Charge NOSYNC
where Charge.TxnNumber < 1000
NOTE: The default timeout in MS Access is 60 seconds, for table scans on TxnNumber you will need to increase the query property to 600 seconds:
|
|
|
|
