Excute multiple delete and insert statements in same query

Posted on 2007-10-08
Last Modified: 2013-11-27
Is there a way to execute multiple delete or insert statements in an Access query? Think of this as an equivalent to a sql server stored procedure.

I have a database in sql server that is used by a windows app. I want to package the database with the windows application installation, so I exported it to Access. The problem is that I have several stored procedures, the functionality of which I need to reproduce. Two of the sps have multiple delete and insert statements I want to replicate in Access; hence, the question above.


P.S. I realize I could simply execute each delete and insert statement from the windows app, but I would like to know if there is a better way.  
Question by:Feyo
    LVL 119

    Expert Comment

    by:Rey Obrero
    <Is there a way to execute multiple delete or insert statements in an Access query>
    unfortunately no, you have to call the query separately.

    LVL 92

    Expert Comment

    by:Patrick Matthews
    Hello Feyo,

    What you can do is create a VBA sub (or less preferably, and Access macro) that will run your
    deletes/inserts for you...


    LVL 65

    Accepted Solution

    >Is there a way to execute multiple delete or insert statements in an Access query?

    Just like a SP, you'll have to bundle multiple queries within a transaction. Unline SQL, if code throws an error, you can set an error trap that will work throughout the funciton/sub, instead of doing IF @@ERROR<> 0 after every query.

    {using ADO}

    On error goto eh

    Dim cn as ADODB.Connection
    Set cn = Whatever

    With cn
       .Execute "Query #1"
       .Execute "Query #2"
       .Execute "You get the idea"

    End With

    'If code makes it to here, then no errors

      'Do your housekeeping here, then bail.
      Set cn = Nothing
      exit sub

      'An error occured.
      msgbox "An error occured: " & err.number & ", " & err.Description, vbOkOnly    '<-- replace this with a real error handler
      goto ex

    LVL 23

    Expert Comment

    by:Ashish Patel
    Try concanating every statement with a semi colon (;) and then execute. I dont know if that works, but should do fine.
    LVL 4

    Author Comment

    Doesn't seem to work in an access query, or did you mean from the window app?
    LVL 23

    Expert Comment

    by:Ashish Patel
    From the window app, try creating your insert or delete statements with ";" in between two statements and then execute them.
    LVL 4

    Author Comment

    asvforce - doesn't work, thanks for trying; jimhorn - that's what I figured. Thanks.
    LVL 4

    Author Comment

    Something is strange with the responses; they seem to be popping up after they should. So, I missed a bunch of responses at the beginning. I'll try to split up the points accordingly. Thanks for the responses.
    LVL 65

    Expert Comment

    by:Jim Horn
    >Something is strange with the responses; they seem to be popping up after they should.
    EE has occasional problems with email notifications of comments being sent significantally after the comment is made.  They're aware of it.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now