In base Convert Query to macro

[davidsharpe]

My PC is current.

• Windows 10 Home (x64) Version 1607 (build 14393.1066)
• Install Language: English (United States)
• System Locale: English (United States)
• Installed: 4/21/2017 3:18:25 PM
• Servicing Branch: Current Branch (CB)
• Boot Mode: UEFI with successful Secure Boot

++++++++++++++++++++++

My LibreOffice is is current.

• Version: 5.4.4.2 (x64)
• Build ID: 2524958677847fb3bb44820e40380acbe820f960
• CPU threads: 4; OS: Windows 6.19; UI render: default;
• Locale: en-US (en_US); Calc: group

++++++++++++++++++++

I have been doing a good deal of looking through access2base
and I have effectively activated it.

Sub DBOpen(Optional poEvent As Object)
If GlobalScope.BasicLibraries.hasByName("Access2Base") Then
	GlobalScope.BasicLibraries.loadLibrary("Access2Base")
End If
Call Application.OpenConnection(ThisDatabaseDocument)
End Sub

++++++++++++++++++++

I will be incrementing through a local table (My_Dates) and
querying a second table (diaries) based on year, month and
day of each record. In the samples provided I am just using one
set of variables (year=2017, month=12 and day=5).

++++++++++++++++++++

I have a query that works (returns the correct data).

SELECT MAX( "diaries"."steps" ) AS "steps" 
FROM "diaries" 
WHERE "diaries"."year" = '2017' 
AND "diaries"."month" = '12' 
AND "diaries"."day" = '5'

++++++++++++++++++++++

I’m attempting to implement the above simple select query into
a macro, where the selected MAX gets put into a local variable
(lSteps2Day).

+++++++++++++++++++

I have apparently gotten the punctuation and concatenation correct
(it doesn’t generate an error anymore).

dim strAYear  as string
strAYear = "2017"
dim strAMonth as string
strAMonth ="12"
dim staADay   as string 
strADay = "5"

dim strSql as string
dim lSteps2Day as long


strSql = "SELECT MAX( 'diaries'.'steps' ) " &_
    " FROM 'diaries'" &_
    " WHERE 'diaries'.'year' = 'strAYear' " &_
    " AND 'diaries'.'month' = 'strAMonth' "&_
    " AND 'diaries'.'day' = 'strADay' "

++++++++++++++++++

I now need to call strSql and assign the MAX
returned to my lSteps2Day.

+++++++++++++++++

Any guidance would be appreciated.

Thanks
Dave

[Ratslinger]

Was there a reason in DELETING your last question on TT NorthWind.odb ‘org.hsqldb.jdbcDriver’ could not be loaded? The answer took effort and could be of help to others. Did not even get any response back from you on it being a help or not. Makes me wonder about providing further answers.

[Ratslinger]

Hello,

Here is basic code for running SQL query in macro - click here.

Result sets (SQL return) depend upon data - see this post.

Here is code for SQL updating of a table - click here with the difference from the query being oStatement.executeUpdate( sSQL ).

Edit:

This is more how your SQL need to be in the macro:

strSql = "SELECT MAX( ""diaries"".""steps"" ) FROM ""diaries"" WHERE ""diaries"".""year"" = '" & strAYear & "' AND ""diaries"".""month"" = '" & strAMonth & "' AND ""diaries"".""day"" = '" & strADay & "'"

[SteveR]

Very informative post. Several years I was experimenting with passing data between Base and SQL but couldn’t really get it to work. I went with a Firefox/PHP/Maria.db solution. So it’s a bit late for me.

[Ratslinger]

Thanks for the comment. It goes beyond this. It has proven helpful in standalone forms, Base to Base table copies and data transfer to all other modules. Just gotta use some imagination & research.

[davidsharpe]

Ratslinger - Regarding your "Was there a reason in DELETING your last question "
I regret any inconvenience that I caused; it was unintentional.
I didn’t see it (I thought I would be notified when your post was made) when you posted it.
When I saw it I’d sort of given up on attempting to use it and I didn’t want any one else to work on it on my behalf.
I deleted it to prevent others from working on something I wouldn’t use.
If you’d like me to, it looks like I may have the capability to reactivate it.
And again I hope you feel less of me for how I acted in this matter

Dave

[peterwt]

@davidsharpe Please reactivate if you can I was looking at it.

[Ratslinger]

As @peterwt has stated, yes, please undelete. The answer applies to anyone wanting to use TT Northwind. It can save some headaches.

[Ratslinger]

@davidsharpe FYI email notification has not been working for some time. You must sign in regularly to check for messages.

[Ratslinger]

Have reposted answer as a new question.

[JPLED]

Hi,

to format an SQL statement in Basic, please read THIS. You will see that you might use - only if you’re using Access2Base - square brackets to surround table and field names instead of double quotes (that have to be doubled again due to their presence within a Basic string).

To get the results of the statement in your program, typically read THIS . The OpenRecordset method may receive your SQL as statement and GetRows will load the whole set at once in a Basic variant array.

Hoping this might help.

JPL

[davidsharpe]

Thank you JPL . The links do provide me some very helpful information
Dave