Page 4 of 4 FirstFirst 1234
Results 46 to 50 of 50

Taking data from 1 table and writing it to a second table provided certain conditions are met

  1. #46
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    40
    Hello Steve

    When I ran the program I got a runtime error: File sharing lock count exceeded

    Table 2 ended up with 178 records (which I think is correct)

    Table 1 records - some have Table 2 reference - some do not

    Bob M

  2. #47
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,648
    When I ran the program I got a runtime error: File sharing lock count exceeded
    Ah... something I may be able to help with....

    I think you mean the max locks per file which is a registry setting - by default it is 9500 but can be increased if that error arises
    See this thread for an explanation of how to increase the max locks per file (and some other related issues):
    https://www.access-programmers.co.uk...e+sharing+lock
    Colin (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

  3. #48
    Bob M is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Mar 2013
    Posts
    40
    Quote Originally Posted by ridders52 View Post
    Ah... something I may be able to help with....

    I think you mean the max locks per file which is a registry setting - by default it is 9500 but can be increased if that error arises
    See this thread for an explanation of how to increase the max locks per file (and some other related issues):
    https://www.access-programmers.co.uk...e+sharing+lock
    Hello Colin

    tried this -> upped the value to 15000
    same error

    If bSimilar loop t1.edit is where it falls over

    Bob M

  4. #49
    ridders52's Avatar
    ridders52 is offline Long time standing
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    1,648
    If it is a max locks per file issue then note that there may be several registry entries for this. From memory, I had five to update.
    I've not heard of any maximum limit and in one google search someone suggested a million.
    I've done baby steps preferring to increase as and when needed.

    Also did you reboot your computer after making the changes.
    It may not take effect till you do so.

    I haven't looked at your database though I have been reading the thread.
    My earlier comment about Steve deserving a medal was because I couldn't make sense of what you were describing so decided not to get involved.
    I was very impressed that Steve did seem to understand it
    He certainly deserves lots of rep points
    Colin (Mendip Data Systems), Website, email

    If this has helped, please click the star button and leave a comment

  5. #50
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    7,459
    Quote Originally Posted by Bob M View Post
    When I ran the program I got a runtime error: File sharing lock count exceeded
    Yes, I had the same error. I thought it was because my confuser had a meltdown and I did a reinstall of Win7 and all programs. I did edit the code - turns out it has something to do with editing the recordset T1 inside the loop.

    I change the T1.update/update code to an update query and .... no more file lock errors!


    This is my understanding of what you want at this point:

    1) Each record in Table1 is compared to every record in Table2.
    2) If a record in Table2 is similar to the current record in Table1, update the field "Table2ID_FK" in the current record in Table1 with the PK value of the record that is similar in Table2.
    3) If there is NOT a record in Table2 that is similar to the current record in Table1, add the T1 record values to a NEW record in Table2 and update the field "Table2ID_FK" in the current record in Table1 with the PK value of the NEW record in Table2.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 03-07-2017, 04:02 PM
  2. Replies: 0
    Last Post: 03-12-2013, 01:51 PM
  3. writing data from recordset to table
    By akrylik in forum Access
    Replies: 5
    Last Post: 05-23-2012, 04:48 PM
  4. Replies: 3
    Last Post: 07-13-2011, 07:01 AM
  5. Writing data from textbox to table
    By mll in forum Forms
    Replies: 4
    Last Post: 03-10-2010, 04:10 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
  •  
Tech Forums: Microsoft Office Forums