have to rebuild query to run

[Guest]

1. I have been running the same query for months, with no problems
2. The query takes about 3 minutes
3. Twice now, over the last several months, the query just kept on running,
past 20 minutes before i aborted.
4. Both time, i rebuild the query from scratch, renamed it the same query
that it replaced.
5. Both times, the rebuild queries ran successfully
6. I'm not technical, but am curious and would appreciate if someone could
tell me what may be the reason for this to happen. Both the old and rebuild
queries ran against the same tables..

Please advise and thanks

 

[Guest]

Please provide the SQL of the query. It might give us some hints.

If the database is out on a network drive, it's possible that you are
actually having network issues.

There's also a good possibility that it's a database corruption issue.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Guest]

Thanks the following is the sql..
I would think rebuilding the query would result in the same problem since
it's going against the same tables.. unless queries can become corrupt

SELECT chdc_location.LOCATION_NO, [085a location to config id
xref].loc_floor, [085a location to config id xref].loc_aisle, [085a location
to config id xref].loc_bay, [085a location to config id xref].config_key,
chdc_location.level, [085b Configuration master].storage_type, [085b1
configuration descriptions].config_desc, "none" AS locid_xref, 0 AS loc_quad,
"**" AS prod_groups INTO [085c location storage types using the config master]
FROM ((chdc_location INNER JOIN [085b Configuration master] ON
chdc_location.level = [085b Configuration master].lev) INNER JOIN [085a
location to config id xref] ON (chdc_location.area2 = [085a location to
config id xref].loc_floor) AND ([085a location to config id xref].config_key
= [085b Configuration master].config_key) AND (chdc_location.bay = [085a
location to config id xref].loc_bay) AND (chdc_location.aisle = [085a
location to config id xref].loc_aisle)) LEFT JOIN [085b1 configuration
descriptions] ON [085b Configuration master].config_key = [085b1
configuration descriptions].config_id;

 

[Guest]

The query looks somewhat straight forward with a couple of exceptions. It's
got a somewhat busy multi-field left join. I also saw the "**" being
inserted. It might just be possible that it's being seen as a wildcard.

Still I'd be worried about a corruption issue as it's happened more than
once. Queries can become corrupt; however, I'd think that it could be
something in the tables that's a problem. Any of the field in question a Memo
field?

Could anyone else be working on this database when it happens? Maybe it's a
record locking issue.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks the following is the sql..
I would think rebuilding the query would result in the same problem since
it's going against the same tables.. unless queries can become corrupt

SELECT chdc_location.LOCATION_NO, [085a location to config id
xref].loc_floor, [085a location to config id xref].loc_aisle, [085a location
to config id xref].loc_bay, [085a location to config id xref].config_key,
chdc_location.level, [085b Configuration master].storage_type, [085b1
configuration descriptions].config_desc, "none" AS locid_xref, 0 AS loc_quad,
"**" AS prod_groups INTO [085c location storage types using the config master]
FROM ((chdc_location INNER JOIN [085b Configuration master] ON
chdc_location.level = [085b Configuration master].lev) INNER JOIN [085a
location to config id xref] ON (chdc_location.area2 = [085a location to
config id xref].loc_floor) AND ([085a location to config id xref].config_key
= [085b Configuration master].config_key) AND (chdc_location.bay = [085a
location to config id xref].loc_bay) AND (chdc_location.aisle = [085a
location to config id xref].loc_aisle)) LEFT JOIN [085b1 configuration
descriptions] ON [085b Configuration master].config_key = [085b1
configuration descriptions].config_id;

Please provide the SQL of the query. It might give us some hints.

If the database is out on a network drive, it's possible that you are
actually having network issues.

There's also a good possibility that it's a database corruption issue.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Guest]

Thanks:

1. There are no memo fields
2. The database is not shared
3. I'll replace the "**" default to something else.. that's a good
observation.
4. I still don't see how the table can be corrupt if the rebuilt query runs
successfully against the same tables...

thanks for you time on this

The query looks somewhat straight forward with a couple of exceptions. It's
got a somewhat busy multi-field left join. I also saw the "**" being
inserted. It might just be possible that it's being seen as a wildcard.

Still I'd be worried about a corruption issue as it's happened more than
once. Queries can become corrupt; however, I'd think that it could be
something in the tables that's a problem. Any of the field in question a Memo
field?

Could anyone else be working on this database when it happens? Maybe it's a
record locking issue.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks the following is the sql..
I would think rebuilding the query would result in the same problem since
it's going against the same tables.. unless queries can become corrupt

SELECT chdc_location.LOCATION_NO, [085a location to config id
xref].loc_floor, [085a location to config id xref].loc_aisle, [085a location
to config id xref].loc_bay, [085a location to config id xref].config_key,
chdc_location.level, [085b Configuration master].storage_type, [085b1
configuration descriptions].config_desc, "none" AS locid_xref, 0 AS loc_quad,
"**" AS prod_groups INTO [085c location storage types using the config master]
FROM ((chdc_location INNER JOIN [085b Configuration master] ON
chdc_location.level = [085b Configuration master].lev) INNER JOIN [085a
location to config id xref] ON (chdc_location.area2 = [085a location to
config id xref].loc_floor) AND ([085a location to config id xref].config_key
= [085b Configuration master].config_key) AND (chdc_location.bay = [085a
location to config id xref].loc_bay) AND (chdc_location.aisle = [085a
location to config id xref].loc_aisle)) LEFT JOIN [085b1 configuration
descriptions] ON [085b Configuration master].config_key = [085b1
configuration descriptions].config_id;

Please provide the SQL of the query. It might give us some hints.

If the database is out on a network drive, it's possible that you are
actually having network issues.

There's also a good possibility that it's a database corruption issue.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

1. I have been running the same query for months, with no problems
2. The query takes about 3 minutes
3. Twice now, over the last several months, the query just kept on running,
past 20 minutes before i aborted.
4. Both time, i rebuild the query from scratch, renamed it the same query
that it replaced.
5. Both times, the rebuild queries ran successfully
6. I'm not technical, but am curious and would appreciate if someone could
tell me what may be the reason for this to happen. Both the old and rebuild
queries ran against the same tables..

Please advise and thanks