ACCESS ADO-Update Function does not work correctly

[Guest]

Hi,

I have a major problem. I use Windows 2000 highest SP and ACCESS 2000 inside
Office 2000 SP-3.

I want to use the following simple command inside ACCESS in VBA

UPDATE tbl_SATURN_Benu tzer SET bool_IstDatensa tzAktuell = False,
bool_ImportChec kFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
((bool_ImportCh eckFlag = False) AND (dat_Löschdatum = Null) AND
(byte_Anwendung = 2));

This means: With the above command I want to update three columns in a table
with UPDATE and SET using a WHERE clause.

Theoreticall y one row should be found and updated. But inside VBA no single
row was found and so no update, absolutely wrong!!

If I take this command and put it completely in ACCESS into a query in
SQL-View it and I execute it, it also finds no row !! This is wrong again.
When I directly switch into the design-view mode and I save it as a new
query before (this is important!!), I get the one desired row updated. If I
go back to SQL-view copy & pase this "working" string into VBA and I execute
it again, I unfortunately get no row as result again.

Can anyone help me? I assume there is a bug. Is there any walk around?

Any help is very appreciated.

Regards,
Winfried
(bremen567@hotm ail.com)

[Darryl Kerkeslager]

Re: ACCESS ADO-Update Function does not work correctly

<bremen567@hotm ail.com> wrote[color=blue]
> I want to use the following simple command inside ACCESS in VBA
>
> UPDATE tbl_SATURN_Benu tzer SET bool_IstDatensa tzAktuell = False,
> bool_ImportChec kFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
> ((bool_ImportCh eckFlag = False) AND (dat_Löschdatum = Null) AND
> (byte_Anwendung = 2));
>
> This means: With the above command I want to update three columns in a[/color]
table[color=blue]
> with UPDATE and SET using a WHERE clause.
>
> Theoreticall y one row should be found and updated. But inside VBA no[/color]
single[color=blue]
> row was found and so no update, absolutely wrong!!
>
> If I take this command and put it completely in ACCESS into a query in
> SQL-View it and I execute it, it also finds no row !! This is wrong again.
> When I directly switch into the design-view mode and I save it as a new
> query before (this is important!!), I get the one desired row updated. If[/color]
I[color=blue]
> go back to SQL-view copy & pase this "working" string into VBA and I[/color]
execute[color=blue]
> it again, I unfortunately get no row as result again.[/color]


dat_Löschdatum = Null

and

dat_Löschdatum Is Null

will return different results.

Access will not convert "= Null" in SQL View, but it will in QBD View.


Darryl Kerkeslager

[Dimitri Furman]

Re: ACCESS ADO-Update Function does not work correctly

On Jan 25 2005, 03:12 pm, <bremen567@hotm ail.com> wrote in
news:ct6986$bph $03$1@news.t-online.com:
[color=blue]
> I want to use the following simple command inside ACCESS in VBA
>
> UPDATE tbl_SATURN_Benu tzer SET bool_IstDatensa tzAktuell = False,
> bool_ImportChec kFlag = True, dat_Löschdatum = #01/25/2005 18:38:17#
> WHERE ((bool_ImportCh eckFlag = False) AND (dat_Löschdatum = Null) AND
> (byte_Anwendung = 2));
>
> This means: With the above command I want to update three columns in a
> table with UPDATE and SET using a WHERE clause.
>
> Theoreticall y one row should be found and updated. But inside VBA no
> single row was found and so no update[/color]

Change (dat_Löschdatum = Null) to (dat_Löschdatum Is Null). Null is not a
value, it cannot be compared using the equals sign - in SQL you have to use
the Is operator.

--
remove a 9 to reply by email

[Guest]

Re: ACCESS ADO-Update Function does not work correctly

Thx for your solution. I guess I worked too much yesterday that I didn't
recognized the error.
Winfried


<bremen567@hotm ail.com> schrieb im Newsbeitrag
news:ct6986$bph $03$1@news.t-online.com...[color=blue]
> Hi,
>
> I have a major problem. I use Windows 2000 highest SP and ACCESS 2000
> inside Office 2000 SP-3.
>
> I want to use the following simple command inside ACCESS in VBA
>
> UPDATE tbl_SATURN_Benu tzer SET bool_IstDatensa tzAktuell = False,
> bool_ImportChec kFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
> ((bool_ImportCh eckFlag = False) AND (dat_Löschdatum = Null) AND
> (byte_Anwendung = 2));
>
> This means: With the above command I want to update three columns in a
> table with UPDATE and SET using a WHERE clause.
>
> Theoreticall y one row should be found and updated. But inside VBA no
> single row was found and so no update, absolutely wrong!!
>
> If I take this command and put it completely in ACCESS into a query in
> SQL-View it and I execute it, it also finds no row !! This is wrong again.
> When I directly switch into the design-view mode and I save it as a new
> query before (this is important!!), I get the one desired row updated. If
> I go back to SQL-view copy & pase this "working" string into VBA and I
> execute it again, I unfortunately get no row as result again.
>
> Can anyone help me? I assume there is a bug. Is there any walk around?
>
> Any help is very appreciated.
>
> Regards,
> Winfried
> (bremen567@hotm ail.com)
>[/color]