Results 1 to 9 of 9
  1. #1
    beha is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    10

    Import excel to access

    Hey



    I´m using an Excel file as a linked table in a shared/split access database. My problem is that 2 people can't read from the excel file at the same time.
    I get the error message that the file i write protected if more than one user open the Form that reads from the file, but it is not.
    There is no problem for 2 people to open the file from MS Excel at the same time.
    I´m using MS 2010.

    Is there any ides what causes this problem?
    I hope you understand my problem.

    Regard
    Bergur Hansen

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Two people can open the same file from Excel but the first one locks it for editing. The second would have to save edits to a new file.

    Also, my experience is that an Excel file with a link to an Access table has issues refreshing the link when the Access project is already open. Also, if the Excel is open and then open the Access, Access opens read only.

    Point is, I doubt can get around this file locking.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    beha is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    10
    Hello June7
    Thanks for the reply
    The excel file is just a look up file, i don´t wont to edit the file. I don´t care if the file is write protected, all i need is that more than one persons looks in the file at the same time. Dosen´t that change anything?

    I get this excel file from another system, what is a good alternative to linked table?
    I´am pretty new to access so i don´t know all the features available.

    Regard
    Bergur

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I have no experience with multiple users accessing a linked Excel, only solo - myself. But apparently by your experience that is an issue, just as there are issues even for solo interaction as I described.

    The only alternative I know would be to import the data.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gadjetramjet is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    An Island in the Pacific
    Posts
    2
    My experience with this was, I couldn't find a way around it, so I imported the data into Access and made it part of the data base, effectively, never using the excel file again. It actually "seems" better for the work, I created forms etc. for anyone to use, and they like the seamlessness and speed better.

  6. #6
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    To follow up on gadjetramjet's comment, if it absolutely necessary that the source data be an excel file from another location that may change over time (in which case a one-time import would not be adequate), you could consider using the DoCmd.TransferSpreadsheet method in vba to import-override the data in the Access table whenever the database is first opened, or when the user opens a particular form, or whenever is most appropriate.

  7. #7
    beha is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    10
    Thanks for all the messages.
    The excel file is changed every week so it is necessary to make an import at daily bases. I´ll check the docmd.trans to morrow.
    Thanks again.
    Regard
    Bergur

  8. #8
    beha is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    10
    I just imported the excel file into a table, then I just have to find out how to make it to a daily routine, not a big worry i guess.

    Thanks again

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    You could have a button that must be clicked to execute the import.

    Or you could have code in Open or Load event of a form that opens by default when the project opens. Multiple users? Opening file more than once a day? If you want to limit to only once a day, record last import date in a table. Compare current date to stored date, if not a match, run import.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import Data from Excel into Access
    By sauce1979 in forum Import/Export Data
    Replies: 2
    Last Post: 10-14-2011, 12:05 AM
  2. import excel file to access 2003 yes, access 2007 no
    By stapik in forum Import/Export Data
    Replies: 9
    Last Post: 07-27-2011, 07:09 AM
  3. Access Import from Excel Overwrite
    By redpanda in forum Programming
    Replies: 8
    Last Post: 07-10-2011, 09:18 PM
  4. Import Excel into access does not work
    By hawg1 in forum Import/Export Data
    Replies: 1
    Last Post: 05-28-2010, 12:05 PM
  5. Import excel sheets to access.
    By calexandru in forum Import/Export Data
    Replies: 0
    Last Post: 08-19-2009, 09:44 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums