Auto pop up Get External Data wizard

[googalabosh]

Not sure if I should put this post in here or the Import/Export thread so I appologize if I chose the wrong one. Additionally, I know the way the database is set up isn't ideal but it's just the way it has to be.

The database is split into two parts: (1) The Front End which is utilized by the end user and includes all the queries, reports, and forms; and (2) the Back End which includes all the tables that are linked to the Front End.

Every few months I send out an update to all the users to update their Front End databases. Everytime they update them they have to relink their databases to their Back End databases (again, I know this isn't ideal but just how it is). My goal is try to make this process easier for them.

What I hope to accomplish is that when they open the Front End database after updating it, the system will check for a tables. If no tables are found it will automtically open up the Get External Data - Access Database wizard. I know I'm only talking about a couple clicks saved here but the less they have to do the better. If you have an even better idea to make this even easier please feel free to share.

Additionally, I am not trained or experienced in VBA (I assume this is what will need to be used). I am still trying to find time to learn it so if you are able to just point me in the direction of some code to use that would be very helpful.

TIA!

-Justin

[ranman256]

you need to relink YOUR copy to the BE table using UNC paths: \\server\folder\database.accdb
do NOT use a drive letter, since others may not have it.
(if you used drive letters, you must relink and ask for new location paths)

as long as everyone is on the same network where the BE tables are , the copy you give them will always be linked correctly.

[googalabosh]

I'm having issues trying to set up the UNC path. Could have something to do with the way my company has the network set up or maybe I'm just doing it wrong. Either way, I have a follow up question based on your answer:

Every employee has their own network drive linked to their profile. Our personal drives have the exact same drive letter( F:\). So, if everyone saves the BE in a folder in their personal drive with the exact same name/path (i.e. F:\Database Customers\Back End Data.accdb), wouldn't this work?

I've tried setting this up on my end but when an employee updates their FE it doesn't work. The path is exactly the same but just to a different "virtual drive". I'm not understanding why this doesn't work.

-Justin

[Gicu]

Because you are not using the same back-end; the back-end needs to be placed in a shared folder on the network where all users have access, not in their private F:\ drive (that acts as their network "My Documents"

Cheers,
Vlad

[googalabosh]

Unfortinately, we can't share the BE. If that is the problem than I guess people will just have to relink the database everytime there is an update.

[Gicu]

A bit confused now, so you have a database with multiple users and each one has their own back-end (and front-end) in their own personal drive? Then the data will not be shared....

Usually a split database is deployed with each user having their own front-end (in their F:\ drive in your case) and all of those front-ends will have linked tables from one back-end located in a shared location on the network (where all users have read/write access).

Have a look at my free utility (http://forestbyte.com/ms-access-util...a-db-launcher/) that would allow you to deploy front-end updates with ease as long as the back-end is accessible by all users.

Cheers,
Vlad