Ask Your Question

Access2Base DFunctions with Mysql [closed]

asked 2016-04-11 23:36:03 +0200

Michel_1495 gravatar image

updated 2016-04-12 00:14:49 +0200

I am working on a membership database based on the one I created in ms-access, front end = libre office base, 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.)

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Michel_1495
close date 2016-04-15 22:41:11.058314

3 Answers

Sort by » oldest newest most voted

answered 2016-04-12 00:51:36 +0200

Ratslinger gravatar image

updated 2016-04-12 01:05:20 +0200

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


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
edit flag offensive delete link more

answered 2016-04-12 13:05:46 +0200

JPL gravatar image

updated 2016-04-13 09:37:42 +0200


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


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

edit flag offensive delete link more

answered 2016-04-12 13:29:04 +0200

Michel_1495 gravatar image

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: "" 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

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2016-04-11 23:36:03 +0200

Seen: 196 times

Last updated: Apr 13 '16