Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Unanswered: Unable to read excel file on mapped drive using ms access query on Windows 7

    I currently have an application which resides on two PCs. This PC's are used to display meetings for each day as we have 35 conference rooms usually booked all day long which can be hundreds of meetings. The application is an MS Access 2007 database which reads an Excel.xls file on the main PC. We have a front and read entrance into the building so the application is installed on both PCs. The Excel file is on the Front Entrance PC and this drive is mapped to the read PC. The full path is provided in the query. This is using Winodws 7. The Front/Main PC runs fine and displays the meeting list. The read/back entrance gives an error message. The drive or network connection refers to is unavailble.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    As you've no doubt already figured out, an Excel file can't be opened by two applications. What you might want to do is to put the Access table on a network drive. I believe you won't have a problem opening that on both PCs.

    Sam

  3. #3
    Join Date
    Aug 2012
    Posts
    2
    Sam:

    The Access data program resides on the same drive as the Excel file. So in this case it really is only one application being run from 2 different PCs. Because the front PC has the Database and the Excel file this one works. The back PC has a mapped drive to be able to access both the database and the exscel file. I am able to get the the database up and running on both PCs but when the back PC needs to execute the query to read the Excel file then that is where ti get stuck. I have turned off my firewall, and also trend virus protection software thinking this might cause issues. I am not getting the message "The specified file "XXXXX" cannot be found" where XXXXX is the name of the named query in the access db.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the expression for the Excel file into the query? More generally, what's the SQL sentence of the query?
    Have a nice day!

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I am able to get the the database up and running on both PCs but when the back PC needs to execute the query to read the Excel file then that is where ti get stuck... I am not getting the message "The specified file "XXXXX" cannot be found" where XXXXX is the name of the named query in the access db.
    I repeat: you cannot open an EXCEL file in two locations. What I'm suggesting is that you bypass the Excel file altogether, and make the DATABASE, or an extraction of same, visible to both PCs.

    In other words, the information should be kept in Access tables, not in Excel files. I assume you can cast tomorrow's information in concrete by, say, 5:00 am. Have a routine that will populate an access table from the excel file. This table, or a query of it, should be displayed on both PCs. If something needs to be changed mid-day (in the excel file), make sure to designate the front pc as the only place to do that: make the change, and re-write the access table from the excel file. It's probably necessary to shut down the connection to the rear pc while you do this operation. When the access table is again up to date, open the table/query in the front pc, and THEN restore the connection to the rear one, and re-open the table/query on the rear pc. Similarly, you should close the rear pc's connection in the morning while you're re-writing the access file, until you display the new info on the front pc.

    Sam
    Last edited by Sam Landy; 08-30-12 at 18:41. Reason: added an afterthought

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Excel like all spreadsheets s great at presenting and analysing data. If you are a single user its tolerable storing and manipulating data. In a multi user scenario at best its dodgy at worst its downright dangerous. Its too easy to loose the detail in the morass of formulae.

    What you are doing is frankly arse about tit. I dont think excel gives you anything in this case. It certainly shouldnt be the primary storage mechanism
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •