Solved

Access 2007 and LINKED external table (portable drive)

Posted on 2010-09-16
3
401 Views
Last Modified: 2013-11-28
Is it possible (I am SURE it is!) to create a linked external table link that is based on the current path?
I have created the link with the external data from the ribbon bar, but when I move from one drive to another, I always have to update the link manually. This app will be running from the server eventually, and I figure at that point it will be the same always, but during development, I am switching systems and have the app on a thumb drive and depending which system, it sepends what drive letter it gives it.

So, is there a way to modify the link path through VBA or set it to reference off of  the current db?

Thanks!
Bruce
0
Comment
Question by:Bruj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
Sheils earned 75 total points
ID: 33696249
Sounds to me that your link is fine the problem is the drive letter keeps changing.

To get around this you need to ensure that drive letter remains the same no matter which computer you plug the usb in.

I'd suggest using true crypt http://www.truecrypt.org/

With this program you can create a virtual drive on your thumb drive and give it a letter (such as "W" or "R") which is unlikely to conflict with one of the drive letters on your network. Then you save the db in this virtual drive.

You can use true crypt on your thimbdrive in conjunction with portable apps http://portableapps.com/

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 50 total points
ID: 33698003
If I have interpreted your post correctly, you may want to consider any of the following:

Remember that you had better have a rock solid recovery plan in event that the USB drive:
-Gets pulled of knocked out of the computer during a critical read/write cycle and your data is corrupted.
-Is stolen or gets lost

FWIW, a lot of times a system is created to create a "Local" copy of the DB.
(One un-split database with all the tables as static tables)

This can be very simple of very complex depending on your needs.
At the most basic level it can be that you use basic VBA code to import the Objects to an external source, or use SQL to make Local tables on the External drive.
This can all be done via a click of a button.

Also remember that if this DB is in development, the data may be irrelevant as you will probably be making changes to the Forms, Reports and queries (not really making design changes to the tables)
So in some cases I load my "traveling development" copy of the DB with dummy data.
This is also a good idea in case the USB drive is ever stolen.

Just some food for thought

JeffCoachman
0
 

Author Closing Comment

by:Bruj
ID: 33899750
Actually, what I did was what you recommended, making the drive the same letter in both systems.
I used windows (manage computer->disk manager) to assign the drive letter


Thanks!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question