 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005Acc2003: Exporting query objects results in loss of join
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? 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. > 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 [quoted text clipped - 5 lines] > aliases the second the query is imported to the "holding" MDB. Is there a way > around this? 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. > 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 [quoted text clipped - 12 lines] > > aliases the second the query is imported to the "holding" MDB. Is there a way > > around this? 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. > 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 [quoted text clipped - 18 lines] > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > around this? 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. > 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 [quoted text clipped - 24 lines] > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > around this? 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? > 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 [quoted text clipped - 32 lines] > > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > > around this? 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. > 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? [quoted text clipped - 35 lines] > > > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > > > around this? 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. > 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 [quoted text clipped - 48 lines] > > > > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > > > > around this? 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. > 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 [quoted text clipped - 54 lines] > > > > > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > > > > > around this? 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. > 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 [quoted text clipped - 59 lines] > > > > > > > > > aliases the second the query is imported to the "holding" MDB. Is there a way > > > > > > > > > around this? 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". |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.