Re: [libreoffice-users] Basic Macros with Base
Hi Andrew, database of Harvey is MariaDB. So it has been quoted a littel bit different: SELECT `Name` FROM `Table` This kind of qouting could be used in Basic macros without any problems: stSql = "SELECT `Name` FROM `Table`" If there are double qoutes like in internal HSQLDB it looks a littel bit different: SELECT "Name" FROM "Table" and in Basic: stSql = "SELECT ""Name"" FROM ""Table""" Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
I think in my AndrewBase.odt document that I previously referenced, that I
wrote a routine to put quotes around things so that they would appear inside
the strings but I would have to go back and double check. But yes, I think you
need to do that.
Get BlueMail for Android
On Jan 26, 2023, 3:21 AM, at 3:21 AM, Harvey Nimmo wrote:
>Hi Robert,
>
>the thought occurs to me that the existing quotes within the SQL
>statements may need their own treatment. The statements that work under
>the LOBase Tools>SQL... function have different type of single
>quotemarks for the column names (e.g. containing two separate words) or
>the text to be set in the columns. I have assumed that the macro (SQL)
>string variables just need to be enclosed in double quotemarks. Is this
>right?
>
>Cheers
>Harvey
>
>
>On Wed, 2023-01-25 at 21:51 +0100, Robert Großkopf wrote:
>> Hi Harvey,
>> >
>> > how would this work for me, where I only want to create a SQL
>> > statement
>> > as a string and execute it.
>>
>> Mcro works with SQL-code, which has been saved in a field of a table.
>> Field in the table is called "SQL-Code". Table is datasource of a
>> form.
>> A button in this form will start the code.
>>
>> First needed object is executing a button in the form to start the
>> procedure.
>>
>> If you will save all code directly in a macro you will need
>> → a connection to the database
>> → SQL code, which has been maskes with double doublequotes for
>> fieldnames and tablenames
>> → var for creating a statement
>> → execute the sql-code in this created statement
>>
>> At which line of the macro the code stops for you?
>> > >
>> > > Here a code from German Base Handbuch:
>> > >
>> > > SUB ChangeData(oEvent AS OBJECT)
>> > > DIM oConnection AS OBJECT
>> > > DIM oForm AS OBJECT
>> > > DIM stSql AS STRING
>> > > DIM oSql_Statement AS OBJECT
>> > > DIM inValue AS INTEGER
>> > > oForm = oEvent.Source.Model.Parent
>> > > oConnection = oForm.activeConnection()
>> > > stSQL = oForm.getString(oForm.findColumn("SQL-Code"))
>> > > inValue = MsgBox("Should SQL-Code" & CHR(13) & stSQL & CHR(13)
>> > > &
>> > > "be executed?", 20, "Execute SQL-Code")
>> > > IF inValue = 6 THEN
>> > > oSQL_Statement = oConnection.createStatement()
>> > > oSQL_Statement.execute(stSql)
>> > > END IF
>> > > END SUB
>> > >
>> > > SQL-Code is part of a table. Field in the table is "SQL-Code".
>> > > Table
>> > > is
>> > > datasource of a form. A button in this form will be enough to
>> > > execute
>> > > the macro. You coud update, insert or delete rows by command
>> > > "execute".
>>
>> Regards
>>
>> Robert
>> --
>> Homepage: https://www.familiegrosskopf.de/robert
>>
>>
>
>
>--
>To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
>Problems?
>https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
>Posting guidelines + more:
>https://wiki.documentfoundation.org/Netiquette
>List archive: https://listarchives.libreoffice.org/global/users/
>Privacy Policy: https://www.documentfoundation.org/privacy
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Hi Harvey, would be better to post such a SQL-code you execute through Tools → SQL. This code should be the same as safed in the database for executing later through the form. I will send you an example with internal HSQLDB per private mail. Regards Robert -- Homepage: https://www.familiegrosskopf.de/robert -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Hi Robert,
the thought occurs to me that the existing quotes within the SQL
statements may need their own treatment. The statements that work under
the LOBase Tools>SQL... function have different type of single
quotemarks for the column names (e.g. containing two separate words) or
the text to be set in the columns. I have assumed that the macro (SQL)
string variables just need to be enclosed in double quotemarks. Is this
right?
Cheers
Harvey
On Wed, 2023-01-25 at 21:51 +0100, Robert Großkopf wrote:
> Hi Harvey,
> >
> > how would this work for me, where I only want to create a SQL
> > statement
> > as a string and execute it.
>
> Mcro works with SQL-code, which has been saved in a field of a table.
> Field in the table is called "SQL-Code". Table is datasource of a
> form.
> A button in this form will start the code.
>
> First needed object is executing a button in the form to start the
> procedure.
>
> If you will save all code directly in a macro you will need
> → a connection to the database
> → SQL code, which has been maskes with double doublequotes for
> fieldnames and tablenames
> → var for creating a statement
> → execute the sql-code in this created statement
>
> At which line of the macro the code stops for you?
> > >
> > > Here a code from German Base Handbuch:
> > >
> > > SUB ChangeData(oEvent AS OBJECT)
> > > DIM oConnection AS OBJECT
> > > DIM oForm AS OBJECT
> > > DIM stSql AS STRING
> > > DIM oSql_Statement AS OBJECT
> > > DIM inValue AS INTEGER
> > > oForm = oEvent.Source.Model.Parent
> > > oConnection = oForm.activeConnection()
> > > stSQL = oForm.getString(oForm.findColumn("SQL-Code"))
> > > inValue = MsgBox("Should SQL-Code" & CHR(13) & stSQL & CHR(13)
> > > &
> > > "be executed?", 20, "Execute SQL-Code")
> > > IF inValue = 6 THEN
> > > oSQL_Statement = oConnection.createStatement()
> > > oSQL_Statement.execute(stSql)
> > > END IF
> > > END SUB
> > >
> > > SQL-Code is part of a table. Field in the table is "SQL-Code".
> > > Table
> > > is
> > > datasource of a form. A button in this form will be enough to
> > > execute
> > > the macro. You coud update, insert or delete rows by command
> > > "execute".
>
> Regards
>
> Robert
> --
> Homepage: https://www.familiegrosskopf.de/robert
>
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Hi Harvey,
how would this work for me, where I only want to create a SQL statement
as a string and execute it.
Mcro works with SQL-code, which has been saved in a field of a table.
Field in the table is called "SQL-Code". Table is datasource of a form.
A button in this form will start the code.
First needed object is executing a button in the form to start the
procedure.
If you will save all code directly in a macro you will need
→ a connection to the database
→ SQL code, which has been maskes with double doublequotes for
fieldnames and tablenames
→ var for creating a statement
→ execute the sql-code in this created statement
At which line of the macro the code stops for you?
Here a code from German Base Handbuch:
SUB ChangeData(oEvent AS OBJECT)
DIM oConnection AS OBJECT
DIM oForm AS OBJECT
DIM stSql AS STRING
DIM oSql_Statement AS OBJECT
DIM inValue AS INTEGER
oForm = oEvent.Source.Model.Parent
oConnection = oForm.activeConnection()
stSQL = oForm.getString(oForm.findColumn("SQL-Code"))
inValue = MsgBox("Should SQL-Code" & CHR(13) & stSQL & CHR(13) &
"be executed?", 20, "Execute SQL-Code")
IF inValue = 6 THEN
oSQL_Statement = oConnection.createStatement()
oSQL_Statement.execute(stSql)
END IF
END SUB
SQL-Code is part of a table. Field in the table is "SQL-Code". Table
is
datasource of a form. A button in this form will be enough to execute
the macro. You coud update, insert or delete rows by command
"execute".
Regards
Robert
--
Homepage: https://www.familiegrosskopf.de/robert
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Hi Robert,
how would this work for me, where I only want to create a SQL statement
as a string and execute it. Can it be that the string is not recognised
as SQL? (In my MSAccess days it worked). Here the run-time error
indicates that the "Object variable is not set" although it is valid
SQL text and works 'by hand'.
Cheers
Harvey
On Wed, 2023-01-25 at 07:43 +0100, Robert Großkopf wrote:
> Hi Harvey,
>
> why do you want to use Access2Base?
>
> Here a code from German Base Handbuch:
>
> SUB ChangeData(oEvent AS OBJECT)
> DIM oConnection AS OBJECT
> DIM oForm AS OBJECT
> DIM stSql AS STRING
> DIM oSql_Statement AS OBJECT
> DIM inValue AS INTEGER
> oForm = oEvent.Source.Model.Parent
> oConnection = oForm.activeConnection()
> stSQL = oForm.getString(oForm.findColumn("SQL-Code"))
> inValue = MsgBox("Should SQL-Code" & CHR(13) & stSQL & CHR(13) &
> "be executed?", 20, "Execute SQL-Code")
> IF inValue = 6 THEN
> oSQL_Statement = oConnection.createStatement()
> oSQL_Statement.execute(stSql)
> END IF
> END SUB
>
> SQL-Code is part of a table. Field in the table is "SQL-Code". Table
> is
> datasource of a form. A button in this form will be enough to execute
> the macro. You coud update, insert or delete rows by command
> "execute".
>
> Regards
>
> Robert
> --
> Homepage: https://www.familiegrosskopf.de/robert
>
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Hi Harvey,
why do you want to use Access2Base?
Here a code from German Base Handbuch:
SUB ChangeData(oEvent AS OBJECT)
DIM oConnection AS OBJECT
DIM oForm AS OBJECT
DIM stSql AS STRING
DIM oSql_Statement AS OBJECT
DIM inValue AS INTEGER
oForm = oEvent.Source.Model.Parent
oConnection = oForm.activeConnection()
stSQL = oForm.getString(oForm.findColumn("SQL-Code"))
inValue = MsgBox("Should SQL-Code" & CHR(13) & stSQL & CHR(13) &
"be executed?", 20, "Execute SQL-Code")
IF inValue = 6 THEN
oSQL_Statement = oConnection.createStatement()
oSQL_Statement.execute(stSql)
END IF
END SUB
SQL-Code is part of a table. Field in the table is "SQL-Code". Table is
datasource of a form. A button in this form will be enough to execute
the macro. You coud update, insert or delete rows by command "execute".
Regards
Robert
--
Homepage: https://www.familiegrosskopf.de/robert
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
You guys are terrific. Thanks for your help! The reason I went to the macros was because I was looking for a repository within LOBase for a number of SQL sequences (e.g. UPDATE tbl SET Col WHERE expr) that a would want to execute from time to time. A macro seemed to be the only possibility. What else could you suggest? (It's a rather trivial requirement actually and using a macro could become a sledgehammer to crack a nut. Collecting my SQL statements into a text file somewhere in the bowels of my machine to be called up with cut and paste struck me as a bit primitive and inelegant). On Tue, 2023-01-24 at 17:07 -0500, Andrew Pitonyak wrote: > > I have a guess since I was not previously aware of this method. > > There is precedence for LO adding support for Microsoft Office (MSO) > compatibility. I am guessing that this method was added for MSO > compatibility with MS Access (but that is just a guess). I would > assume, therefore, that it should act similarly to how the method > work in MS Access (again a guess). > > When I really want to know, I pull the code and then I go looking for > the method in the code to see what it does. > > Also, if it immediately registers as an error, then you might need to > turn on compatibility mode. > > Well, that is what I thought until I googled a bit > > This implies that the command is based on an extension rather than > directly supported by LO. > > https://extensions.libreoffice.org/en/extensions/show/access2base > https://extensions.openoffice.org/en/project/access2base-api-base-users > https://ask.libreoffice.org/t/syntax-form-controls-base/45282 > > but then this seems to imply otherwise > > https://books.libreoffice.org/en/BG72/BG7209-Macros.html > > Sorry, not much help :-( > > > On Tuesday, January 24, 2023 15:50 EST, Harvey Nimmo > wrote: > Thanks. Not sure I can understand how that should work...hmmm > > I based my syntax on the example here > > https://learn.microsoft.com/en-us/office/vba/api/access.docmd.runsql > > > > On Tue, 2023-01-24 at 20:39 +, Dave Howorth wrote: > > On Tue, 24 Jan 2023 20:32:18 +0100 > > Harvey Nimmo wrote: > > > > > Where can I find the syntax of the DoCmd.RunSQL command to be > > > used > > > with a LOBase Macro in Basic? > > > > I've never heard of the command, but google suggests > > > > https://docs.libreoffice.org/wizards/html/classaccess2base_1_1DoCmd.html#a3c1b223e5e4024f2a8b955b2df3b6f92 > > > > I've no idea whether it is correct/relevant. > > > > > My assumption was > > > X = "SQL string" > > > DoCmD.RunSQL X > > > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
I have a guess since I was not previously aware of this method. There is precedence for LO adding support for Microsoft Office (MSO) compatibility. I am guessing that this method was added for MSO compatibility with MS Access (but that is just a guess). I would assume, therefore, that it should act similarly to how the method work in MS Access (again a guess). When I really want to know, I pull the code and then I go looking for the method in the code to see what it does. Also, if it immediately registers as an error, then you might need to turn on compatibility mode. Well, that is what I thought until I googled a bit This implies that the command is based on an extension rather than directly supported by LO. https://extensions.libreoffice.org/en/extensions/show/access2base https://extensions.openoffice.org/en/project/access2base-api-base-users https://ask.libreoffice.org/t/syntax-form-controls-base/45282 but then this seems to imply otherwise https://books.libreoffice.org/en/BG72/BG7209-Macros.html Sorry, not much help :-( On Tuesday, January 24, 2023 15:50 EST, Harvey Nimmo wrote: Thanks. Not sure I can understand how that should work...hmmm I based my syntax on the example here https://learn.microsoft.com/en-us/office/vba/api/access.docmd.runsql On Tue, 2023-01-24 at 20:39 +, Dave Howorth wrote: > On Tue, 24 Jan 2023 20:32:18 +0100 > Harvey Nimmo wrote: > > > Where can I find the syntax of the DoCmd.RunSQL command to be used > > with a LOBase Macro in Basic? > > I've never heard of the command, but google suggests > > https://docs.libreoffice.org/wizards/html/classaccess2base_1_1DoCmd.html#a3c1b223e5e4024f2a8b955b2df3b6f92 > > I've no idea whether it is correct/relevant. > > > My assumption was > > X = "SQL string" > > DoCmD.RunSQL X -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
Thanks. Not sure I can understand how that should work...hmmm I based my syntax on the example here https://learn.microsoft.com/en-us/office/vba/api/access.docmd.runsql On Tue, 2023-01-24 at 20:39 +, Dave Howorth wrote: > On Tue, 24 Jan 2023 20:32:18 +0100 > Harvey Nimmo wrote: > > > Where can I find the syntax of the DoCmd.RunSQL command to be used > > with a LOBase Macro in Basic? > > I've never heard of the command, but google suggests > > https://docs.libreoffice.org/wizards/html/classaccess2base_1_1DoCmd.html#a3c1b223e5e4024f2a8b955b2df3b6f92 > > I've no idea whether it is correct/relevant. > > > My assumption was > > X = "SQL string" > > DoCmD.RunSQL X > -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
On Tue, 24 Jan 2023 20:32:18 +0100 Harvey Nimmo wrote: > Where can I find the syntax of the DoCmd.RunSQL command to be used > with a LOBase Macro in Basic? I've never heard of the command, but google suggests https://docs.libreoffice.org/wizards/html/classaccess2base_1_1DoCmd.html#a3c1b223e5e4024f2a8b955b2df3b6f92 I've no idea whether it is correct/relevant. > My assumption was > X = "SQL string" > DoCmD.RunSQL X -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
The only reference, I found to DoCmd in the LO Base Guide 7.3 is on
page 504 (Special commands), i.e. rather brief ("To name a few").
As the employed syntax compiles in LOBase without error, I assume it is
correct as described below, so far. So I'm looking for a better
explanation for the behaviour.
By the way, I am running
Version: 7.3.4.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 2; OS: Linux 5.14; UI render: default; VCL: gtk3
Locale: en-GB (en_GB.UTF-8); UI: en-GB
Calc: threaded
LO Base is my front end toward a Mariadb (V10) as backend database
server system.
Cheers
Harvey
On Tue, 2023-01-24 at 14:45 -0500, Andrew Pitonyak wrote:
>
> I am not familiar with "DoCmd.RunSQL", I will have to look it up
> (unless you have a few links). Is DoCmd a server?
>
> I have not used Base with Macros in a long time, but, last time I
> did, I was creating connections to a database and then running SQL
> commands using a connection object.
>
> I have examples in AndrewBase.odt if that is of any help to you.
>
> https://www.pitonyak.org/database/
> https://www.pitonyak.org/database/AndrewBase.odt (called Random DB
> Ramblins, and yes that is spelled incorrectly).
>
>
> On Tuesday, January 24, 2023 14:32 EST, Harvey Nimmo
> wrote:
> Where can I find the syntax of the DoCmd.RunSQL command to be used
> with
> a LOBase Macro in Basic?
>
> My assumption was
> X = "SQL string"
> DoCmD.RunSQL X
>
> The SQL string runs correctly is correct when executed as SQL Command
> in the Base Tools/SQL window.
>
> The Basic Macro with the above statemsnt compiles without error when
> the SQL command text is enclosed in "" quotes. But at runtime it
> halts
> with the message that the variable X is not set. However, a Basic
> Print
> statement shows the correct value for X.
>
> (By the way, the Watch function displays no values for any variable.
> Not sure if it should)
>
> My conclusion is that I ma using the wrong syntax for DoCmd.RunSQL
>
> Any ideas?
>
>
>
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Re: [libreoffice-users] Basic Macros with Base
I am not familiar with "DoCmd.RunSQL", I will have to look it up (unless you have a few links). Is DoCmd a server? I have not used Base with Macros in a long time, but, last time I did, I was creating connections to a database and then running SQL commands using a connection object. I have examples in AndrewBase.odt if that is of any help to you. https://www.pitonyak.org/database/ https://www.pitonyak.org/database/AndrewBase.odt (called Random DB Ramblins, and yes that is spelled incorrectly). On Tuesday, January 24, 2023 14:32 EST, Harvey Nimmo wrote: Where can I find the syntax of the DoCmd.RunSQL command to be used with a LOBase Macro in Basic? My assumption was X = "SQL string" DoCmD.RunSQL X The SQL string runs correctly is correct when executed as SQL Command in the Base Tools/SQL window. The Basic Macro with the above statemsnt compiles without error when the SQL command text is enclosed in "" quotes. But at runtime it halts with the message that the variable X is not set. However, a Basic Print statement shows the correct value for X. (By the way, the Watch function displays no values for any variable. Not sure if it should) My conclusion is that I ma using the wrong syntax for DoCmd.RunSQL Any ideas? -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
