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

Update SNAFU with Access 97

P: n/a
Our setup:
Online db: MySQL
Inhouse db: MS Access 97 with MySQL tables linked via ODBC

Our issue:
Almost every field updates successfully, except one.

A scenario:
Information is written to online db.
We run an inhouse query to collect info from online db and update
inhouse db.
All fields from the online db update the inhouse db fine except one.
The odd field is of the type "text", and so is its corresponding
online field.
Other fields of the type "text" update just fine.

When in query design mode, viewing the query as a datasheet shows
correct updated data in all of the fields, including the problem
field, however running the query does not update that one field,
regardless of what the query in datasheet mode displays.

Here is the query:
===START===
UPDATE Client_Name_Addr RIGHT JOIN staging ON Client_Name_Addr.rec_id
= staging.rec_id SET Client_Name_Addr.[First Name] =
[staging]![first_name], Client_Name_Addr.[Last Name] =
[staging]![last_name], Client_Name_Addr.Addr1 = [staging]![addr_1],
Client_Name_Addr.Addr2 = [staging]![addr_2], Client_Name_Addr.City =
[staging]![addr_city], Client_Name_Addr.State =
[staging]![addr_state], Client_Name_Addr.Zip = [staging]![addr_zip],
Client_Name_Addr.CaseType = [staging]![case_type],
Client_Name_Addr.[Next Court] = [staging]![next_date],
Client_Name_Addr.UserID = [staging]![user_id], Client_Name_Addr.Notes
= [staging]![notes];
===END===

"Text" fields in this query are:
First Name, Last Name, Addr1, Addr2, City, State, Zip, CaseType, and
UserID

All of the fields, including integer and memo fields, update
successfully with the exception of the "UserID" field.

I very much appreciate any insight into this issue. Really. Anything.
Thanks!
Nov 13 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
James Butler wrote:
Our setup:
Online db: MySQL
Inhouse db: MS Access 97 with MySQL tables linked via ODBC

Our issue:
Almost every field updates successfully, except one.

A scenario:
Information is written to online db.
We run an inhouse query to collect info from online db and update
inhouse db.
All fields from the online db update the inhouse db fine except one.
The odd field is of the type "text", and so is its corresponding
online field.
Other fields of the type "text" update just fine.

When in query design mode, viewing the query as a datasheet shows
correct updated data in all of the fields, including the problem
field, however running the query does not update that one field,
regardless of what the query in datasheet mode displays.

Here is the query:
===START===
UPDATE Client_Name_Addr RIGHT JOIN staging ON Client_Name_Addr.rec_id
= staging.rec_id SET Client_Name_Addr.[First Name] =
[staging]![first_name], Client_Name_Addr.[Last Name] =
[staging]![last_name], Client_Name_Addr.Addr1 = [staging]![addr_1],
Client_Name_Addr.Addr2 = [staging]![addr_2], Client_Name_Addr.City =
[staging]![addr_city], Client_Name_Addr.State =
[staging]![addr_state], Client_Name_Addr.Zip = [staging]![addr_zip],
Client_Name_Addr.CaseType = [staging]![case_type],
Client_Name_Addr.[Next Court] = [staging]![next_date],
Client_Name_Addr.UserID = [staging]![user_id], Client_Name_Addr.Notes
= [staging]![notes];
===END===

"Text" fields in this query are:
First Name, Last Name, Addr1, Addr2, City, State, Zip, CaseType, and
UserID

All of the fields, including integer and memo fields, update
successfully with the exception of the "UserID" field.

I very much appreciate any insight into this issue. Really. Anything.
Thanks!


If I were you I'd start with a simpler update statement, say to the
user_id alone, and work up from there.
--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #2

P: n/a
Trevor Best <nospam@localhost> wrote in message news:<40***********************@auth.uk.news.easyn et.net>...
If I were you I'd start with a simpler update statement, say to the
user_id alone, and work up from there.


The whole query works with the exception of a successful update of the
one field. It works fine with the problem field in the query, and it
works fine with the problem field not in the query. All fields update
successfully regardless of their type...except for that one problem
text field.

I even set up a query with JUST the problem field, and it still
refuses to update. I'm not getting any error messages, and the query
is running through to the end. Since this is one query out of five
that are triggered by a macro, you might think that a problem with
this query would cause problems with the next queries in
sequence...but that does not happen.

Perhaps this might be interesting to note: Running the reverse query
(updating the online MySQL database from the inhouse MS Access97
database) works just fine. Whatever value is in the problem field in
the Access db gets sent to the MySQL db, and updates it properly..

It's just a stinkin' text field, for crying out loud! What issues does
MS Access97 have with updating multiple text fields? The other text
fields update just fine.

Thanks in advance.
Nov 13 '05 #3

P: n/a
st**********@hotmail.com (James Butler) wrote:
Trevor Best <nospam@localhost> wrote in message news:<40***********************@auth.uk.news.easyn et.net>...
If I were you I'd start with a simpler update statement, say to the
user_id alone, and work up from there.


The whole query works with the exception of a successful update of the
one field.


Possibly user_id is a reserved word in MySQL? See
http://dev.mysql.com/doc/mysql/en/mysqld_safe.html

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 13 '05 #4

P: n/a
Tony Toews <tt****@telusplanet.net> wrote in message news:<4r********************************@4ax.com>. ..
Possibly user_id is a reserved word in MySQL? See
http://dev.mysql.com/doc/mysql/en/mysqld_safe.html


Actually, the field names are AttyID and attorney_id. I swapped in
user_id due to misplaced paranoia. :) I'm so used to modifying stuff
before I post it, it's gotten to be irritating!

It occurs to me to mention that the Client_Name_Addr table is
replicated. I'm sure that has implications, but why update some text
fields but not all? I get the feeling I'm missing a basic tenet of how
MS Access97 deals with text fields...although I can't imagine it would
be too complicated.
Nov 13 '05 #5

P: n/a
James Butler wrote:
Tony Toews <tt****@telusplanet.net> wrote in message news:<4r********************************@4ax.com>. ..
Possibly user_id is a reserved word in MySQL? See
http://dev.mysql.com/doc/mysql/en/mysqld_safe.html

Actually, the field names are AttyID and attorney_id. I swapped in
user_id due to misplaced paranoia. :) I'm so used to modifying stuff
before I post it, it's gotten to be irritating!

It occurs to me to mention that the Client_Name_Addr table is
replicated. I'm sure that has implications, but why update some text
fields but not all? I get the feeling I'm missing a basic tenet of how
MS Access97 deals with text fields...although I can't imagine it would
be too complicated.


It looks like you took it back to the basic and updated (or failed to
update) just the one problem field. Can you now do a query without the
join, e.g. "update client_name_addr set AttyID = 'wibble'". Does it
update then? If not then we can take MySQL out of the equation.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #6

P: n/a
Silent failure is unusual.
Have you done something like 'Set Warnings Off' or disabled
transactions for the RunSQL? If you run the query from the
immediate window, do you get anything different:

CodeDB.Execute "my queryname or sql", dao.dbFailOnError

(david)
"James Butler" <st**********@hotmail.com> wrote in message
news:5e**************************@posting.google.c om...
Trevor Best <nospam@localhost> wrote in message

news:<40***********************@auth.uk.news.easyn et.net>...
If I were you I'd start with a simpler update statement, say to the
user_id alone, and work up from there.


The whole query works with the exception of a successful update of the
one field. It works fine with the problem field in the query, and it
works fine with the problem field not in the query. All fields update
successfully regardless of their type...except for that one problem
text field.

I even set up a query with JUST the problem field, and it still
refuses to update. I'm not getting any error messages, and the query
is running through to the end. Since this is one query out of five
that are triggered by a macro, you might think that a problem with
this query would cause problems with the next queries in
sequence...but that does not happen.

Perhaps this might be interesting to note: Running the reverse query
(updating the online MySQL database from the inhouse MS Access97
database) works just fine. Whatever value is in the problem field in
the Access db gets sent to the MySQL db, and updates it properly..

It's just a stinkin' text field, for crying out loud! What issues does
MS Access97 have with updating multiple text fields? The other text
fields update just fine.

Thanks in advance.

Nov 13 '05 #7

P: n/a
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:<41***********************@news.syd.swiftdsl. com.au>...
Silent failure is unusual.
Have you done something like 'Set Warnings Off' or disabled
transactions for the RunSQL? If you run the query from the
immediate window, do you get anything different:


Probably the most simple illustration of my experience running the
query is:

1) Open the Access97 database, select the query, click "Design".
2) From the other "Design" options, select "Datasheet" view.

This results in exactly what I expect the query to do. It shows that
correct data from the mySQL db would update the Access db in all the
right places, including the problem field (no problem).

3) Close the query design interface.
4) Select the query, click "Open" to run it for real.

This results in the issue I am writing about...everything updates
except for the one text field in every row where there should have
been an update. I get the initial warning "You are about to run...",
as usual for an update query. The query finishes, gives me the usual
"..is about to update x rows..." and the "...failed to update x rows
due to..." (which I get with every Access 97 query, due to existing
matching data and such.) No other warnings or error messages, all
fields except the problem field are updated with mySQL data, and the
db continues on as if everything were just fine.

I'm thinking about changing to an integer field! :)

I'll try to force any errors to display, as suggested.
Nov 13 '05 #8

P: n/a
James Butler wrote:
"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message news:<41***********************@news.syd.swiftdsl. com.au>...
Silent failure is unusual.
Have you done something like 'Set Warnings Off' or disabled
transactions for the RunSQL? If you run the query from the
immediate window, do you get anything different:

Probably the most simple illustration of my experience running the
query is:

1) Open the Access97 database, select the query, click "Design".
2) From the other "Design" options, select "Datasheet" view.

This results in exactly what I expect the query to do. It shows that
correct data from the mySQL db would update the Access db in all the
right places, including the problem field (no problem).

3) Close the query design interface.
4) Select the query, click "Open" to run it for real.

This results in the issue I am writing about...everything updates
except for the one text field in every row where there should have
been an update. I get the initial warning "You are about to run...",
as usual for an update query. The query finishes, gives me the usual
"..is about to update x rows..." and the "...failed to update x rows
due to..." (which I get with every Access 97 query, due to existing
matching data and such.) No other warnings or error messages, all
fields except the problem field are updated with mySQL data, and the
db continues on as if everything were just fine.

I'm thinking about changing to an integer field! :)

I'll try to force any errors to display, as suggested.


The "failed to update x rows due to..." is probably the thing we're all
missing here.

Try running it as DAO querydef, e.g.

CurrentDb.Execute "MyQuery", dbFailOnError

Any other messages?

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 13 '05 #9

P: n/a
Geez. What a dope I am.

I started deconstructing the query, updating only one field at a time
to try to determine where the hangup was. This is obviously something
I should have done a long time ago.

Field after field worked fine...until I got to the unlikely Addr2
field...and then the [Next Date] field.

As I went through the fields, running the query, I gained a new
appreciation for the cryptic "validation rule violation" message which
didn't used to affect me, or so I thought.

There is nothing in the "validation rule" or "validation text"
sections of the table design interface for any of the fields, so I
thought it was yet another silly, irrelevant error message from the
Microsoft jokers.

Running the query during my tests returned no such "validation rule
violations" when the test was successful. Always a "validation rule
violation" occured during unsuccessful tests, during which no data was
updated...even the fields listed in the query prior to Addr2 and [Next
Date].

The "validation rule" which was being violated in BOTH cases was...are
you sitting down?..."Allow Zero Length" was set to "No" in both
fields, and they should have been set to "Yes" in both fields.

Changing the table properties to reflect that insight fixed
everything.

I guess the rows that contained data that were successfully being
updated up to this point did not violate that rule, and I just didn't
notice that those that DID violate it were not being updated. (I'm the
MySQL builder. I did not build the ancient Access database, nor am I a
person who actually uses the databases in question.)

To sum up: There was nothing wrong with updating the text fields, as
long as all of the fields are properly set up to accept a variety of
potential values, including NO value.

Lesson learned. Crow being consumed...
Thanks to everyone for all of the thought you gave this. I do
appreciate it.
Nov 13 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.