Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2009
    Posts
    8

    Unanswered: Access 2003 Disconnected recordset

    Good morning all.

    I have a form and a subform. Currently there are three criteria on the main form a user would select. The subform is bound to a query (off the master table) and populated on a change event of third criteria.

    I test to see if there is a username on any of the records and display a message to lock a user out of those records and subsequently, there is a command button to put the time and date stamps on each record when the user is done with a 'batch'.

    This is causing random record locking and I believe each user may be locking themselves out when two 'connections' are created for the same records.

    I was researching disconnected recordsets in the web and I was wondering if that would work for us.

    This is what I am trying to accomplish:

    I would like to leave the subform unbound, search a 'batch' of records for a username and if no username, populate the subform. While users update the subform, there is no remote connection to the table and it isn't updated until the user clicks on the DateTimeStamp button and an update query will run.

    I have mostly used DAO, but am not opposed to using ADO. The DB is broken up in a FE (Desktop) / BE (LAN) configuation.

    Any suggestions?

    WHEELS

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What I would do is probably not the most elegant solution but one of the easiest:

    1. Create a local table and link your subform to it.
    2. Populate the local table with the needed data form the master table, using a query. If necessary lock the lines of data you are selecting into the master table.
    3. Do whatever you have to do with the data. Your subform is linked to a local table holding a copy of the data, so it's not linked to the original data of the master table.
    4. When processing data into the local table is finished send back those data to the master table.

    I hope this helps.

    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    8
    Hi Sinndho.

    I can create the local table (MT query from master table).

    My issue is when I link the subform RecordSource to the table, how do I clear it out each time without deleting it and recreating it?

    If I use an append query it just adds to the table instead of writing over those records.

    WHEELS

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    After sending back the data from the local table to the master table, you can empty the local table with a command like:

    Code:
    Currentdb.Execute "DELETE * FROM <local table>"
    or you can create a query that does the same and run this query.

    Have a nice day!

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or you can use a MAKE TABLE query, which will destroy and re-create the temporary table every time it is run.
    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

  6. #6
    Join Date
    Feb 2009
    Posts
    8
    I have posted redgarding this issue in the past, but have not had a viable resolution as of yet.

    I have a table with approx. 25 fields. Most of the fields are text and set with a 255 field length. The field size if much too large for most of the data.

    I believe we are experiencing some page locking and I was wondering if this might cause a row to extend to a different page.

    Also, if you had a Form which gathered data for a query (based on the master table) and a subform that would display a 'batch' of records (1 - 50) based on the criteria on the main form, allow the users to edit the data, and then click on a command button to save and date stamp those records in the table, what would be the most efficient method of doing this?

    WheelsGuy

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I was wondering if this might cause a row to extend to a different page.
    I've never seen such behaviour, so I am tempted to just say "no" to that question.

    what would be the most efficient method of doing this?
    I'm not sure there is an answer that you'll like. If you need to hold some data until you hit save, then you that data must be stored in a temporary table which is then copied to the live table when save is hit.
    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

  8. #8
    Join Date
    Feb 2009
    Posts
    8
    Thank you StarTrekker.

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    'Trekker: i'm tempted in the opposite direction.
    page lock in A used to be over a 4kB "page" (don't use locks these days so i might be out of date) ...if true, it is completely reasonable that a 4kB page might wrap over more than one record.

    why not try record lock just for the fun of it?

    why not try no locks - issue SQL along the lines of
    UPDATE thisTable SET blah = blahNew WHERE blah = blahOld ...works a treat and does not rely on locks.

    izy
    currently using SS 2008R2

Posting Permissions

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