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
Solved

Access 2007 and LINKED external table (portable drive)

Posted on 2010-09-16
3
399 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
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

789 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