Ask Your Question
0

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 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.)

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
0

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

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

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

JPL gravatar image

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

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

edit flag offensive delete link more
0

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: "https://bugs.documentfoundation.org/s..." 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

Stats

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

Seen: 144 times

Last updated: Apr 13 '16