Solved

Access 2007 and LINKED external table (portable drive)

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

911 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now