Tip Drag & drop objects from one database to another

[strive4peace]

and indeed you did, @isladogs! Here is the video,

Drag & Drop Access Database Objects (7:00)

funny comment, @NauticalGent -- because I'm inside and get it

I've marked it to watch later, when I log into YouTube so I can Like it (assume it'll be worthy). I knew about this trick but you're right, not many do -- and it's good to know!

Colin, I see while I was writing this message, you beat me to posting a link ... we must have been writing at the same time -- my message took longer to write than yours though, and I also had to update my database (smile)

 

[isladogs]

Hi Crystal
Ha! At first I thought you'd dragged the video from my post and dropped it on yours!

For anyone interested, exactly the same feature is available in Excel - drag a worksheet from one open Excel workbook to another.
Partly true in PowerPoint as well - you can drag the contents of one or more slides ...BUT you lose the background design
Not sure whether there is any equivalent feature in Word

 

[strive4peace]

@isladogs, lol

MAU seems to be a Microsoft Access FUnction shortcut and similar to MAG files
https://datatypes.net/open-mau-files

.. never used one ... yet -- I'd like to find out about the related Compressed Music Score though!
https://musescore.org/en

and I just got the irony of your drag and drop comment too

 

[isladogs]

Thanks for the link.

I'm really not sure how you could create a shortcut to a function or create a file just containing a function.

Similarly of all the 10 shortcut file types listed, the only one I don't understand is .MAG - what type of diagram is that? Is it possible to link to a diagram from SSMS?

 

[strive4peace]

remember, you're dragging to the desktop to MAKE a shortcut ... apparently, from this, it seems Access can handle some other data formats not on the menu ... that's pretty interesting if its true!

@isladogs , re: "BUT you lose the background design" -- you can click on the little floatie to keep the source formatting when you drop it -- it only defaults to using the destination's master

What I drag the most is files to open into an application window. I have a dual installation, so I can't double-click to open with the latest version of Word, Excel, or PowerPoint. With Access, though, I can right-click on a file in Windows Explorer and pick which version I want to open it with -- that's nice!

With other applications like Camtasia (where I usually want the older version but it defaults to latest -- again, I got multiple ones installed), I usually drag the video project I'm editing into the open version I want to use.

The files never have the same name, which is why I don't just go get the last one ... zip them and rename them before opening again

 

[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?

 

[Uncle Gizmo]

FAQ Here...

Access FAQs

www.access-programmers.co.uk

I just had a look in the FAQ section and can see what @Pat Hartman alluded to, there's stuff in there that shouldn't be in there! That's the essence of the problem with having a hints and tips forum, something else to maintain. We could run the site like Colditz, if anyone commits an offence just ban them, but I note other sites where they are very strict aren't the most successful or popular...

 

[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!