Moving Objects Between Computers

Topic

New Reply

I am not new to Access, but am very new to version 2007.

For years, when collaborating with others on database construction, we have shared our work in the following way:
We each have a copy of the database on our computer (I’ll call it Database.mdb).
If I am working on creating a new query, when I have completed my work in the copy of Database.mdb that is on my computer and want to give the new query to my co-workers in other locations I do so by

1) Creating a blank database
2) Importing the new query – nothing else – into the blank database (I’ll call if Query.mdb)
3) Give a copy of Query.mdb to my co-workers (usually by email)
4) My co-workers save the copy of Query.mdb that I’ve sent them onto their computers.
5) My co-workers open their copy of Database.mdb and import the new query from Query.mdb.

That’s it. Worked like a charm over and over (also for forms and reports – tables are more complicated because of relationships but could at least be transported the same way).

Now we are all working with Access 2007. I am finding that if I send a query using this method, unless I also import the relevant tables into Query.mdb, the fieldnames in the query all turn into names like “Expr1” and “Expr2” after my co-workers import it into Database.mdb, even if the query was never opened while it was in Query.mdb. Including the tables of course makes our transport database (Query.mdb) larger and has more potential for confusion since, using the old method, it was clear that everything in the transport database was to be imported (I am often sending more than a single query).

Our two questions are:
What changed (because we’d like to understand it)
And is there a better way to share our work than the method that we’ve been using?

Thanks,
-cynthia

Reply | Quote

Replies

I don’t have Access 2007 so I don’t know how it has changed in this respect, or why.

If you want to transfer the “bare” design of a query, you could switch to SQL view, copy the SQL string to a text file and send that to your coworkers. They can copy and paste the SQL string into the SQL view of a query in their copy of the database.

Advantages:
– The text file you send is extremely small.
– It can contain multiple SQL strings.

Disadvantages:
– Formatting (column widths, number/date/text formats) will not be transferred.

Reply | Quote

Thank you Hans.

I think that will work! Unless I’m doing something I don’t realize, I don’t think I ever do formatting in a query, anyway – in my databases all the formatting happens in the forms and reports. So I don’t think the disadvantage would make much difference to me.

Thanks again.
-cynthia

Reply | Quote

Another alternative you might explore is to create the empty database, and then instead of doing an import, close the empty database and open the design database and do an export to the empty database. I haven’t observed the behavior you describe, but we only do that sort of thing occasionally. I’ll do a bit of testing and see if I can replicate what is going on. In the meanwhile, you might consider creating an empty database with links to the tables used in the queries (to keep the size of the database down to reasonable emailing size) so the queries retain their name. But Hans’ suggestion to convert the query to a SQL string is probably the most efficient way for queries. However it won’t work with forms or reports – but your existing technique should.

Reply | Quote

Thanks Wendell.

I just tried it by including a linked table – rather than the whole table – and it seemed to work on my colleague’s computer.
That’s one of the things I think is great about Access. There is almost always more than one way to solve a problem!

-cynthia

Reply | Quote