Excute multiple delete and insert statements in same query

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.

Thanks.

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.  
LVL 4
FeyoAsked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Is there a way to execute multiple delete or insert statements in an Access query?
No.

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
   .BeginTrans
   
   .Execute "Query #1"
   .Execute "Query #2"
   .Execute "You get the idea"

End With

'If code makes it to here, then no errors
cn.CommitTrans

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

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

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

0
 
Patrick MatthewsCommented:
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...

Regards,

Patrick
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Ashish PatelCommented:
Try concanating every statement with a semi colon (;) and then execute. I dont know if that works, but should do fine.
0
 
FeyoAuthor Commented:
Doesn't seem to work in an access query, or did you mean from the window app?
0
 
Ashish PatelCommented:
From the window app, try creating your insert or delete statements with ";" in between two statements and then execute them.
0
 
FeyoAuthor Commented:
asvforce - doesn't work, thanks for trying; jimhorn - that's what I figured. Thanks.
0
 
FeyoAuthor Commented:
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.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.