Access won't load query when trying to view

[SaneManPritch]

Hello

I'm currently using access to try and link together a number of different databases into one. These databases are exported from an internal system we use and outputs in Excel format.

When these export there is one large database which has about 25 columns and about 3000 rows. The other information that exports are much smaller databases which link to the rows of information in the larger database and for a mail merge to work I need them all to be merged in single rows.

I've linked the main database with all of the separate smaller databases in Access so that I can run the query to combine them all together. However, when I try and run this query or view it Access appears to be loading but never does actually load. I assume it's timing out. I left it running overnight and the problem still persisted.



Does anyone know the likely reason for this? I assume it's a RAM issue, or maybe disk space or hard drive. The file size of the Access project is about 17mb, so not ridiculously big, but the computer I am using is quite outdated. Is the query likely to work with a better computer, or does Access have certain limitations for larger queries? With all the rows and columns merged with the separate database there is likely to be 3000 rows and about 100 columns. Is this too large for Access to run?

Thanks in advance.

[June7]

Have you tried on a more current computer setup? 3000 rows and 100 fields is still very small. 17mb seems big, have you run Compact & Repair? I suspect there is something wrong with the query joins. If you could import the data to working tables and provide the project by attaching to post, will look at. Remove confidential data, run C&R, zip if still large.

[ajetrumpet]

I suspect there is something wrong with the query joins.

I wouldn't sure about that. If the sql is not accepted by the engine, the query shouldn't even attempt to execute and an error should pop up.

Moreover, have you considered the complexity of your sql, SaneMan?? I still fully believe that complex queries can completely overwhelm Access's crappy database engine and stall indefinitely. I would not be surprised, and I think I've seen it happen with my own queries.

Do you see the query's progress bar at the bottom right-hand portion of your screen when execution starts? Or does 2003 even have one of those? Access does sometimes give "query too complex" error messages with sql that is over the top, however the indefinite stalling if it executes is entirely possible. In my experience, if a query takes more than 1-1.5 minutes to run, more than likely it will never produce a dataset on the screen.

You can also stop query executions by pressing ESC. Either that or Ctrl+Break. I forget which one. But at any rate, what I'm talking about here is similar to the consequence of stacking too much memory when you run a loop too long in Access vba. It will stall, and you will have to break execution manually because the end of the loop will never be reached.

[June7]

That's pretty much what I meant by 'something wrong with the query joins'. I have built some queries that were badly configured and Access would try to run them without error, they would just never render and would have to manually break.

[ajetrumpet]

That's pretty much what I meant by 'something wrong with the query joins'. I have built some queries that were badly configured and Access would try to run them without error, they would just never render and would have to manually break.

got an example?

[June7]

Not any more. It's been quite some time since I tried building a query that bugged that badly.