ODBC DataBase Freeze

[MindBender77]

This is hard to explain but, I have a Access 2k db that pulls over 100k records; counts the number of tech initials which gives the number of boxes each tech packed in a specific time frame.

I believe that the query is causing the ODBC db to freeze for a few seconds causing data not to updated from other application running.

Is this theory possible?

I attempted to pull just the data into a temp table in my Acc 2k app and count them from there as a work-around but, produces the same result. I eventually get the information I need but, at a cost.

Any insight or theory will be appreciated,
Bender

[NeoPa]

ODBC can be a bitch.
With recordsets of that size you need to get down and dirty with the ODBC settings sometimes. The default settings are for typical usage and your requirements fall outside of that.
ODBC settings vary per provider so I can only comment generally. The timeout setting is critical, but so can others be.

A far more useful area to explore though, would be to get the far end to do the bulk of the work for you. This depends directly on what the far end is though.

[MindBender77]

Thanks for your insight, Neopa.

However, the more I look into this problem the more "Red Tape" I find myself running into. I have decided that pulling a weeks worth of data is a bit much. I then decided to run 7 smaller queries, one for each day and appending these to a temp table inside the DB. From there I can do the appropriate calculations. This helps some what but, does solve the root problem. If I get more info on the matter, I'll be sure to send the update.

The "Red Tape" comes from not having authorization/security clearance to dig further into the problem myself i.e. ODBC settings.

Bender

[NeoPa]

ODBC is such a general thing that it's hard to deal with it specifically in a place such as this. Daily updates seems to be a work-around for now at least.
BTW Congratulations on your first 100 posts :)

[MindBender77]

ODBC is such a general thing that it's hard to deal with it specifically in a place such as this. Daily updates seems to be a work-around for now at least.
BTW Congratulations on your first 100 posts :)

Agreed. I'll keep on it and post updates just for general knowledge for others.

And thank you. TSDN has helped me more times than not in the past. I'm just trying to give back to the communities that aided me.

Bender