Ciao, dato che ho anche io bisogno per il mio database di avviare il codice SQL con una macro, ho provato a chiedere aiuto anche sul forum inglese, ti incollo la risposta che mi hanno appena dato se tu ci capisci qualcosa di più.....
I do not write your macro but I hope the instruction here are clear. I think the only thing you have to do is place a lot of doubles quote in your SQL command. When your SQL command does not work then it does also not work in the macro. Do not mix question about the working of SQL commands with MACRO commands. Be also aware BASIC does not understand SQL and SQL does not understand BASIC. In other words BASIC and SQL are complete different worlds.
When you need to execute SQL code inside a macro then be sure that the SQL code is working. You can test the SQL code in the SQL tool of OOo. Making the macro and the SQL code at the same time more difficult. Starters do not know where is the problems.
After you have test the query inside the SQL you can start with building the macro
There you want start the macro with a button in a form it is more easy to execute the SQL command. With a button in a form what must have a connection to the database it is easy to execute a SQL command all you need is.
oForm.Activeconnection.CreateStatement.execute(sSqlStatement)
Modifying the Original SQL command to the command what is working in a BASIC macro.
When you have surround the variables in the SQL commandwith double quotes then they must be surround in macro with double double quotes
working is SQL tool
select "Name" from "Table"
Becomes in the Macro
"select ""Name"" from ""Table"""
When you have a long SQL line then it is better to split it over several BASIC lines. This can be done when you split the SQL command in several strings and add them with the Ampersamp(&). Each basic line must be end with an underscore. When you have done this do not trust what you have done but test it with a print statement. When you see the same result as what you have done with the test in the SQL tool then it is good
example
Codice: Seleziona tutto
Select all Expand view
sStatementorg="select ""Name""" &_
" from ""Table"""
print sStatementorg
For executing SQL commands there are several methods.
The interface XStatement does have three different methods
executeQuery
executeUpdate
execute
I think the method execute is the best one for what you need.
I hope this detailed instruction does help you and does also help other people.
Codice: Seleziona tutto
REM ***** BASIC *****
option explicit
Sub ExecuteSQLStatement(oEvent as object)
dim oButton
dim oForm
dim oConnection
dim oStatement
dim sStatementorg 'This is the statement how it is working in the SQL Tool
dim sSTatementWorking ' This is the statement how it is working
sStatementorg="Place here the command"
if sStatementorg="Place here the command" then
print "Add your own SQL command"
end
end if
'Example code for making one code line over more lines
'sStatementorg="select ""Name""" &_
' " from ""Table"""
'print sStatementorg
oButton=oEvent.source.model
oForm=oButton.parent ' Go from the button to the form
oConnection=oForm.Activeconnection
select case isnull(oConnection)
case true
print "There is no Active connection to the database so we cannot work"
print "We end"
end
case False
oStatement=oConnection.createStatement
oStatement.execute(sStatementorg)
end select
End Sub
Fammi sapere se riesci.
Ciao
