I wonder if someone would be so kind as to give me a hand with a
slight problem I have, aside from see a doctor, that is :)

I am trying to evaluate the use of a 'real' database for use in the
next generation of our software which is written in Visual FoxPro and
currently uses xBase tables.

Because our software is aimed at small retailers 99% of whom know
nothing about computers let alone administering a database, I am left
with only a few possibilities, one of which is Firebird.

Now, FoxPro accesses non-xBase data either by using ODBC or ADO
drivers and I have set up a number of the trial versions of both
drivers for the evaluation. ADO works without problem and returns the
correct recordsets as expected but it is not so easy to use an ADO
recordset in FoxPro so I'd prefer to use ODBC which returns an xBase
table. However there seems to be a problem with all the drivers for ODBC.

When trying to create a remote view on a table in the test database
using the view designer I get the error:

Connectivity error: Dynamic SQL Error:
SQL Error Code = -104
Token unknown - line 1, char 21
,

No matter which of the drivers I use the same error is returned. The
test database and one empty table was created using the following script:

/* Test.fdb */
create database "d:\Data\Databases\Test.fdb"
page_size 4096
user 'SYSDBA'
password 'masterkey'
;

create table Lng
(
nLngPK integer not null,
cLngKey varchar(30),
cActive char(1)
)
;

The ODBC conection was created using the Windows ODBC administration
wizard (WinXP) using a predefined data source rather than a
connectivity string, although a quick test using a connection string
gave the same result.

If, however, I build the view manually, a tedious process to be
avoided whenever possible, the process is sucessful.

Has anyone any constructive ideas?

I suspect that, since it is only the FoxPro view designer that has the
problem, it is a problem with the view designer rather than anything
else, but there is also the possibility that I'm doing something wrong
and hence the call for help.

I should mention that since we have over 7 years of developement of
our products using FoxPro we will not be changing to any other
development language in the near future, so answers to the effect of
use VB/Java/C++/... etc will be ignored.

Mark Austen