Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    28

    Unanswered: Problem using a "Make Table" query

    I have a query with a Union in it. (qryBase). (Access 2003).

    As I want my Form (frmBase) to be able to update one of the fields, I have been advised that the best way to achieve this is to write a “make table” query, (qry_mk_tbl) accessing the original query, and then to point my Form to this new table. (tblqry).

    I then wrote a macro (MacroRunMakeTable), with “OpenQuery” as an Action,
    and in the “On Open” Event of the Form, I set the value to
    “MacroRunMakeTable”.

    But when the Form tries to load, I get “The database engine could not lock table ‘tblqry’ because it is in use by another person or process”.
    When I click on OK, I get the “Action Failed” box, referring to the macro.
    I get these messages when nothing else is running on my PC.

    There as a post for a similar scenario in the Forum, but I cannot find any resolutions.

    Any help would be most grateful.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you won't be able to lock the table if you are using that table in a the same form.

    can you explaion in english what you are trying to do. why someone has recommended that you create a table int he first place to edit/change one column. I'm suspicious of the need to use a make table query (unelss you need to export that data to another process
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    28

    Problem using a "Make Table" query

    Hi Healdem
    This is a simplified version of my original query :
    SELECT tblA.Report, tblB.Style, tblA.Source
    FROM tblA LEFT JOIN tblB
    ON tblA.Report = tblB.Report
    UNION
    SELECT tblB.Report, tblB.Style, "Not Set in tblA "
    FROM tblB LEFT JOIN TblA
    ON tblB.Report = TblA.Report
    where tblB.Report not in (select TblA.Report from TblA)

    I want my Form to be able to update tblB.Style from a dropdown,
    but have been advised that as my Query is a UNION Query,
    it is a non-updatable recordset, as can be demonstrated
    in the corresponding datasheet view,
    where the record navigation buttons allow
    browsing to first, last, previous & next, but not "new".

    Hence the need for the intermediate table to return an updateable recordset.
    Many thanks for your help.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    providing you have the PK of the row you need to update I'd suggest you run some SQL to update trhe value of the relevant column(s) by executing some code in the form (or control)'s before update, or possibly after update event.
    docmd****nsql("update MyTable set mynumericcolumn=666, myStringColumn="666" where acolumns priamarykey=blah-di-blah")
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    28
    Hi Healdem.
    I ended up writing a new form (frmLaunch) which is initiated at Startup.
    This form is not linked to any of my tables or queries,
    but just contains an 'On Open' Event Procedure which calls 'MacroRunMakeTable'.
    This macro now contains the following :
    RunSQL to delete from the existing 'tblqry'
    OpenQuery to run 'qry_mk_tbl' which creates a new 'tblqry'
    Close to close 'qry_mk_tbl'
    OpenForm to open 'frmBase', &
    Close to close 'frmLaunch'

    It works great, as I can now update the required field.
    What a palaver !

Posting Permissions

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