running several lines

  • Thread starter Thread starter Mr. x
  • Start date Start date
M

Mr. x

How can I write a one sql statement, that has several insert.

i.e :

insert into mytable(col1, col2, col3) values(1,2,3);
insert into mytable(col1, col2, col3) values(2,3, 4);

.... but the following doesn't work (error : character found after sql
statement).

Thanks :)
 
Mr. x said:
How can I write a one sql statement, that has several insert.

i.e :

insert into mytable(col1, col2, col3) values(1,2,3);
insert into mytable(col1, col2, col3) values(2,3, 4);

... but the following doesn't work (error : character found after sql
statement).
Click to expand...

Access queries can only execute a single SQL statement. You could execute this in
code and it would look almost like what you have now.

CurrentDB.Execute "insert into mytable(col1, col2, col3) values(1,2,3)",dbFailOnError

CurrentDB.Execute "insert into mytable(col1, col2, col3) values(2,3,4)",dbFailOnError
 
Isn't any transaction I can start and commit/rollback in access ?

How can I do that ? - If so, then I need an example, please.

Thanks :)
 
Sorry, you can't. Jet doesn't support that. You need to use code to execute
the several SQL statements one after the other.
 
Mr. x said:
Isn't any transaction I can start and commit/rollback in access ?

How can I do that ? - If so, then I need an example, please.
Click to expand...

See help file for...
BeginTrans, CommitTrans, Rollback Methods
 
yes, you can go:

Public Sub MyTest

BeginTrans


CurrentDB.Execute "insert into mytable(col1, col2, col3) " & _
" values (1,2,3)",dbFailOnError

CurrentDB.Execute "insert into mytable(col1, col2, col3) " & _
" values(2,3,4)",dbFailOnError

If MsgBox("Do you want to actaully do these updates ? ", _
vbYesNo) = vbYes Then

CommitTrans

Else
Rollback

End If

end sub

I used the line continuation character above. You can also just as well used
saved queries, and used the same as above.


BeginTrans


CurrentDB.Execute "query1"

CurrentDB.Execute "query2"

If MsgBox("Do you want to actaully do these updates ? ", _
vbYesNo) = vbYes Then

CommitTrans
Else
Rollback

End If


Anyone who has played around with the basic programming language could
certainly in a few minutes write some code to read a text file, and then
use the above idea of currentdb.Execute to run each line of the
text file. The result would be that you can run sql scripts.

You can also whack ctrl-g, and type the sql statements directly into the
debug window interactive at a command prompt also using

docmd.runSql "your sql"

The above is automatically wrapped in a transaction for you. Or, you can use

currentdb.Execute "your sql"
 
Thanks ...

I am using the asp.net (ado.net) for that, so there is no visual basic -
just an sql statement,
and I think that beginTrans is not an sql statement, becuase in access
database it is unknown.
I would be glade if you can give me an example code for ado.net by using
transaction (if it possible).

Thanks :)
 
Mr. x said:
Thanks ...

I am using the asp.net (ado.net) for that, so there is no visual basic -
just an sql statement,
and I think that beginTrans is not an sql statement, becuase in access
database it is unknown.
Click to expand...


Actaully, beginTrans is a JET database engine command, and it is supproted.

conn = your ADO conneiton object....


conn.Execute "BEGIN TRANSACTION"
conn.Execute "DELETE Tasks.[Emp ID], Tasks.*" & _
"From Tasks" & " WHERE Tasks.[Emp ID])= '5';"

conn.CommitTrans

I not sure how ADO actually supports transactions, because ADO is only a
connection layer to the database like JET, Oracle, etc. Some of those
database engines may not have transactions.. However, the above should work
for ADO.
 
ASP.NET supports VB.NET, or C#, or any other language that targets the .NET
Common Language Runtime (CLR). There is example code showing how to use
ADO.NET transactions in VB.NET and C# in the topic 'Performing a Transaction
Using ADO.NET' in the .NET Framework SDK documentation. The .NET Framework
SDK can be downloaded at http://msdn.microsoft.com/downloads

You might receive a more detailed response in an ASP.NET newsgroup.
 
Back
Top