• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

Access - Multiple SQl statements

Is it possible to execute  Multiple SQl statements from .NET code using the OLE DB objects?  if I try using two INSERT statement, each ending with a semicolon, I get an error - "Characters found after end of SQL statement. "
0
HLRosenberger
Asked:
HLRosenberger
  • 6
  • 3
  • 3
1 Solution
 
Antonio Salva RipollCommented:
Hi HLRosenberger.

In Access, SQL Server, Oracle.... the SQL action querys must be executed one after other. So, you must call the first "Insert ..." and later the second "Insert...".

Best regards.

Antonio.
0
 
Éric MoreauSenior .Net ConsultantCommented:
can we see your query? You normally don't have to put the ; as the syntax is quite rigid.
0
 
HLRosenbergerAuthor Commented:
It's just two inserts.  I do this using SQL server all the time.

"INSERT INTO tax_status (id, code, taxable) VALUES(1234, 'DDDD', 0); " & _
                                                                "INSERT INTO tax_status (id, code, taxable) VALUES(1235, 'ffff', 0);")
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Éric MoreauSenior .Net ConsultantCommented:
have you tried removing the ; ?
0
 
HLRosenbergerAuthor Commented:
if I do that, I get "Missing semicolon at end of SQL statement".
0
 
HLRosenbergerAuthor Commented:
bottom line - I would like to do a "batch" update to an access database.   Is that possible?  Like execute multiple inserts/updates in one action?
0
 
HLRosenbergerAuthor Commented:
Does Access support anything like Stored Procs?   Can I write a macro or VBA script that I can execute from .NET code?
0
 
Antonio Salva RipollCommented:
Hi @HLRosenberger.

To the question if Access supports stored procedures, the answer is "no in the same way as SQL Server".

Although you can use VBA to store and execute SQL statements, you must put them one after another.

This is the VBA code to execute your Inserts:
Public Sub RunMyInserts()
    CurrentDb.Execute "INSERT INTO tax_status (id, code, taxable) VALUES(1234, 'DDDD', 0);"
    CurrentDb.Execute "INSERT INTO tax_status (id, code, taxable) VALUES(1235, 'ffff', 0);"
End Sub

Open in new window


Also, if you can pass parameters to the procedure, you can do a "batch"
Sub TestParams()
    
    RunMyInserts "1234, 'DDDD', 0"
    RunMyInserts "1235, 'ffff', 0"

End Sub

Public Sub RunMyInserts(MyParams As String)
    CurrentDb.Execute "INSERT INTO tax_status (id, code, taxable) VALUES(" & MyParams & ");"
End Sub

Open in new window


Best regards.

Antonio (Barcelona, Spain)
0
 
Éric MoreauSenior .Net ConsultantCommented:
from what I have read elsewhere, it doesn't seem to be possible but maybe there are some workarounds for example:
INSERT INTO tax_status (id, code, taxable) 
select * from (
   select 1234, 'DDDD', 0
   union all 
   select 1235, 'ffff', 0
);

Open in new window

0
 
Antonio Salva RipollCommented:
@Éric, great solution.

As I always said, "the best solutions are always the simplest ones".

My sincere congratulations.

Antonio.
0
 
HLRosenbergerAuthor Commented:
0
 
HLRosenbergerAuthor Commented:
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now