You are here:

Using MS Access/union query in Access97 misses data

Advertisement

Expert: Geoff - 2/16/2005


Question
I have an Access 97 database that has gotten too large.  I split it into multiple databases, link them, and use a union query so I can search across all databases at once.  This has worked fine for 11 (1997, 1998, 1999, 2000, 2001, 2002, 2003a, 2003b, 2004a, 2004b, 2004c) databases.  However, when I add a 12th (2005), it won't show up in the query.  No errors - the data just isn't there.  When I modify my Union statements to add ALL, I get double 2003b, no 2005.  There is no duplicate data in these two tables.  If I drop 2003b from my query, 2005 shows up.  Do you have any idea what might be going on?

Answer
Hi Patricia,

MS Access does have an overall memory limit - Access 97 is 1GB (Access 2K - 2GB), so it may well be this, that is causing the problem. There are also other limits, like the number of columns etc etc - best check all the specification limits - see onboard help - search for 'specification'.

Good luck and kind regards

Geoff :-)

Using MS Access

All Answers

Answers by Expert:

Ask Experts

Volunteer

Geoff

Expertise

I specialise in database analysis and design, SQL and database queries using QBE and VBA. In my work, I use MS Access together with MS SQL Server as ETL (Extraction - Transformation - Loading) tools for migrating data between business ERP systems and data stores. My forte is building bespoke functions and applications.

See my website for example apps and downloads

Experience

I am a chartered engineer with 30 years of engineering and business experience, member of the BCS and have been working specifically in database applications, including SQL Server (v7/8/2000) for the last 9 or so years. I previously taught a course in Database Analysis and Design, but am now a freelance consultant and systems analyst.

Commercial database design and development work undertaken.

©2012 About.com, a part of The New York Times Company. All rights reserved.