Tip Drag & drop objects from one database to another (1 Viewer)

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

 

[Uncle Gizmo]

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

Typo MAT = MAF

 

[Uncle Gizmo]

Typo MAT = MAF

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

 

[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

 

[Uncle Gizmo]

???????????

I thought I cleared up my mistake. I am used to using MAF, I first posted in 2015

Link to other db

How can I link to second db from a cmd in first db? I tried putting the path in the link address line but that didn't work Goggle didn't bring up anything.

www.access-programmers.co.uk

and there are earlier posts by others...

So when I saw you'd written "MAT" Colin, I assumed it was a typo, which is why I mentioned it. I thought it should be "MAF"... But I see "MAT" is for table links.

 

[Uncle Gizmo]

Although I don't find all the desktop shortcuts for Access objects particularly useful,

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

 

[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

 

[Uncle Gizmo]

Sounds like a separate tip!

Definitely a candidate for a YouTube video!!!

I maintain a list of tips on my website here:-

MS Access Links - Nifty Access

MS Access Links I gather links from all over the internet related to MS Access, and of course other things of interest to me in creating novel MS Access Solutions. I was finding it difficult to maintain lists of links on the Nifty Access Website, that is, until I found this WordPress plug-in:-...

www.niftyaccess.com

It's searchable, due to some excellent software by MayMay...

https://maymay.net/

It's a list mainly to help me...

I think Jon could add MayMay's code to the WordPress structure of the forum.

However, I think it's invisible to Google, which wouldn't be good for SEO ....

 

[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

 

[Pat Hartman]

I can't remember when I discovered the drag and drop it was so long ago but I use it frequently. You need to have all objects closed or you might not be able to use the feature.