Subject Re: Lookup doens't work any more
From Mervyn Bick <invalid@invalid.invald>
Date Tue, 5 Nov 2019 12:06:43 +0200
Newsgroups dbase.getting-started

On 2019-11-04 4:09 PM, Dirk wrote:
> Goodday,
> since day and night use a datamodule in a programm,
> now suddently got follow msg :: database Engine Error : Capablitity not
> supported c:\..QSQLOOO.DBF
> when i click further :(ignore) no fields were found to process :
> mainstamm.DMD
> after a while i get errors on *.WFM file
> and finally i get the form can apppend, but don't get the lookup tables
> ( can not chose)
> use 12.xx version
> when i use the source everthing works normal
> this parts of dmd gives error
>   with (this.STAMMDAT1.rowset)
>        with (fields["Province"])
>           lookupSQL = "select area,area from euroregions order by area"
>        endwith
>        with (fields["TAAL"])
>           lookupSQL = "select idtaal,naam from taal order by naam"
>        endwith
>        autoEdit = false
>     endwith
> but yesteray i had to give rights to some directories
> what can be wrong
> thanks
> Dirk

dBASE should delete the temporary _QSL tables when the program closes.
In some cases is does and in others it misses them.  It is a good idea
to clear them out manually from time to time do avoid running out of
available numbers.  If you make sure that no two programs place these
files in the same folder you could write the housekeeping routine into
the application's onOpen event handler thereby ensuring you start with a
clean slate each time.

I've never actually had a situation where a _QSL table has not been
created because dBASE has run out of available numbers but the error
message doesn't look right for that situation.

Your lookupSQL statements, apart from the one for the TAAL field, look
suspect and this is far more likely to give rise to the reported error.

lookupSQL is usually (but of course not always) used to display text in
place of an ID number stored in a field in a table.  The text to be
displayed is fetched from a different table by specifying the fields in
the second table which contain the ID no and the text.  If your main
table stores, say, 2 in the TAAL field then the display of that record
will show "English" if a record in the TAAL table has 2 in its idtaal
field and "English" in its naam field.

All your other lookupSQL statements select one fieldname twice from the
lookup table.  While one can select a field more than once in a query's
select statement it's meaningless in a lookupSQL situation and this is
probably what has given rise to the 'Capability not supported' error.

You don't need an ORDER BY clause in a lookupSQL statement and this may
be contributing to the error.

If each lookup table only has two fields, the Id no and the value to be
returned, then you can use

with (fields["Province"])
    lookupSQL = "select * from from euroregions"

If the lookup field and the return field are not the first two fields in
the lookup table or if there are other fields in the table then you need
to specify the two fields.