Tip Drag & drop objects from one database to another

[isladogs]

If I knew what a 'diagram' meant, I could probably create a .MAG shortcut!
Perhaps its another outdated relic like .MAW (Data Access Page shortcut)

I've asked Shane if he can explain both .MAG shortcuts & .MAU files

I've had dual Office 2010 & 365 installations for years. Before that I had 2003 & 2010.
When I want to select which version a particular file is opened in, I just right click the file, click Open With... then choose the version

 

[isladogs]

Access team member has confirmed that both .MAG and .MAU refer to features deprecated many years ago in Access.

Since publicising this feature, I've read comments such as it doesn't work if the object is open or if the database isn't fully compiled.
I cannot replicate either issue despite repeated testing.

However, over at AF.net, @Ajax made the valid point that table relationships cannot be transferred by this method. I will also add that neither can references.

One thing I did forget to mention is that modules can also be dragged from the VBE of one database to another

 

[strive4peace]

I can imagine that desktop shortcuts to launch a macro to RunCode might be helpful in some cases

 

[NauticalGent]

Good idea! This type of post doesn't fit in either Code Samples or Example Databases.
Perhaps @Jon will be willing to add a Tips & Tricks forum area

So @Jon, can we have a dedicated Forum for Tips and Tricks?

 

[NauticalGent]

something else to maintain.

Don't go getting lazy on me now...

 

[NauticalGent]

OK, then YOU take the crap out of FAQ's

On second thought, do we really NEED the additional overhead...?

 

[NauticalGent]

Smart man

LuvYaMeanIt!

 

[Sun_Force]

......, I've read comments such as it doesn't work if the object is open or if the database isn't fully compiled.
I cannot replicate either issue despite repeated testing.

@isladogs
to replicate follow these steps.

1. Create a new database
2. Add a new form, open it in design view, add several controls (button, textbox). DON'T save the form. Don't compile the database.
3. open an existing database, go to vba, open a module, add a procedure that has an error. something like:
   Sub Test()
   h
   end sub
4. Again Don't save the module. Don't compile the database.
5. Now from the database you've opened in step 3, drag a form to the new database from step 1

you will receive this:

I've seen different messages too. Different situations, different messages. But I think it gives you the idea.

In above steps both databases have unsaved objects and are not compiled. You can test it while only one of the databases are not compiled. Access doesn't tell you which database is in question.

 

[isladogs]

@Sun_Force
Thank you for your clear & detailed explanation.
I do hope your normal working practice doesn't use this kind of approach!

Yes doing that does trigger the message .... BUT its not proving anything specific to drag & drop.
You will get EXACTLY the same message if you try to import objects from the database with the unsaved module using the import wizard.
Or indeed if you try to transfer objects by either method from the database with the unsaved form

Until an object is saved it does not appear in the MSysObjects table. Access has no record of it
You should NEVER try to transfer objects from one database to another whilst these contain unsaved objects.
Access will prevent you doing so - that's the point of the error message.

If Access did allow you to do so, there would be far more corrupted databases as a result
The fact that the error message occurs is in my opinion a good thing

However I agree it would be helpful if the message was more specific

NOTE: Once you save the module or other object containing the error, then you can transfer objects successfully using either method

 

[Sun_Force]

@Sun_Force
I do hope your normal working practice doesn't use this kind of approach!

While I don't understand what you mean by "This kind of approach", but I have bad news. I use drag & drop objects between my apps frequently. I have a lot of objects that are shared between my databases. login form, search forms, print forms, modules, etc.
If I change the design of a form or a function, I simply drag and drop it into other applications to have the same results in all of them.

For example, I have a module containing several functions that receive a table name, a filter, and a report name, creates a pdf of the results, zip and password protect the pdf, creates a new mail with a certain body, adds the recipients and attach the protected zip to the mail.
If I change my mind and decide to change the length or complexity of the password, I change the appropriate function, and drag the whole module to my other databases.

@Sun_Force
BUT its not proving anything specific to drag & drop.
You will get EXACTLY the same message if you try to import objects from the database with the unsaved module using the import wizard.

I didn't try to prove anything. Me and @Pat Hartman (#20) explained you will receive an error if the object is opened or unsaved. You couldn't replicate the error message and I simply explained how to replicate it.
Sometimes I'm working on several objects and forget to save one of them, specially if it's a module. then I receive the error.

Until an object is saved it does not appear in the MSysObjects table. Access has no record of it
You should NEVER try to transfer objects from one database to another whilst these contain unsaved objects.
Access will prevent you doing so - that's the point of the error message.

If Access did allow you to do so, there would be far more corrupted databases as a result
The fact that the error message occurs is in my opinion a good thing

You're getting me wrong.
I don't expect Access to copy an unsaved object to another database. I only expect Access to warn me which object is causing the error. (which you accepted it could be helpful)

 

[isladogs]

I'm not trying to disagree with you or Pat
Its good to know that you frequently use this method of transferring objects.
Most developers don't do so & many had never heard of the method

'This kind of approach' was a reference to trying to transfer objects from databases where one or more objects haven't been saved

All I was pointing out that the error message is nothing to do with using drag & drop itself
Access prevents you transferring objects by any method where there is an object that has not been saved.

As already stated, I have no difficulty transferring open objects .... provided any changes have been saved

 

[isladogs]

From bitter experience with power failures etc, I'm also obsessive/paranoid about saving objects on a regular basis.
That may explain why I had never until now seen the error message when transferring database objects either using the wizard, drag & drop or copy & paste.

As I expected, further testing indicates that the error only occurs when the 'source' database contains one or more unsaved objects.
It doesn't appear to matter whether the destination database has any unsaved objects.

On repeated testing, I did occasionally find that after getting the error message when I tried to transfer one or more saved objects (using either method) from a db with unsaved objects, the file lock wasn't immediately released after those objects were saved. It is in those cases that you may need to close & reopen the database...or just wait a few seconds for the lock to be released

BUT as I keep saying, that same point applies whether using the wizard, drag & drop or copy & paste to transfer the object..
In each case, Access is deliberately protecting against possible corruption

 

[Jon]

So @Jon, can we have a dedicated Forum for Tips and Tricks?

I hadn't commented on this discussion because I wanted to see it play out first. It seems like the binary idea of having either a Tips and Tricks section or not might blind us to seeing a third way: renaming the Access FAQ section to Access Tips, Tricks and FAQs. Just a thought.

Let me know your opinions.

 

[NauticalGent]

It's a yes for me!

 

[Jon]

@Pat Hartman It depends on what you mean by subdivisions. Do you have a forum example that shows what you mean or how the layout would be?

 

[Jon]

I understand what you mean by the structural arrangement. But I am talking about layout examples of what you might have envisioned, hence asking if you had any forum examples that already exist.

 

[oleronesoftwares]

Its a No for me.

Reason most questions asked in all threads are actually asking for either tips and tricks to solve a problem.

Tips and tricks is a bit vague, a user or guest will look for threads that has similar questions or challenges he/she is facing , and not neccesarily
tips and tricks.

 

[Jon]

There are setups like this, which is what I presume you mean?

Example subforum:

 

[Jon]

Let me provide a more zoomed out view so you can see there are indeed 3 tiers. I am reluctant to have two many things that increases the need to scroll down.

 

[Jon]

I've had a look at the Access FAQ, Code Repository etc sections and there are very few posts there. I am not sure it is worth the vertical overhead for an infrequently used section of the site. Adding multiple sublayer will increase the scrolling required to get to the other areas.