Access2Base DFunctions with Mysql

[Michel_1495]

I am working on a membership database based on the one I created in ms-access, front end = libre office base 5.1.2.2, back end is a Mysql (usb)server. I use the native mysql connector
In the mysql server I created the database vtt_leden.
In vtt_leden there are 2 tables, one containing the information about the club & current (sport)season: t_club, one with the memberdata: t_leden
From within the member details form I want to read from the table “t_club” the start year of the current sport season (field “club_seizoen”). (I will store this value in the field “leden_seizoen” if the player is participating this season)
For this I use DLookup.

Dim C as integer
C=1 'there is only one record in this table
S = DLookup("[club_seizoen]", “[vtt_leden].[t_club]”, "[club_id]= " & C
When executing the macro I receive a error message:
Error #1510 (DFunction execution failed, SQL=SELECT TOP 1 “club_seizoen” AS “TEMP48341” FROM “vtt_leden”.“t_club” WHERE “club_id”= 1) occurred in DLookup

I tested the same script in a lo with embedded database & it works fine
S = DLookup("[club_seizoen]", “[t_club]”, "[club_id]= " & C

So I wounder if access2base can be used with mysql database.
Anyone with access2base experience can tell me more?

(As a temporary solution I execute a Update SQL, but I can not see the result immediatly on screen.)

[Ratslinger]

Although I don’t use Access2Base, I believe your problem is the quotes around the field names. They work in an embedded DB because that is what is expected but in a macro the code is a passthrough and MySQL is looking for the ` character surrounding the field names.

S = DLookup(`[club_seizoen]`, `[vtt_leden].[t_club]`, `[club_id]`=  & C

or

S = DLookup(`[club_seizoen]`, `[vtt_leden]`.`[t_club]`, `[club_id]`=  & C

not exactly sure with Access2Base.

After a couple of tests this may even work

S = DLookup([club_seizoen], [vtt_leden].[t_club], [club_id]=  & C

[JPLED]

CORRECTION

The correct syntax is indeed:

 S = DLookup("[club_seizoen]", "[vtt_leden].[t_club]", "[club_id]= " & C

It “should” work.

HOWEVER, there is a bug in Access2Base. It does not consider that the

SELECT TOP 1 ...

construction is not valid for MySQL. See for instance here for more info.

I will provide a correction of the Access2Base library. It will be included in release LO 5.2.

Sorry for the inconvenience and many thanks to help us making LO even better.

Regards.
JPL

[Michel_1495]

Hi,
Thanks for your replies but unfortunatly, not working

S = DLookup("[club_seizoen]", “[vtt_leden.t_club]”, "[club_id]= " & C)

S = DLookup("[club_seizoen]", “[t_club]”, "[club_id]= " & C)

I tried really a lot of different possibilities.

I found this: “https://bugs.documentfoundation.org/show_bug.cgi?id=90337” but it seems it was a false alarm: the name of a table was incorrect (case sensitive)
I am pretty sure table & fieldnames are all correct as I do not use capitals

Regards
michel