Results 1 to 6 of 6
  1. #1
    markjkubicki's Avatar
    markjkubicki is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jul 2010
    Location
    Fire Island Pines, NY
    Posts
    496

    updating a linked table with VBA (with multiple users)

    background info that I sort of know, but could use clarification / verification / validation / correction...

    in code, I occasionally have to update a linked backend table
    to change the structure (add fields, relationships, change data type...) I know to unlink the tables

    ? the file would still be in use, but just not linked ?

    the problem comes if a second user also has the same file in use..
    I erroneously thought that, after unlinking, if I checked to see if the file was still in use, presuming that unlinking "closes" the file, and it was not, I could run the update code. which it does not - correct? The file would still be in use, with the tables linked by the other user. The code would fail (or as it does... freeze)



    so what I think I need to do after breaking the links, is re-check the links to see if they are still active (because although they broke from the instance that executed the code to break them, they still exist elsewhere.) And because they exist, any code to update the table will not run.

    Does this sound correct?
    (the logic is mostly from me surmising thru trial an error; but I also might have just created a myth!)

    thanks in advance, Mark

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you want to perform design changes in a db (doesn't matter if it's a back end or *front end) you should open it exclusively. You don't do this by opening from any 'recent files' list.
    If you don't you run the risk of performing changes only to find you cannot save them, are blocked from even starting the changes, or inducing corruption from conflicts. Thus it doesn't matter if the tables are linked or not. If you're performing design changes on tables, there's no need to un-link them. This statement
    to change the structure (add fields, relationships, change data type...)
    raises a little red flag. It's one thing to add table fields because your original schema overlooked some attribute of an entity in your project. It's another thing altogether to be adding fields for similar attributes when that data should be in rows in its own table. Not sure what your statement means.

    * - I'm not advocating sharing a single FE here, just saying it doesn't matter about how either should be opened.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    And you shouldn't be sharing a Front End, which it sounds like you are, from your description.

    Having multiple users sharing a single Front End, on a network drive, is, as you've found out, the surefire way to repeated episodes of corruption, as well as speed and timing problems, and all manner of strange, odd and curious behavior!

    Being in forced retirement, I've spent 8-10 hours a day here and on other Access forums/newsgroups, and over the past twelve years have seen literally dozens and dozens of reports of split databases, using links to a single Front End, causing these kinds of problems, as well as massive data loss! The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin! But once it does, they become chronic, which is to say they occur over and over and over again!

    You've simply got to place a copy of the front end on each users PC.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The really insidious thing is that this kind of setup can work for extended periods of time before the troubles begin!
    Nine years and counting, for me. My retirement wasn't forced (hope you are doing OK) but what I built is still working. Every time I see those guys (appx 6 times per year) I ask them if they can't break a db so I can be called back to fix it
    No, I wouldn't build it that way again and don't disagree with anything you've said here. I was dead set against doing what my predecessor did for distribution because it was flaky and I didn't know any better way. Now I do and wouldn't share like that again, even though the applications been working that long in a network environment, there was more than one app built this way, and nothing ever went wrong. Wish I had as much luck with the lottery
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    The record, that I've heard of, for one like this working without problems, was 12 years! Then it bombed, bombed big time, and repeatedly! Started with timing problems/write problems then, almost immediately, went into massive data loss...which with 12 years of data could have been catastrophic! Fortunately, the company's network administrator was and old hand and backed up everything on a regular basis, so they had less than a week's worth of data loss, with all of the original source material still being available.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good point on the backups. It's one detail I didn't consider because they back up nightly and have done so ever since I can remember - and it's a huge backup. Plus backups are kept off site. Then again, the nightly downloads of the ODBC data that was pulled into the tables were done by my apps. The be's were backed up once before the update to ensure that day's data couldn't be lost if the download failed. Even the ODBC tables were copies of the production data so that no one at my level could mess with it.

    Maybe I will beat the record!

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

Similar Threads

  1. Multiple users querying linked tables
    By vadnagra in forum Import/Export Data
    Replies: 6
    Last Post: 09-21-2016, 01:31 PM
  2. Replies: 3
    Last Post: 10-08-2015, 01:02 PM
  3. Replies: 2
    Last Post: 11-03-2014, 02:36 PM
  4. Updating Linked Table
    By cbende2 in forum Access
    Replies: 1
    Last Post: 07-29-2014, 01:43 PM
  5. Simultaneous Updating Continuous Form/Multiple Users
    By robrich22 in forum Database Design
    Replies: 1
    Last Post: 02-18-2013, 06:11 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