?
Solved

Linking table to shared network folder

Posted on 2005-03-14
9
Medium Priority
?
253 Views
Last Modified: 2008-01-16
I am having trouble linking to an excel spreadsheet(or even importing it) from a shared network folder on our coporate network. I have tried two different ways. First I mapped a drive for the folder containing the spreadsheet, then I tried just using the UNC path to the file, I get the same error both ways. The error basically says access cannot find the path to the file, but it adds a pound sign(#) to the end of the filename instead of the excel extension(.xls) so I get an error like this:

using a UNC path I get excel cannot find the file \\networkshare\somefile# the actual path is \\networkshare\somefile.xls
I get the same error using a mapped drive" excel cannot find the file G:/somefile#

It is obvious that access can see the file because I can navigate to it wioth no problem but it removes the extension and adds "#"

Any ideas?
0
Comment
Question by:ashvillerob
[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
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Author Comment

by:ashvillerob
ID: 13535565
Correction to above, access adds a dollar sign($) to the file name not a pound sign(#)
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13535890
Post your code, please.
0
 
LVL 1

Author Comment

by:ashvillerob
ID: 13536193
code?

I am simply using  File->GetExternalData->Import
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 3

Expert Comment

by:charlietou
ID: 13538274
What are you trying to do, set a hyperlink to the file from within a field in a table?
Or are you just trying to incorporate the spreadsheet as a new table within your database?

If the second, then, starting from the main database window in the tables view,
choose File->Get External Data->Import, navigate to your network drive, and in the dropdown box at the bottom of the window, choose .xls files.  You should see a list of available files.  Once you choose the one you're looking for, Access will guide you through the Import Spreadsheet Wizard, and you should be home free.

Hope this helps,

Charlie T.
0
 
LVL 1

Author Comment

by:ashvillerob
ID: 13538335
Thanks but I am very familiar with linking a table to an excel spreadsheet or importing an excel spreadsheet into the database as a new table.

When the excel spreadsheet is stored on the local machine I have no problems, this problem is just happening when I try to import an excel spreadsheet into access from either a mapped drive or a network shared folder. I am able to choose File->Get External Data->Import, navigate to your network drive and select the spreadsheet, but once I hit ok, Access sits there a secound then complains that it cannot find the file and in the error it shows the file path and filename, but it adds a $ to the end of the file name.

For example the path is \\somesharedfolder\excelspreadsheet.xls

Access gives this error when trying to import the file:

Access cannot find the file \\somesharedfolder\excelspreadsheet$

As you can see Access removes the file extensiona dn adds a $ sign wwhen reporting the error.
0
 
LVL 3

Expert Comment

by:charlietou
ID: 13538603
I don't get it.

I just tried it on my office network, navigated to a mapped drive, chose the file, and was done.

Stupid question, but what happens when you double-click the file?

Charlie T.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13544871
Where exactly is it failing?  You said you can browse to and select the file...does the wizard stop there or do other prompts appear?  Also, have you checked the Excel file for corruption or weird data, or perhaps high-bit characters in the filename/worksheet name?  What version of Access are you using?  Excel?

Excel files are referenced as [filename]$[sheetname]![range], so it sounds like Access is taking the filename, adding the "$", then trying to append the sheet name, which it can't find.
0
 
LVL 1

Author Comment

by:ashvillerob
ID: 13545006
I gave up on Access for the import portion, now I use MySQL to import the excel spreadsheet and then export form MySQL into Access. I have never had a problem importing excel spreadsheets with Acess, so it has to be something related to fact that these spreadsheet are on a mapped drive. MySQL pulls them in with no problem at all.

Also to answer your questions I use Access 2003, the excel file is in excel 4.0 format.

When I choose File->Get External Data->Import, I can navigate to the mapped drive, select (excel file .xls) format in the dropdown menu, see all the excel files but after I select the excel file I want to import and hit enter, I get the error, that the file cannot be found. Frustrated me to the point of reverting to MySQL.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 13545451
My best guess is Access 2003 has something to do with the problem...I've heard about quite a few bugs in the new version and do not use it regularly myself (2002 at the office).  Did you try more than one file, or does it do with any file you try to import from a network drive?  What about non-Excel files?  I went to the MSKB and search on several different phrases, but all the results holding any promise regarded the ISAM entries in the registry, which I do not think is pertinent to your problem.

On the bright side, at least you found a way around it.  I'm sorry we could not be of more help.  If you'd like to continue experimenting with this to narrow down the problem, I'd be happy to sit through it with you.  Otherwise, you can post a request to close this question and refund your points at:

http://www.experts-exchange.com/Community_Support

Good luck with the rest of your project!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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