?
Solved

Access 2007 and LINKED external table (portable drive)

Posted on 2010-09-16
3
Medium Priority
?
412 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 225 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 150 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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

569 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