?
Solved

Excute multiple delete and insert statements in same query

Posted on 2007-10-08
9
Medium Priority
?
1,446 Views
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.

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.  
0
Comment
Question by:Feyo
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20034085
<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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 20034108
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
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 20034112
>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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 23

Expert Comment

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

Author Comment

by:Feyo
ID: 20034149
Doesn't seem to work in an access query, or did you mean from the window app?
0
 
LVL 23

Expert Comment

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

Author Comment

by:Feyo
ID: 20034271
asvforce - doesn't work, thanks for trying; jimhorn - that's what I figured. Thanks.
0
 
LVL 4

Author Comment

by:Feyo
ID: 20034288
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 20034309
>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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

612 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