Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    23

    Arrow Unanswered: Access update query using data from a linked SQL table

    We have an access database that has a table linked to a SQL Server table. We have another table in Access that we want to add records to based on the CustomerID of the linked SQL table.

    We have a query bringing in the data from linked SQL table and the Access table. We want to update the access table but every time we go to enter text, it wil not let us.

    It seems that because it is pulling data from the SQL Server, it wants to write back to it and this is preventing us from writing to the Access side of the query.

    We don't want to modify any data from the SQL side, we just need to show and relate data from the linked table to the Access table we want to add data to.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2002
    Location
    Dublin, OH
    Posts
    10
    Paul,

    I'm not sure if this is your issue or not, but I've run into instances where I get a message that says something like "Not an updatable query". I have not been able to identify a cause and this has even happened to me after an update query has worked fine for months.

    The way I get around it is to make it a two-step process. Change the update query to a make table query that dumps the output to a temporary table. Then run an update query using the temproary table instead of the SQL server table.

    If you do it in a macro or code, you can then delete the temp table.

    I hope this helps.
    Patrick Mildenberg

  3. #3
    Join Date
    Feb 2002
    Location
    Hong Kong
    Posts
    3
    This one is interesting as this will help me too in my current work. But can you help with the code or the macro too ?
    Thanks
    N.S.Rao

  4. #4
    Join Date
    Feb 2002
    Posts
    23
    The problem is that if we need to use a macro, it will not work with our web based interface. We need to be able to update the query with ASP.

    I appreciate your help.

  5. #5
    Join Date
    Feb 2002
    Location
    Dublin, OH
    Posts
    10
    Paul,

    Unfortunately, I don't know anything about ASP, but I assume it will allow you to launch queries through code. You don't have to do it in a macro. I was just saying that if you did use a macro, you could add a step that would delete the temporary table when you finished.

    What you can do is create one make-table query that retrieves the data you want and puts it into a table; call it tblNewData.

    Then create an update- or append-query that takes the data from tblNewData and updates or appends (whichever is appropriate for your operation) the data in your existing table.

    In VBA, you would write a procedure that runs the first query and then the second and then deletes the temporary table.

    ---------------------------------------------------------------
    Sub UpdateDataFromSQL

    'Turn off "You are about to . . . " and "Do you really want to . . ." warnings
    Docmd.SetWarnings False

    'Run query to retrieve data
    Docmd.Openquery "qryMkTblGetSQLData"

    'Run query to update data in your existing table
    Docmd.Openquery "qryUpdtExistingData"

    'Delete temporary table
    Docmd.Deleteobject acTable, "tblNewData"

    'Turn warnings back on
    Docmd.SetWarnings True

    End Sub
    ---------------------------------------------------------------

    I hope this helps. Let me know if it doesn't.
    Patrick Mildenberg

  6. #6
    Join Date
    Feb 2002
    Posts
    23
    Creating a Make Table query has helped things. How can I create a macro to run once every day to update the data?

  7. #7
    Join Date
    Feb 2002
    Location
    Dublin, OH
    Posts
    10
    Go to the Macros tab and click New.

    Each row is one step in the macro. In action, select what you want to do from the drop-down list and enter parameters in the fields at the bottom of the screen. (F6 switches between the upper and lower panes)

    I would start with SetWarnings as my first action and enter No in the Warnings On field at the bottom. If you don't do this, you will get messages for each action query in the macro (i.e. "This query will change data in a table, do you want to continue?", "About to add x records to the table")

    Next, enter the steps you want the macro to perform. To run the make table and append queries you have created, you will want to select OpenQuery in the Action field at the top and the name of the query in the Query Name field at the bottom.

    Finally, enter another SetWarnings step and set it to Yes. If you don't do this, other warning/error messages (that you should want to see) will be surpressed and if you try to do something that would normally cause an error, it will seem to just ignore you and not work. (frustrating until you figure it out)
    Patrick Mildenberg

Posting Permissions

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