Editing Links to Excel Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database that links to 30 or so Excel spreadsheets as well as a
couple other databases. It works great on my end, however one of my users has
a different letter for her drive than the rest of us, which prevents her from
accessing the data.

To elaborate, our link looks like this: '(K:\Compliance\JaneDoe.xls)' while
hers would be: '(G:\Compliance\Courses\JaneDoe.xls\Sheet1$)'.

Even though the files are all on the same drive, the link goes the long
route. Is there any way to shorten the link like you would on a web page to:
'(Courses\JaneDoe.xls\Sheet1$)'?

The Linked Table Manager doesn't seem to give that option.

Thanks
 
Usually the best approach is to replace G: or K: with the server and
share name

\\Server\Share\Compliance\JaneDoe.xls

but your user doesn't just have a different drive letter, she has a
different folder structure and therefore isn't even linking to the same
files you are (unless you're all doing complicated things with shortcuts
or hard links). Or have you mis-typed one of the paths?
 
That is incorrect. She does indeed have a different letter drive. These are
assigned at random as their profiles are built at corporate so their are a
half dozen letters possible for their drives.

My question is how to edit the links. How would one go about removing the G
or K as you mention? Access does this automatically when using the Wizard.
 
You didn't answer the question. If one user is accessing a file on a
server as
K:\Compliance\JaneDoe.xls
and another use is accessing the very same file as
G:\Compliance\Courses\JaneDoe.xls
it's not just a case of havving different drive letters mapped to the
same share.

So: if the two different paths above are the result of mis-typing, and
the only thing that is different between one user and another is the
drive letter - I still recommend using the UNC path (e.g.
\\SERVER\Sharename\Compliance\JaneDoe.xls) so as to remove the
randomly-assigned drive letter from the equation.

In other circumstances you will need to adjust the paths for the linked
tables to suit the drive mapping and folder structure seen by each
machine. You can't use a relative path in a linked table. If you go to
http://www.mvps.org/access/_vti_bin/shtml.exe/search.htm and search for
relink
you'll find some code for relinking tables.
 
All I am asking is how to edit the links the Wizard created to shorten the
path. Leave it at that.
 
Sorry, I don't mean to sound rude. I am just frustrated trying to get this
going. There has got to be a simple approach to this.
 
You can't use relative paths in linked tables.

Sorry, I don't mean to sound rude. I am just frustrated trying to get this
going. There has got to be a simple approach to this.
Click to expand...
 
I have some code that I use to change files and sheets for an Excel import
file. It can be adapted to work for you.

You can't change the link, but you can delete it and re-write it
programmatically.

Dim tblPended As TableDef
Dim TableName as String
TableName = "StatusFile" 'change this to your link name
Set dbs = CurrentDb()
Set tblPended = dbs.TableDefs(TableName)

'---- after I get the info for the new link, delete the old link and
recreate a new one

DoCmd.DeleteObject acTable, TableName
Set tblPended = dbs.CreateTableDef(TableName)
With tblPended
.SourceTableName = SheetName + "$"
.Connect = TableConnection
End With
dbs.TableDefs.Append tblPended


Here's some code I use to get the old connection info - you'll need to run
it on a computer that's connected correctly to get the correct connection
info to use later.

PathCurrent = tblPended.Connect
TableConnection = Left(PathCurrent, (InStr(1, PathCurrent, "DATABASE="))
+ 8)
PathCurrent = Right(PathCurrent, Len(PathCurrent) - (InStr(1,
PathCurrent, "DATABASE=") + 8))

PathNew = PathCurrent
SheetName = tblPended.SourceTableName 'Keep old sheetname unless
changed
TableConnection = TableConnection + PathNew


HTH
Bryan
 
Back
Top