Stop Access saving 'broken' queries

[tt40]

Anyone know how to prevent Access 2002 from automatically breaking all
the incorrect joins in a query and then automatically saving the broken
query?

This is what I would call stupid design behaviour...Access takes your
'broken' query and auto saves it as what I call a 'corrupt' query...

1. Rename some BE tables and attributes
2. Open your FE
3. Open a query in design view that you know to now have broken names
and joins
4. Access intercedes and to inform you "can't represent joins etc..."
5. Close the query design without saving it
5. Note the 'modified' date of the query is now today.

Access has now overwritten a broken query with a totally f&*ked,
'corrupted' query and killed all the joins - the SQL joins have now
been expunged - ie. corrupted - automatically by simply opening the
qeury in design view.

And you can't read the query definition using VBA to find out what the
original SQL looked like so you can restore the joins because it's been
'fixed' by Access automatically.

Anyone have a configurable solutuion to this?

(My coded solution is to run a For Each QDF procedure to populate a
table with all the QDFs so I can edit them and test/rerun them in a
sandpit query form without having Access intervene to futher corrupt my
borken query)

Cheers
Greg

[Lyle Fairfield]

(My coded solution is to run a For Each QDF procedure to populate a

table with all the QDFs so I can edit them and test/rerun them in a
sandpit query form without having Access intervene to futher corrupt my
borken query)

Have you thought about extending your current solution to ask code to
do the edit as in:

Dim q As DAO.QueryDef
For Each q In CurrentDb.QueryDefs
q.SQL = Replace(q.SQL, "TestDate", "TrialDate")
q.SQL = Replace(q.SQL, "OldTableName", "NewTableName")
' etc
Next q
?

Now, you might have some difficulty with "TestDate" or "TrialDate"
being part of another word; in that case you may know how, or could ask
for a RegExp function/solution that would replace whole words only.

[tt40]

Indeed Lyle - that's a useful enhancement.

To date, I've done find/replace in the 'memo' field holding the QDFs
SQL string - but coding many of those replacements with the QDF object
is clever - and programmable.

(My QDFs form displays options to edit the saved SQL string - not the
original query 'cause Access would intercede - to create a temp copy of
the SQL, to test the copy as a QDF (docmd.openquery... etc.) and then
overwrite saved version if all okay.

This is all required because of a physical rebuild and BE merge - so
lots of chained queries (~100) to fix.

I guess what I'd really like is for Access to not think for me and
allow me to hold dead queries until I decide to fix them, and not
assume to proceed to clean up the broken links...by removing them.

Greg

Lyle Fairfield wrote:

(My coded solution is to run a For Each QDF procedure to populate a
table with all the QDFs so I can edit them and test/rerun them in a
sandpit query form without having Access intervene to futher corrupt my
borken query)

Have you thought about extending your current solution to ask code to
do the edit as in:

[Sky]

In my experience with Access 2000 and 2003, you can cancel the save, and the
query remains unchanged.

Perhaps you have "Name AutoCorrect" turned on, under Tools, Options,
General? That should be disabled.

- Steve

<tt**@ozemail.com.auwrote in message
news:11*********************@i42g2000cwa.googlegro ups.com...

Anyone know how to prevent Access 2002 from automatically breaking all
the incorrect joins in a query and then automatically saving the broken
query?

This is what I would call stupid design behaviour...Access takes your
'broken' query and auto saves it as what I call a 'corrupt' query...

1. Rename some BE tables and attributes
2. Open your FE
3. Open a query in design view that you know to now have broken names
and joins
4. Access intercedes and to inform you "can't represent joins etc..."
5. Close the query design without saving it
5. Note the 'modified' date of the query is now today.

Access has now overwritten a broken query with a totally f&*ked,
'corrupted' query and killed all the joins - the SQL joins have now
been expunged - ie. corrupted - automatically by simply opening the
qeury in design view.

And you can't read the query definition using VBA to find out what the
original SQL looked like so you can restore the joins because it's been
'fixed' by Access automatically.

Anyone have a configurable solutuion to this?

(My coded solution is to run a For Each QDF procedure to populate a
table with all the QDFs so I can edit them and test/rerun them in a
sandpit query form without having Access intervene to futher corrupt my
borken query)

Cheers
Greg

[tt40]

Oh - that sounds entirely feasible Sky, gonna give it a go.

(Sorry for delaying in responding)
Thanks
Greg
Sky wrote:

Perhaps you have "Name AutoCorrect" turned on, under Tools, Options,
General? That should be disabled.