Query two identical tables without combining

[Andy]

Hi all,

We currently have a database that is gaining size quite rapidly. As we
don't have any other methods of storing data (we can't get any other
software), my ideal solution to a future meltdown would be to start a
new database every 6-12 months and simply link all tables together in
a master database.

Say, for example, I now have two databases with identical table and
field names, but different data - both called tblAirport.

If I link them both to a master database as tblAirport1 and
tblAirport2 and merged both tables I would, again have the size issue
to contend with.

Instead of having to run separate queries for tblAirport1 and
tblAirport2 is there a way to temporarily merge them in one query?
Either that or creating an append query that lets me choose which
dates (each table has a date field) are appended to the new table, to
keep the size low enough. My SQL is not good enough to try testing
this however.

Any ideas welcome!

 

[John Spencer]

How large is the database? Access can handle databases up to a maximum of 2
gigabytes.
Do you compact the database on occasion?
Are you storing images in your database?

You can use a UNION query to combine data from two tables in one query. The
problem with union queries is that they are never updateable and they tend to
be slow.

SELECT *
FROM tblAirPort1
UNION ALL
SELECT *
FROM tblAirport2

Have you considered using MS SQL server to store the data. MS SQL 2005
express is free and allows you to have up to 4 gigabytes of storage. The 2008
version allows up to 10 Gigabytes of data.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Andy]

Hi John - thanks for the reply.

Our current database is only around 100mb at the moment but that's 2
months of data so I'd hope to maybe have databases storing a year or
so of data each. I'm thinking about a solution I can setup before it's
really needed. We compact and backup the database very regularly and
don't store images.

I've used union queries before but am confused about the "never
updateable" part. I thought they ran from other tables using whatever
data was present at that moment?

Good idea about the SQL server but our options here are very limited.
Our IT department are very stingy about giving us access to other
software and would also manage to pull rediculous costs from thin air!

If the union query is my best bet I'll play around with it and worst
case scenario we run separate queries from more than one database when
necessary.

Thanks for the advice!

 

[Access Developer]

UNION queries, by definition, are not updateable.

However, if you can determine the underlying table (and you could include
the table in a Query that you UNION), you can grab all the data from a
record in the UNION query, and use a separate query to update the actual
table.

If you do much updating, deleting, and adding, it will be especially useful
to run Compact and Repair more frequently.

Too bad you have an un-cooperative IT department. I'd guess, from what you
say, that they also prohibit your adding software (even free software like
SQL Server Express) -- that's the usual approach.

In the "contract development" business over many years, I've had occasion to
work with clients who had cooperative and un-cooperative IT departments.
Generally, when they realized that the consultant (that'd be "I") knew what
I was doing and talking about, they became more cooperative, especially back
in the days when I was employed by the primary provider of their hardware
and software.

If they do not, then see if you can get an "extra" PC installed, and install
the free SQL Server Express or some other ODBC-compliant database on it and
figure out how to share a folder, and thus the database. Or, install the
database on one of your existing machines... just be aware that you will
have fewer connections, and thus, slower response, if the server is not on a
domain that you sign into.

 

[John Spencer]

Just to clear up a bit of confusion.

Updateable means that you can add, edit, and delete the rows of data in the
query. The union query will always show the current data as of the time you
execute the query, so if the data changes in the tables, the union query will
show the changes, you just can't update the rows of data using the union query
results.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County