By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,105 Members | 1,048 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,105 IT Pros & Developers. It's quick & easy.

ACCESS ADO-Update Function does not work correctly

P: n/a
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_Benutzer SET bool_IstDatensatzAktuell = False,
bool_ImportCheckFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
((bool_ImportCheckFlag = 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
(br*******@hotmail.com)
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
<br*******@hotmail.com> wrote
I want to use the following simple command inside ACCESS in VBA

UPDATE tbl_SATURN_Benutzer SET bool_IstDatensatzAktuell = False,
bool_ImportCheckFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
((bool_ImportCheckFlag = 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.

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
Nov 13 '05 #2

P: n/a
On Jan 25 2005, 03:12 pm, <br*******@hotmail.com> wrote in
news:ct*************@news.t-online.com:
I want to use the following simple command inside ACCESS in VBA

UPDATE tbl_SATURN_Benutzer SET bool_IstDatensatzAktuell = False,
bool_ImportCheckFlag = True, dat_Löschdatum = #01/25/2005 18:38:17#
WHERE ((bool_ImportCheckFlag = 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


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
Nov 13 '05 #3

P: n/a
Thx for your solution. I guess I worked too much yesterday that I didn't
recognized the error.
Winfried
<br*******@hotmail.com> schrieb im Newsbeitrag
news:ct*************@news.t-online.com...
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_Benutzer SET bool_IstDatensatzAktuell = False,
bool_ImportCheckFlag = True, dat_Löschdatum = #01/25/2005 18:38:17# WHERE
((bool_ImportCheckFlag = 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
(br*******@hotmail.com)

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.