473,230 Members | 1,462 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,230 software developers and data experts.

Problem with inserting dates using mx.ODBC.Windows driver

Hello,

I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.

When I read a DATE item everything works fine, like:
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
data = cursor.fetchall()
print data [(..., <DateTime object for '2003-07-31 00:00:00.00 at address>, ...)]

But when I try to update the table I get a ProgrammingError, like:
date = mx.ODBC.Windows.Date(2003, 07, 31)
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("INSERT INTO table (..., DATE, ...) VALUES (..., ?,

....)", (..., date, ...))
ProgrammingError: Syntax Error in INSERT INTO statement.

Is it possible to use the mx.DateTime type for MSAccess databases? If so,
how should it be done? If not, how can I insert dates into a MSAccess
database?

Thank you in advance, Fons
Jul 18 '05 #1
1 3266
Fons Dijkstra wrote:
Hello,

I'm using the mx.ODBC.Windows package in order to read/write a MSAccess
database. Everything works fine apart from the DATE format handling. I'm
using the default "datetimeformat" (i.e. DATETIME_DATETIMEFORMAT) as
suggested. The date columns in the MSAccess database have "Short Date"
format.

When I read a DATE item everything works fine, like:

conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("SELECT * FROM table")
data = cursor.fetchall()
print data
[(..., <DateTime object for '2003-07-31 00:00:00.00 at address>, ...)]

But when I try to update the table I get a ProgrammingError, like:

date = mx.ODBC.Windows.Date(2003, 07, 31)
conn = mx.ODBC.Windows.connect(database)
cursor = conn.cursor()
cursor.execute("INSERT INTO table (..., DATE, ...) VALUES (..., ?,
...)", (..., date, ...))
ProgrammingError: Syntax Error in INSERT INTO statement.

Is it possible to use the mx.DateTime type for MSAccess databases? If so,
how should it be done? If not, how can I insert dates into a MSAccess
database?
That's strange: if you pass in an mxDateTime value as bound
parameter to .execute() then underlying ODBC driver will take
care of converting it to the format needed by the database.

If that fails for MS Access, then something in their driver
must be broken or the driver is requesting the date value
as string which it really shouldn't.

As work-around you could convert the mxDateTime value
to a string in the needed format and pass the string
instead of the original value.
Thank you in advance, Fons


--
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source (#1, Aug 01 2003)
Python/Zope Products & Consulting ... http://www.egenix.com/
mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/

__________________________________________________ ______________________
Jul 18 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: simianphile | last post by:
OK, I had a problem that I've now fixed but I can't really understand what was causing it in the first place. I have an intranet site that uses basic authentication to allow users to view and...
5
by: SerGioGio | last post by:
Hello, I am going nuts. I am trying to connect to my local ORACLE instance using ODBC. It used to work few weeks ago, but it fails now. Connection with: - SQL*plus: connection works! -...
1
by: php newbie | last post by:
Hello, I am trying to insert some date values into a table. I am using ODBC prepared statements to (potentially) improve performance. The statement syntax I am using is this: INSERT INTO...
0
by: AlessanBar | last post by:
Hello Friends !! I have a strange problem, and I need to know what would be the source of this. I have a laptop computer with the following configuration: Pentium III Brand : Toshiba Speed :...
0
by: Jindrich Prchal | last post by:
Hi there. We are running DB2 v7.2 for Win NT on Windows 2000 SP3 machine with poor configuration AMD 1800+, 512MB RAM and usual IDE harddisk. During tests of migration our batch programmes...
3
by: Wescotte | last post by:
I'm having an issue with a php program I am writing. If the user enteres their password incorrectly a popup box appears asking them if they would like to reenter their password (it's part of the...
3
by: Richard Morton | last post by:
Hi, This is my first posting to this list, I guess a small intro should be first, been developing in PHP for sometime, mainly with Mysql and nearly always on Windows (oops, sorry). I have...
2
by: tommydkat | last post by:
Hi! I'm updating a Windows application that we develop that uses a DB2 database. We switched from using Microsoft Developer Studio 6 to Visual Studio .Net 2003 to build our app and since that...
2
by: Frank Millman | last post by:
Hi all I have found a problem using MS Sql Server connecting via the odbc module from python-win32. I am liaising with Mark Hammond, and he is trying to help, but he is very busy, and I...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

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.