by superjacent (Posted Tue Jun 04, 2013 4:00 am)
eugenedakin wrote:
Hello Steve,
It looks like you had many attempts without any success. I have a few thoughts and suggestions, this stumps me is as you mentioned that it works with the SELECT statement... Hmmm....
Here are a couple things to check:
1) Dates are an issue with access because there are many different defaults. Could you try and check the date format? Even though the date format may look correct, Access sometimes has an issue. Try and convert the date format to its decimal equivalent (Example: 2013-06-03 8:53:26 AM = 41428.37044). Access usually works with the decimal equivalent.
2) Try and get your prepared statement to work with only one field update, as an example, see if you can update only the string (without any integer or date field update). If it works with the one field, then try and add the other fields and check for errors.
Could you post an example of the SQL update, as an example db.SQLExecute("UPDATE Library SET Author = 'E. Atkin', Inventory = 9, Title = 'How to fix a Car', Cost = 94.99 WHERE ID = 5") Feel free to change the actual terms used to keep your info confidential.
Note: The commit command does not need to be added to updating Access databases.
Sincerely,
Eugene
Thanks Eugene, your comments are valid and is what I ended up doing isolating the offending/problematic issues of the prepared statement. A sample project including an Access mdb file is here :
https://dl.dropboxusercontent.com/u/946 ... esting.zip
. It reproduces what I highlight in the following paragraphs. One caveat, I now have the latest ODBC driver but this does not alter the result.
From_NUG_List wrote:
An update on this saga. My development machine is a Windows 7, 64 bit system. I have Office 2010 installed. I have Microsoft Access ODBC driver version 14.00.6015.1000 dated 21 Dec 2010. This driver supposedly includes access to both the old .mdb databases and the newer .accdb databases. I do not have Office 2003 installed on this system.
On my VM, it's Windows XP with Office 2003 installed. Microsoft Access ODBC Driver is version 4.00.6305.00 dated 14 Apr 2008. This is the same configuration as my employer's desktop computers.
To recap the issue:
I can execute normal UPDATE SQL statements in both environments.
This is not the case for UPDATE prepared statements. I have detected two problem issues:
1. When attempting to bind STRING / TEXT data the only ODBC data type constants available are STRING and LONGSTRING. Only the LONGSTRING constant partially works for binding. That is, only the first two characters of the text data are actually updated to the database. When binding using the STRING data type no update takes place. Regardless, no database error is thrown.
2. When attempting to send a NULL value for a date field an error is thrown by the database. If a date is valid an update occurs normally. The date field in the database can accept nulls or be empty.
As mentioned, the above two issues are not problems if executing an UPDATE statement normally (non prepared). I am getting the same results on my company computers.
I will be creating a feedback issue regarding this.
ps. With Xojo just about due we might have to pick this topic up in the new forum.
Read the main topic related to this post