Solved

Access 2007 and LINKED external table (portable drive)

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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…

752 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