473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

ACCESS ADO-Update Function does not work correctly

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
3 4107
<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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

29
by: Mark B | last post by:
We have an Access app (quite big) at www.orbisoft.com/download. We have had requests by potential users to have it converted to an SQL version for them since there corporate policy excludes them...
13
by: Randy Harris | last post by:
Several years ago I became involved in a major Access development project. At the time, A2K was very new and the books were rather strongly suggesting that ADO was the future and DAO would be...
64
by: John | last post by:
Hi What future does access have after the release of vs 2005/sql 2005? MS doesn't seem to have done anything major with access lately and presumably hoping that everyone migrates to vs/sql. ...
7
by: carl.manaster | last post by:
I'm new to this game. I can find my way around C# without any trouble, and I've used Access, a little bit, in the past. Now a friend wants an application of mine to read from his Access database....
6
by: ASP.Confused | last post by:
I have an ASP.NET page writtein in VB that uses ADODB. I just had to force-install MDAC 2.8 after I tried to rollback to 2.6 (my web host uses this, and I wanted to be compatible with them.) I...
9
by: Lyle Fairfield | last post by:
http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/ADOPlusOverview.aspx **** begin quote **** ADO.NET Overview ADO.NET is an evolution of the ADO data...
8
by: vbDavidC | last post by:
I am wondering if I should use MS Access or SQL server as my database. I have a little experience programming an Access DB with VB6 (DAO/ADO). I am now starting over with VB2005 and will be doing...
10
by: WannaKatana | last post by:
I am just wondering why, with nothing else running and executing an update query against a very large table, does Access seem to be causing less than 10% processor usage. Then it says "There is not...
5
by: MS | last post by:
OK, now, I've climbed far enough up the learning curve to be about where I was with Access in building data-driven Windows forms but I've seen no advantage over the old way. Maybe finer-grained...
6
by: Mark | last post by:
Currently using MS Access 2000 and SQL Server Express. Using the current DAO OpenRecordset code listed below, however I keep getting the error message.... 3254: ODBC --Cannot lock all records ...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.