Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2007
    Posts
    35

    Unanswered: Import already open excel files

    Hi all.

    Can I use DoCmd.TransferSpreadsheet to import data from Excel sheets (I only need to read the data) if the file is already open by another network user?

    If not, how can I make it possible?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I don't know, why don't you try it yourself and see if it causes a problem?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I don't think you can. You can make a copy of the file first and open the copy.

    Or import the data at midnight or something.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Nov 2007
    Posts
    35
    To answer my first question - No I can't, when I try to import it complains that the file is open.

    Copying the files just sounds like an overkill for my simple task. Importing at midnight is also not an option as I need the most recent data that I can get.

    Is there another way then?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You say overkill, we say workaround
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Is there another way then?
    You mean is there another way besides the two perfectly normal ways to achieve the goal that we have already given you?

    No.

    Oh hang on, I guess you could re-develop the spreadsheet and use VBA for Excel to get it to maintain a duplicate of itself I suppose.

    Ooooh... you could also turn the spreadsheet into an Access database!!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2007
    Posts
    35
    Thank you for the help, I appreciate the fact you are always here when I need it.

    Sorry if it seemed I'm ungrateful. The reason I am not inclined to use the given solutions is because my needs are pretty specific and what suggested will not go along nicely with my requirements.

    The fact that I am forced to use a workaround for this issue also surprised me. There may be a reason behind preventing the importing of open files, but it is certainly not clear to me.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by ronh
    There may be a reason behind preventing the importing of open files, but it is certainly not clear to me.
    It's called pessemistic locking.

    A user can have the spreadsheet open and make as many changes as they like - they can even delete all the data! But before they hit that save button, none of these changes are commited. Pessemistic locking says that if you're not 100% sure of what you're getting, then you'll wait.
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Posts
    35
    Then why not give me the version before any changes were made - the last saved version, which is very likely what the file contains before the user saves it.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That would be optimistic locking; which the powers-that-be did not implement.
    George
    Home | Blog

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ronh
    Then why not give me the version before any changes were made - the last saved version, which is very likely what the file contains before the user saves it.
    a variation on that is to get a copy of the file..... but you don't seem willing to accept that..............
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Nov 2007
    Posts
    35
    The fact that I have to make a copy of files just to read their content, then delete the copies once their use is not anymore relevant, is not efficient. Say you do not agree.

    And why should I comply with this locked file nonsense? I just want to read the content of files. This task is no more than trivial, and forcing me to find a workaround that compromises efficiency is basically tying my hands.

    This simplest issue renders what may seem a perfectly valid design erroneous and ineffective.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are designing the system in its entirety from the ground up then Id agree

    but you are trying to integrate components from elsewhere means your system has to fit thise compoinents. I can't see Microsoft chanign Excel for your use, I cant see your employers paying microsoft to change it for your app.

    Id suggest the reason you are hittign a problem is that your system is npot being designed to make best use of the Office components. Id suggestr that doing data entry in Excel is silly..... its a great analysis tool, its great for dinky formatting of reports suitable fdor accountants and other number orientated people, its OK for data manipulation.. its crap at data entry and data storage.

    in short by using excel for data capture you are playing to a spreadsheets greatest weakness. you should be doing the data capture and storage in a db (whether thats a file type db like JET or Filemaker, or a server product is irrelevant). you have precious little control over the process in Excel, its very easy to disconnect the the data from the logic, its easy to create data errors without realising.

    Porting data to spreadsheets is fine, but you have to be very very circumpsect about porting data from spreadsheets into a DB. the problems

    when you bang your head against a brick wall only you are going to get hurt, contin uing to rail against the way office apps work is a form of head banging... it'll do you no good, it wont get the job done.

    as I see it you have several choice
    do it the kludgey workaround way (take a copy, process that, then delete the copy)
    do it properly, use a db to store the data, a front end to do the data capture, excel to manipulate & report if you wish
    or continue to rail against the injustices of the world and not get the job done.
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is not about efficiency.

    You don't really get a choice but to comply with locks - it is something between the application (Excel) and the OS. If you were using text files there is no locking so you can do what you like. The task is more than trivial. You understand the ACID properties of a transaction and table locking behaviours right? What is going on here is very synonymous with what goes on in any relational database.

    Quote Originally Posted by ronh
    This simplest issue renders what may seem a perfectly valid design erroneous and ineffective.
    I would argue that this is not a valid design. Excel is a poor candidate for what you appear to be doing here. The problem you are having is one reason (and by no means the most compelling IMHO).

    Anyhoo - I believe there is an alternative. Have you tried linking the spreadsheet instead of using docmd.transferspreadsheet? I am still concerned about accessing the data whilst someone is using it but that is your design decision
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Nov 2007
    Posts
    35
    Thank you all for the kind help.
    I will have to consider how to tackle this matter.

Posting Permissions

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