locked
update Sql Server RRS feed

  • Question

  • Hello everybody,
    I would like to know which is the best method to update the Sql Server structure using Ms Access.
    In practice, create tables, delete them, create views, procedures, triggers, insert fields, ...

    I tried with pass_trought queries but with few results.
    Some commands are not executed, or there is no confirmation of what has been done.

    Marco Dell'Oca


    Friday, July 3, 2020 4:47 PM

Answers

  • One passthrough query can execute one batch of sql statements.

    There is no "best" way. The way I do it is to create an in-memory querydef  using db.CreateQueryDef(""), set the Connect and SQL properties, and Execute it with dbFailOnError or dbSeeChanges.

    Updating the BE that way is something I am very familiar with: one of my customers has a commercial product on the market and by inspecting existing version numbers we know what code to run to rev the database from vX to vY. Not a trivial matter, but once setup correctly it becomes simple and repetitive.

    In this case the app supports both Access and SQL backend, so is a bit more challenging. Some information about my NADD 2018 presentation about that topic is available here


    -Tom. Microsoft Access MVP


    Saturday, July 4, 2020 3:46 PM

All replies

  • Passthrough queries are indeed the best way, but you have to be well-versed with T-SQL so you can for example use THROW to throw an error when a DDL statement fails. It rarely is a single statement; more likely several of them.

    -Tom. Microsoft Access MVP

    Friday, July 3, 2020 5:34 PM
  • Hi Tom,

    So you confirm that pass-through queries are the best way to update Sql Server.
    Practically in pass-through queries it is possible to insert all the commands that can be inserted in a SQLQuery MSSMS? Quite right?
    Now, what is the best way to run a pass-through query in MS Access?
    Create a query and run it with the DoCmd.OpenQuery command
    or
    create a command and run it as ADO?

    I would like to create a form which, recalled after logging in to my back-end, can update the database by entering the necessary changes included between the date of the last compilation of the back-end and the date of the last update of the database.
    What do you think about it?
    It is feasible?

    Thank you so much

    Marco Dell'Oca

    Saturday, July 4, 2020 5:42 AM
  • One passthrough query can execute one batch of sql statements.

    There is no "best" way. The way I do it is to create an in-memory querydef  using db.CreateQueryDef(""), set the Connect and SQL properties, and Execute it with dbFailOnError or dbSeeChanges.

    Updating the BE that way is something I am very familiar with: one of my customers has a commercial product on the market and by inspecting existing version numbers we know what code to run to rev the database from vX to vY. Not a trivial matter, but once setup correctly it becomes simple and repetitive.

    In this case the app supports both Access and SQL backend, so is a bit more challenging. Some information about my NADD 2018 presentation about that topic is available here


    -Tom. Microsoft Access MVP


    Saturday, July 4, 2020 3:46 PM
  • Hello Tom,
    thank you so much for all the information.
    I have already started to develop a program that updates the BE.
    I have noticed that
    BEGIN TRY
    END TRY
    BEGIN CATCH
        THROW;
    END CATCH
    is not compatible with Provedure and Views.
    But this is not a problem, I will omit to use it with procedures and views.

    Thanks so much.

    Marco Dell'Oca
    Sunday, July 5, 2020 9:22 AM