Acc2003: Exporting query objects results in loss of join

[Michael Matuszak]

I often import query objects to an empty "holding" MDB file to send them as
an email attachment to another user for importing into a copy of the full
database that I have. I do this so that I do not have to email the entire
database file, which can be 100+ MB in size. When I used to do this with
Access XP, the joins were preserved and the field names remained intact as
long as I did not open the query in the "holding" MDB file. In Access 2003,
the joins are removed and all the fields in the query are given "Exp_"
aliases the second the query is imported to the "holding" MDB. Is there a way
around this?

 

[Klatuu]

Try opening the query in design view, switch to SQL view, copy and paste to
your email text, then have the person on the other end do the same thing in
reverse.
Create a new query, close the Show Table dialog, switch to SQL view, paste
the text from the email, and switch back to design view. Every thing will be
in place.

 

[Michael Matuszak]

I had thought of that, but I often deal with non-technical people who are
already trained to import objects. I was hoping to avoid having to train them
to switch views and paste text, with the inevitable annoyance of smart quotes
popping up. Thanks for the quick reply. It's probably my only option now, but
I really question why Microsoft had to make this change.

 

[Klatuu]

On re-reading your original post, I think maybe I misunderstood. I think
that as long as you don't open the query until it gets to the destination, it
should maintain its relations and definitions. Any time you open a query and
the queries or tables it references are not visible, the field references
change to Exp and the relations are lost.

 

[Michael Matuszak]

That is the whole issue. What you describe here is true of Access 2002 and
earlier. However, something has changed with Access 2003. You no longer have
to open the query for the joins to be lost and the fields renamed. It happens
as soon as the query is imported into the holding MDB. Access 2003 is somehow
evaluating the query on import, not finding the tables and changing the
query. It is now doing immediately on import what it used to do only when the
query was opened.

 

[Klatuu]

I was not aware of that. That is ugly. One question, though, how have you
been able to determine that is when it is happening?

 

[Michael Matuszak]

Simple. I create a new MDB file. I import query C which is based on tables A
and B without importing tables A and B. I do not open query C after I import
it into the new MDB file. I then open a copy of the original database that
contains only tables A and B and not query C. I import query C. I then open
query C to discover the join has been removed and the fields all have aliases
"Exp1", "Exp2", etc.

I have found that if I EXPORT query C to the new MDB file and then IMPORT
query C into a copy of the original database, everything is preserved. So now
at least I have a less cumbersome workaround than copying and pasting text in
SQL view. I still want to know what redeeming purpose Microsoft had for this
change.

 

[Klatuu]

Very interesting. What if you also import tables A and B into the new mdb
then open the original and import query C? I'm wondering if the loss happens
in the new mdb. I know you are not opening query C in the new mdb, but maybe
at some point in time during the process, query C checks it's references
without actually being opened.

 

[Michael Matuszak]

I can only assume that Access is analyzing the query as it imports into the
new MDB, since EXPORTING the query to an empty MDB file does not have the
same effect, as evidenced by the fact that the exported query C imports back
into the full MDB intact.

 

[Klatuu]

This is an interesting problem. It would be good to know when Access
analyzes the query. I think I might do some experimenting with that. I
don't know that I can get to it for a while. If you figure anything out,
please post back. I will do the same.

 

[B.Newman]

I'm also getting the same problem. I have an automatic updater MDB
which carries updated program elements (forms, tables, queries, etc.).
The end user runs the updater and it copies all of the updated elements
into the application.

In Access 2003, when it copies queries over, using
DoCmd.TransferDatabase, the queries lose all joins and all fields
become "Expr".