Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Back end data table question (Access 2K)

    Dear All

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back end data table question (Access 2K)

    Is the imported spreadsheet linked to other tables? If so, you might have problems. Otherwise, you could use DoCmd.TransferDatabase to export the table from the front end to the back end. If the table already exists, and not in use, it will be overwritten by the new one.

  3. #3
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back end data table question (Access 2K)

    Hans

    Thanks for the suggestion, that's one all to obvious solution I'd overlooked......

    My concern is that it may be possible for some one to leave the database open all night, so when the 'first' user logs on and their front end detects the updated Excel files it will not be able to overwrite the old tables. This was one of the reasons for me looking at the idea of using two queries, a delete and an append. While this will mean more complexity I think it may be a more reliable method? So, are there any pitfalls I should avoid by using the query methods?

    Thanks

    Ian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back end data table question (Access 2K)

    Depends on whether the table is linked to other tables, but that problem would occur with any method. Otherwise, the delete and append queries should work OK.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2001
    Location
    Newport, Gwent, Wales
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Back end data table question (Access 2K)

    Hans

    Nope, no links. Whether this is good practice or not I'm uncertain but I tend to try and avoid table level links, unless absolutely necessary, and only link tables at the query stage.

    I've now got the query method working, just got to work out the 'Timer' instruction to create a two minute delay if the 'file in use' error (error 1004) is generated. I'm most of the way there now, thanks for the help.

    btw,my two minute delay is based on the longest time I've recorded the update taking, plus 200 percent, that should keep the code out of trouble.

    Ian

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Back end data table question (Access 2K)

    Joins between tables are very useful, and in fact an essential part of a relational database. Enforcing relational integrity, and specifying cascading updates and deletes are important to ensure the quality of your data, and take a lot of work out of your hands.

Posting Permissions

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