Results 1 to 5 of 5
  1. #1
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    Link Excel spreadsheet to the source spreadsheet to avoid problem in Access?

    Hello Forum,



    I didn't even know how to write the title of this thread… I really need help!

    Ok… I had a spreadsheet -File1- that I linked as a table to my Access DB. I found out that, when File1 is open (because someone is updating it or whatever), I get error messages while in the DB (I don't remember in which case though, I didn't write it down).

    therefore I created a new spreadsheet, File2 in which I copied File1 data with links. File2 has become my linked table in Access. This way, I don't mind if File1 is open or not, BUT:

    - Do I need to go and open File2 from time to time in order to update the data that might have been changed in File1?
    - Could I have kept File1 as a linked table thanks to a magic trick so Access wouldn't bother me if it was open or not?

    Thank you for your time and your help. Have a great day all!

    S.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    or keep file1 linked.
    internally, have a table the same as File1.
    you would import the data via append query. It would empty then copy the data from File1.
    run all queries/reports based on the internal table.

    to update the data , run the import macro. If it gives an error either:
    1. wait for the user to get out of file to run update
    or
    2. run the existing data in internal table
    or
    3. ask around for users to get out THEN run the update.

  3. #3
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Hello ranman256,

    I tried this trick, with the following code:

    Code:
    CurrentDb.Execute "INSERT INTO [Table1] SELECT * FROM [TbFile1]"
    But that was one case where, if File1 was open, I was prompted that the DB engine could not write nor read File1 because it was open…

    That's why I tried something with File2.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    But you now deal with 2 files,
    as opposed to 1 table and 1 file to update from.

  5. #5
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    So you are telling me that there is no way to avoid or bypass the "File is open" problem?

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

Similar Threads

  1. Replies: 1
    Last Post: 03-15-2019, 08:20 AM
  2. Replies: 21
    Last Post: 09-18-2015, 11:54 AM
  3. Replies: 1
    Last Post: 02-02-2015, 04:08 PM
  4. Replies: 9
    Last Post: 09-18-2014, 10:16 AM
  5. Replies: 1
    Last Post: 03-05-2012, 04:21 PM

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