Tip Drag & drop objects from one database to another

[isladogs]

After almost 20 years using Access, it amazes me that I still find out new things all the time...

When I want to copy objects to a new database, I have always done so by importing or exporting by the usual methods

By chance I just discovered you can drag & drop as well! MUCH easier...!
Holding down shift & control allow multi-select in the usual way

The only drawback I can see is that linked tables in the source database become local tables in the destination database

If this is also a "well I never knew that " moment for you, do have a look at the attached video which shows this in use - not quite Oscar winning standard on this occasion

And if the rest of you already knew you could do this ... perhaps it just proves how little I really know

 

[RuralGuy]

So you CAN teach an old dog new tricks!

 

[isladogs]

Depends on who the old dog is in this case

 

[isladogs]

I'm just giving this a bump as it appears several experienced Access developers including MVPs are still unaware of this very useful feature.
In fact, after hearing about it from me, Mike Wolfe has just written an article on the subject Hidden Feature: Drag and Drop Access Objects Between Files (nolongerset.com). Thanks to @NauticalGent for letting me know about Mike's article

Back in post #1, I mentioned one drawback. When linked tables are dragged those become local tables.
I've since found a work-round to this which is far from intuitive....
Drag the linked table(s) across to the other database a second time. This time Access will ask you what to do with the table(s) ...select linked table and the local table will be replaced by a linked table. See the attached short video

In addition if you drag any Access object such as a table onto the desktop, a shortcut is created in the desktop. The shortcut will have a suffix .MAT
When the shortcut is double clicked, the database opens directly to the table.
This works for any Access object e.g. form shortcut .MAF, query .MAQ, report .MAR, module .MAD etc
Personally, the only time I’ve ever found this shortcut feature useful is for quick access to items in a DEMO session

 

[oleronesoftwares]

I'm just giving this a bump as it appears several experienced Access developers including MVPs are still unaware of this very useful feature.
In fact, after hearing about it from me, Mike Wolfe has just written an article on the subject Hidden Feature: Drag and Drop Access Objects Between Files (nolongerset.com). Thanks to @NauticalGent for letting me know about Mike's article

Back in post #1, I mentioned one drawback. When linked tables are dragged those become local tables.
I've since found a work-round to this which is far from intuitive....
Drag the linked table(s) across to the other database a second time. This time Access will ask you what to do with the table(s) ...select linked table and the local table will be replaced by a linked table. See the attached short video

In addition if you drag any Access object such as a table onto the desktop, a shortcut is created in the desktop. The shortcut will have a suffix .MAT
When the shortcut is double clicked, the database opens directly to the table.
This works for any Access object e.g. form shortcut .MAF, query .MAQ, report .MAR, module .MAD etc
Personally, the only time I’ve ever found this shortcut feature useful is for quick access to items in a DEMO session

Thank you for this post, never knew this feature exists.

 

[isladogs]

Ah! Not Typo MAT for tables! MAF for forms.

???????????

Although I don't find all the desktop shortcuts for Access objects particularly useful, there are 10 different types in all, even including views and stored procedures. I've also included one other strange file type associated with Access...

I've no idea what the diagram object is that would give a .MAG shortcut ...nor do I know what a .MAU file is.

Right clicking the shortcuts also allow quick access to e.g. table design view etc....

BTW Having checked in earlier versions of Access, both drag and drop features have been available since Access 97
Its amazing how neither feature seems to be widely known about, even amongst experienced developers, especially when its so useful for transferring objects between databases.

 

[arnelgp]

somebody is using it way 2013?
Drag and drop objects from one mdb/adp to another - Access 2010 (microsoft.com)

 

[NauticalGent]

I completely missed this thread back in 2018...we should have a (moderated) "Tips and Tricks" forum, perhaps?

 

[isladogs]

somebody is using it way 2013?
Drag and drop objects from one mdb/adp to another - Access 2010 (microsoft.com)

Hopefully, some people have been using it since 1997 when the feature was first added to Access

I completely missed this thread back in 2018...we should have a (moderated) "Tips and Tricks" forum, perhaps?

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

 

[isladogs]

Hi Tony
As you say, the shortcuts feature is more widely known though, in my opinion, not often that useful.
I mentioned how to use the shortcuts back in post #4. Right clicking the shortcut gives several useful options including design view

Its the drag and drop from one dB to another which seems to be almost completely unknown....despite being very useful.

I'm certainly not claiming to be the first person to discover either feature...just trying to make them more widely known.
In fact the only relevant hits on Bing are for threads I've posted at e.g. Bytes and AF.net together with Mike Wolfe's article.

 

[NauticalGent]

The form one "MAF" is definitely useful!

Open your database, minimise it so you can see both your database and desktop, then drag your menu Form, or any other form for that matter, onto the desktop. Now whenever you want to open your database, click on the shortcut and it will take you straight through to that form without using any macros or Autoexc!!!

Sounds like a separate tip!

 

[isladogs]

On different subject, I didn't realise the alerts feature told me before someone posts...in this case @NauticalGent

 

[Sun_Force]

I used to drag & drop objects between my test databases when I started using Access. Every office application allow drag & drop objects. I mostly use it in Microsoft Visio.

Every now and then I received a message that the database is in a situation that can not continue this process (or some messages like this)
I had to close and re-open both databases to be able to copy objects.

Later I noticed if one of the databases has an unsaved object (form, query, table, report) or is not compiled, I receive this message. It was enough to compile both databases and save the opened objects.

Access could be more user friendly if the message contained which database needs to be compiled.

 

[isladogs]

Definitely a candidate for a YouTube video!!!

I'm currently editing a video on this topic. It will be ready to upload to my Isladogs channel in an hour or so

 

[isladogs]

Video just uploaded to YouTube:

 

[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