Change in table contents

N

neeraj

I have several queries which use a table Table1. Now the contents of this
table have changed to Table2. Table2 has the same fields as Table1 but some
records have been added, some no longer there.
In these queries, Table1 is joined to other tables/queries.
How can I have my queries keep working? I have thought of deleting Table1
and renaming Table2 as Table1. But that way, all the joins involving Table1
are lost. Is there a way out
 
B

bpeltzer

Copy Table1 and paste it, with some other name (never a bad thing to have a
backup). Then right-click on Table2 and rename it as Table1; indicate it's
okay to overwrite the existing Access object.
 
P

peregenem

neeraj said:
I have thought of deleting Table1
and renaming Table2 as Table1. But that way, all the joins involving Table1
are lost.
Click to expand...

I think you are mistaken: joins defined in Query objects are persisted
as (uncompiled) SQL text are retained even when an existing table is
removed. Steps to reproduce this:

CREATE TABLE Test1 (key_col INTEGER);

CREATE TABLE Test2 (key_col INTEGER);

CREATE VIEW qryTest
AS
SELECT * FROM Test1
INNER JOIN Test2
ON Test1.key_col = Test2.key_col;
-- creates Query object including a join

SELECT * FROM qryTest;
-- works without error

DROP TABLE Test1;

SELECT * FROM qryTest;
-- fails with error 'cannot find Test1'

CREATE TABLE Test1 (key_col INTEGER);

SELECT * FROM qryTest;
-- works without error, join has been retained
 
Top