Board index » delphi » Cached updates with MS Access

Cached updates with MS Access

Hi

Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
no problems until I turn Cached Updates on.  Then I get what appears to be
this incomplete error message:-

    "General SQL Error.
    Operation is not supported for this kind of object.
    Field."

This only occurs when one or more of the requested fields is a numeric type
(integer or float).  If the fields are all char/text types there's no
problem and the query delivers.  As I say, the query *can* deliver numeric
fields provided cached updates are not requested.

Can anyone explain this or offer a workaround?

-Pete

PS  When I try the same thing with MS SQL Server I have no problems at all,
seemimg to suggest that the error is in the MSACCESS driver?  Even odder,
using a table instead of a query also works fine with Access.

 

Re:Cached updates with MS Access


Quote
>Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
>no problems until I turn Cached Updates on.  Then I get what appears to be
>this incomplete error message:-

>    "General SQL Error.
>    Operation is not supported for this kind of object.
>    Field."

I haven't seen an answer to this. It's just one of several little gotchas
that made me basically give up on Access development with Delphi.

Re:Cached updates with MS Access


Quote
>>Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
>>no problems until I turn Cached Updates on.

You might try using a TClientDataset / TProvider instead of Cached Updates.

=Bill=

Re:Cached updates with MS Access


I've just developed a whole application based on Access and using TQuery and
Cached updates. Almost worked all okay. However had to use IDA3532.dll
version 5.0.0.38 9 (BDE DA03.5 access driver). The driver that comes with
the D4 5.02 patch is no good - V5.0.1.22

Just came across one problem that currently appears fatal (See an earlier
post of mine) - but have switched to using an ODBC connection to Access
until a new version of the driver is available (hopefully shortly).

Apparently some of the errors in the latest BDE access driver version have
been found but not yet tested ready for release - See posts of about 2 weeks
ago.

Myles.

Quote
>Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
>no problems until I turn Cached Updates on.  Then I get what appears to be
>this incomplete error message:-

>    "General SQL Error.
>    Operation is not supported for this kind of object.
>    Field."
>Can anyone explain this or offer a workaround?

>-Pete

Re:Cached updates with MS Access


Thanks, Bill

It shouldn't be necessary though, should it?  It makes you wonder if anyone
*really* tests this stuff before launching it.
I don't know about you, but I'm no longer persuaded that Delphi is in good
hands.  It's not the cheap item that Turbo Pascal used to be - I'm using the
very expensive C/S - and it ought to be right.  I sometimes wish Bill Gates
would take it over.  It would be properly documented and supported, and
Anders would have the tiller again.
-Pete

Quote
WmBurke(TeamB) wrote in message <73ao3f$5...@forums.borland.com>...
>>>Here's a curious thing.  When I use a TQuery to access MS Access(!) I
have
>>>no problems until I turn Cached Updates on.

>You might try using a TClientDataset / TProvider instead of Cached Updates.

>=Bill=

Re:Cached updates with MS Access


Thanks, Myles

Using the earlier DLL allows the select query to execute with cached updates
set, but then crashes the update query!

You just can't win.  (See my thoughts to Bill Burke)

You mention an update on 5.01  Have you certain knowledge that an update is
in the offing or when it might appear?

-Pete

Re:Cached updates with MS Access


Quote
>Just came across one problem that currently appears fatal (See an earlier
>post of mine) - but have switched to using an ODBC connection to Access
>until a new version of the driver is available (hopefully shortly).

How you can do that (change to odbc). Just put the alias to a dns or what
more? Is fully compatible with all the work i do in BDE Native fashion?

German Gentile

Re:Cached updates with MS Access


WmBurke(TeamB) escribi en mensaje <73ao3f$5...@forums.borland.com>...

Quote
>>>Here's a curious thing.  When I use a TQuery to access MS Access(!) I
have
>>>no problems until I turn Cached Updates on.

>You might try using a TClientDataset / TProvider instead of Cached Updates.

What about D4 pro users?

Re:Cached updates with MS Access


Well, I followed Myles's example and tried ODBC.  It works faultlessly!  So
what are we doing farting about with the native drivers?  Has anyone done
any objective testing - as opposed to pure prejudice - to determine whether
there are any real drawbacks to ODBC with MS Access?

German, to answer your question.  Create the DSN by double-clicking the ODBC
icon in Control Panel.  Create your MS Access Data Source and exit.  It will
then get included automatically in the list of available databases/aliases
in the Delphi IDE.  If you use the new ODBC alias instead of the one you're
using now, I don't think you'll have any other changes to make.

-Pete

Quote
German Gentile wrote in message <73c3lp$6...@forums.borland.com>...

>>Just came across one problem that currently appears fatal (See an earlier
>>post of mine) - but have switched to using an ODBC connection to Access
>>until a new version of the driver is available (hopefully shortly).

>How you can do that (change to odbc). Just put the alias to a dns or what
>more? Is fully compatible with all the work i do in BDE Native fashion?

>German Gentile

Re:Cached updates with MS Access


I think it is the time for Borland to clear up some problems with BDE
concerning with Access 95, 97 including both setup/configuration and different
version of BDE.  It is already a fact  that Uncle Bill will drive developers to
use Access.  Besides, its single file format is also too appealing.  The time
we spent on making a setup program already counts fair percentage of our
product development.  And we are still not sure if another vendor's setup
program will mess up the configuration again.  It is even worse with the
version problem.  You definitely can not prevent another setup program to
install newer version of BDE.  While many developers are forced to choose third
part replacement.  It is really not a long-term solution.  They are usually
short of a full set of components, i.e. most of them do not have BatchMove
support.
Instead of using a centralized BDE, could Borland consider componentizing it.
Or at least allow local configration files.

James.

Quote
Peter McCall wrote:
> Hi

> Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
> no problems until I turn Cached Updates on.  Then I get what appears to be
> this incomplete error message:-

>     "General SQL Error.
>     Operation is not supported for this kind of object.
>     Field."

> This only occurs when one or more of the requested fields is a numeric type
> (integer or float).  If the fields are all char/text types there's no
> problem and the query delivers.  As I say, the query *can* deliver numeric
> fields provided cached updates are not requested.

> Can anyone explain this or offer a workaround?

> -Pete

> PS  When I try the same thing with MS SQL Server I have no problems at all,
> seemimg to suggest that the error is in the MSACCESS driver?  Even odder,
> using a table instead of a query also works fine with Access.

Re:Cached updates with MS Access


James,

I agree. We've got a lot invested here. It's long past time that
Inprise address the "BDE problem."

Phil Cain

"James M. Lucas" <JMLu...@LegendNet.com> wrote:

Quote
>I think it is the time for Borland to clear up some problems with BDE
>concerning with Access 95, 97 including both setup/configuration and different
>version of BDE.  It is already a fact  that Uncle Bill will drive developers to
>use Access.  Besides, its single file format is also too appealing.  The time
>we spent on making a setup program already counts fair percentage of our
>product development.  And we are still not sure if another vendor's setup
>program will mess up the configuration again.  It is even worse with the
>version problem.  You definitely can not prevent another setup program to
>install newer version of BDE.  While many developers are forced to choose third
>part replacement.  It is really not a long-term solution.  They are usually
>short of a full set of components, i.e. most of them do not have BatchMove
>support.
>Instead of using a centralized BDE, could Borland consider componentizing it.
>Or at least allow local configration files.

>James.

>Peter McCall wrote:

>> Hi

>> Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
>> no problems until I turn Cached Updates on.  Then I get what appears to be
>> this incomplete error message:-

>>     "General SQL Error.
>>     Operation is not supported for this kind of object.
>>     Field."

>> This only occurs when one or more of the requested fields is a numeric type
>> (integer or float).  If the fields are all char/text types there's no
>> problem and the query delivers.  As I say, the query *can* deliver numeric
>> fields provided cached updates are not requested.

>> Can anyone explain this or offer a workaround?

>> -Pete

>> PS  When I try the same thing with MS SQL Server I have no problems at all,
>> seemimg to suggest that the error is in the MSACCESS driver?  Even odder,
>> using a table instead of a query also works fine with Access.

Re:Cached updates with MS Access


Peter,
Could you give me a bit more information how you implement cached updates.
Is the update component specified and upd sql statements are valid ?

I successfully completed projects using access 97 and cached updates.

The thread moves slightly to the odbc/native story. I cannot comment the
current release of the bde. I switched to odbc after having trouble with the
native driver in d3 c/s. But cached updates are working fine with odbc. Be sure
you use code where it is needed and handle update errors accordingly.

The move from native to odbc is really a smooth thing (thanks to bde!), just
configure an odbc driver and specify its' dns in an alias.

Kind Regards
Max

Quote
Peter McCall wrote:
> Hi

> Here's a curious thing.  When I use a TQuery to access MS Access(!) I have
> no problems until I turn Cached Updates on.  Then I get what appears to be
> this incomplete error message:-

>     "General SQL Error.
>     Operation is not supported for this kind of object.
>     Field."

> This only occurs when one or more of the requested fields is a numeric type
> (integer or float).  If the fields are all char/text types there's no
> problem and the query delivers.  As I say, the query *can* deliver numeric
> fields provided cached updates are not requested.

> Can anyone explain this or offer a workaround?

> -Pete

> PS  When I try the same thing with MS SQL Server I have no problems at all,
> seemimg to suggest that the error is in the MSACCESS driver?  Even odder,
> using a table instead of a query also works fine with Access.

--

=======================
MBS Software Co., Ltd.
M.-Ph. Blickenstorfer
67/359 Mooban Tantong 5
Phuket 83000
Thailand
Tel     : +66 76 242 516
Fax     : +66 76 242 383
e-mail  :  m...@loxinfo.co.th
=======================

Other Threads