MS Access data conversion - errors reading linked table when row contains Timestamp

Author

errors reading linked table when row contains Timestamp

mr.bill

2005-04-12, 8:25 pm

My environment is DB2 database v8.2 on AIX as server, Windows XP with MDAC2.8
and Access 2002 (Office XP w/SP3) as client.

I have a table in the database whose key is [ field1(decimal,16,0)
+
field2(char16) + field3(timestamp) ]

In DB2, timestamps go to six places to the right of the nearest second (e.g.
2004-06-02-13.07.53.123456)

Evidently the degree of precision is messing up Access' ability to display
recordsets from rows in this table if the fractional seconds portion contains
a number other than Zero in the fourth, fifth, and sixth position. That's
the ten-thousandths, hundred-thousandths, and millisecond positions if I'm
not mistaken. For instance, the above example would fail to be displayed,
but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.

Ill-affected rows display as #Deleted in all columns if using Datasheet
view, and the resultset of a query cannot be manipulated either as in an
Update or Delete.

Is there no way to solve this issue? Would upgrading to Access 2003 help?
I don't have this problem in code using ADO, but of course the task I have
before me requires I build some temporary tables in Access to update the data
in DB2 with. I shouldn't be spending this much time doing something so
simple.

About the only other alternative I can see is to create a temporary table in
DB2 (requires much red tape) and upload my data from Access into it. Then I
would have to use some other ODBC tool to run the SQL bypassing Access
entirely. That doesn't make our ROI look very favorable. Any other
suggestions would be very much appreciated.

TIA,
~mr.bill

Peter Yang [MSFT]

2005-04-13, 3:25 am

Hello,

From IBM's website, I located a number of articles which discussed the
issue of Microsoft products having trouble with DB2 Timestamp data, and
provides recommended configuration switches that may help out. You may
wish to talk with your DB2 representatives to ensure these have already
been taken into account in your situation. Take a look at the following:
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvv

http://www-1.ibm.com/support/docvie...&q1=%22Microsof
t+Access%22+%22Times
tamp%22&uid=swg21153006&loc=en_US&cs=utf-8&lang=en

That site describes the issue and recommends the following configuration
parameters on the DB2 side. :
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvv
Solution The following CLI Configuration Keywords were added to DB2 v8.1
fixpack 4 as a workaround (depending on the data type of concern):

MapTimeDescribe=1
MapTimeStampDescribe
=1
MapDateDescribe=1
vvvvvvvvvvvvvvvvvvvv
vvvv

Also found a section of CLI Configuration options for the driver. See

http://www-1.ibm.com/support/docvie...&q1=%22Microsof
t+Access%22+%22Times
tamp%22&uid=swg21179697&loc=en_US&cs=utf-8&lang=en

http://www-1.ibm.com/support/docvie...uid=swg21164225

One of the options is as follows:
----------------------
24 Reports TIME data as SQL_CHAR data. This patch value is used as a
workaround for Microsoft Access applications.

Hope this helps.

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

====================
====================
=============

This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| Thread-Topic: errors reading linked table when row contains Timestamp
| thread-index: AcU/qAoJ6pGcdh/HRMS5/uKKKNUgqw==
| X-WBNR-Posting-Host: 12.106.254.66
| From: "=?Utf-8?B?bXIuYmlsbA==?=" < HedoSandwich@newsgro
up.nospam>
| Subject: errors reading linked table when row contains Timestamp
| Date: Tue, 12 Apr 2005 14:39:00 -0700
| Lines: 34
| Message-ID: <8CE05694-1A83-4D04-9501- 00DD9D5E16A5@microso
ft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.conversion
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.conversion:3107
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.access.conversion
|
| My environment is DB2 database v8.2 on AIX as server, Windows XP with
MDAC2.8
| and Access 2002 (Office XP w/SP3) as client.
|
| I have a table in the database whose key is [ field1(decimal,16,0)
+
| field2(char16) + field3(timestamp) ]
|
| In DB2, timestamps go to six places to the right of the nearest second
(e.g.
| 2004-06-02-13.07.53.123456)
|
| Evidently the degree of precision is messing up Access' ability to
display
| recordsets from rows in this table if the fractional seconds portion
contains
| a number other than Zero in the fourth, fifth, and sixth position.
That's
| the ten-thousandths, hundred-thousandths, and millisecond positions if
I'm
| not mistaken. For instance, the above example would fail to be
displayed,
| but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.
|
| Ill-affected rows display as #Deleted in all columns if using Datasheet
| view, and the resultset of a query cannot be manipulated either as in an
| Update or Delete.
|
| Is there no way to solve this issue? Would upgrading to Access 2003
help?
| I don't have this problem in code using ADO, but of course the task I
have
| before me requires I build some temporary tables in Access to update the
data
| in DB2 with. I shouldn't be spending this much time doing something so
| simple.
|
| About the only other alternative I can see is to create a temporary table
in
| DB2 (requires much red tape) and upload my data from Access into it.
Then I
| would have to use some other ODBC tool to run the SQL bypassing Access
| entirely. That doesn't make our ROI look very favorable. Any other
| suggestions would be very much appreciated.
|
| TIA,
| ~mr.bill
|

John Nurick

2005-04-13, 3:25 am

Hi Bill,

Access date/time fields and VBA date/time variables are basically
doubles, IEEE 8-byte floating point values, with the date stored on the
left of the decimal point as a day number and the time on the right as a
fraction of a day. Today is day 3845, and 1 microsecond (the sixth
decimal place of a second) is approximately 0.00000000001157407 days.
Putting the two together you have more significant figures than a double
can handle. Upgrading to Access 2003 won't help in this regard.

If you can round the DB2 values to the nearest millisecond the
resolution of the double is usually good enough (though would be the
usual issues with floating point comparisons and you'll have to write
your own formatting routines).

Otherwise, you could just store the DB2 timestamp as a string in a text
field. These would sort OK and wouldn't cause problems in the primary
key, but you'd have to write code for any date arithmetic that's needed.

In principle you could convert the DB2 timestamp into a number and store
in a Decimal field - but see http://allenbrowne.com/bug-08.html first.

On Tue, 12 Apr 2005 14:39:00 -0700, "mr.bill"
< HedoSandwich@newsgro
up.nospam> wrote:

>My environment is DB2 database v8.2 on AIX as server, Windows XP with MDAC2.8
>and Access 2002 (Office XP w/SP3) as client.
>
>I have a table in the database whose key is [ field1(decimal,16,0)
+
>field2(char16) + field3(timestamp) ]
>
>In DB2, timestamps go to six places to the right of the nearest second (e.g.
>2004-06-02-13.07.53.123456)
>
>Evidently the degree of precision is messing up Access' ability to display
>recordsets from rows in this table if the fractional seconds portion contains
>a number other than Zero in the fourth, fifth, and sixth position. That's
>the ten-thousandths, hundred-thousandths, and millisecond positions if I'm
>not mistaken. For instance, the above example would fail to be displayed,
>but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.
>
>Ill-affected rows display as #Deleted in all columns if using Datasheet
>view, and the resultset of a query cannot be manipulated either as in an
>Update or Delete.
>
>Is there no way to solve this issue? Would upgrading to Access 2003 help?
>I don't have this problem in code using ADO, but of course the task I have
>before me requires I build some temporary tables in Access to update the data
>in DB2 with. I shouldn't be spending this much time doing something so
>simple.
>
>About the only other alternative I can see is to create a temporary table in
>DB2 (requires much red tape) and upload my data from Access into it. Then I
>would have to use some other ODBC tool to run the SQL bypassing Access
>entirely. That doesn't make our ROI look very favorable. Any other
>suggestions would be very much appreciated.
>
>TIA,
>~mr.bill

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

mr.bill

2005-04-13, 8:24 pm

Thank you for the information. I was all over IBM's website yesterday before
I resorted to posting here, and read all about the CLI Configuration Keywords
available thru the configuration panel of their driver software. l had
played around with several without success.

I didn't see the newer ones that you pointed out though - namely
MapTimeStampDescribe
so I gave it a try. Unfortunately, the net result was
not any better. I could see that the data type had changed in Access from
Date/Time to char(26) and in other tables where the timestamp field was not a
part of the key, the records displayed properly with strings in the timestamp
columns. However, in the one table that uses timestamp as part of the key,
none of the records could be displayed now -- the cells all contained #Name?,
instead of #Deleted. In addition, an ODBC error messagebox popped open.

I examined the ODBC trace and saw the error being returned from IBMs driver
is "SQL0180N The syntax of the string representation of a datetime value is
incorrect." I find that particularly ironic given that the KB article you
located at IBM's site stated that the workaround using MapTimeStampDescribe

would cure this exact error for an Access97 client. I'm doubtful that
downgrading to Access 97 would help in this situation as I am still incluned
to believe that the timestamp column being a part of the natural primary key
is what is making this extra difficult.

The basic thrust of the article at IBM's site stated that this was not their
problem as their database and drivers were abiding by the ODBC standards
published at Microsoft's site. Their opening statement is, "The problem is
that Microsoft Access is using SQL_C_DEFAULT instead of providing an actual
data type during the SQLBindParameter() call." And I think the other
gentleman that replied here did an excellent job of explaining the
implications of what IBM meant when they said, "However, the buffers
Microsoft Access gives DB2 contain SQL_C_CHAR data, i.e. ASCII data."

So, of course now we're reduced to fingerpointing.

Unless you have something else up your sleeve I'm afraid I will have to find
another client-server capable database tool to get this job done.

Thanks,
mr.bill

"Peter Yang [MSFT]" wrote:

> Hello,
>
> From IBM's website, I located a number of articles which discussed the
> issue of Microsoft products having trouble with DB2 Timestamp data, and
> provides recommended configuration switches that may help out. You may
> wish to talk with your DB2 representatives to ensure these have already
> been taken into account in your situation. Take a look at the following:
> vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvv
>
> http://www-1.ibm.com/support/docvie...&q1=%22Microsof
> t+Access%22+%22Times
tamp%22&uid=swg21153006&loc=en_US&cs=utf-8&lang=en
>
> That site describes the issue and recommends the following configuration
> parameters on the DB2 side. :
> vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvvvvvv
vvvvvvvvvvvvvvvv
> vvvvvvvvvvvvvvvv
> Solution The following CLI Configuration Keywords were added to DB2 v8.1
> fixpack 4 as a workaround (depending on the data type of concern):
>
> MapTimeDescribe=1
> MapTimeStampDescribe
=1
> MapDateDescribe=1
> vvvvvvvvvvvvvvvvvvvv
vvvv
>
> Also found a section of CLI Configuration options for the driver. See
>
> http://www-1.ibm.com/support/docvie...&q1=%22Microsof
> t+Access%22+%22Times
tamp%22&uid=swg21179697&loc=en_US&cs=utf-8&lang=en
>
> http://www-1.ibm.com/support/docvie...uid=swg21164225
>
> One of the options is as follows:
> ----------------------
> 24 Reports TIME data as SQL_CHAR data. This patch value is used as a
> workaround for Microsoft Access applications.
>
> Hope this helps.
>
> Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ====================
====================
=============
>
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>
>
>
> --------------------
> | Thread-Topic: errors reading linked table when row contains Timestamp
> | thread-index: AcU/qAoJ6pGcdh/HRMS5/uKKKNUgqw==
> | X-WBNR-Posting-Host: 12.106.254.66
> | From: "=?Utf-8?B?bXIuYmlsbA==?=" < HedoSandwich@newsgro
up.nospam>
> | Subject: errors reading linked table when row contains Timestamp
> | Date: Tue, 12 Apr 2005 14:39:00 -0700
> | Lines: 34
> | Message-ID: <8CE05694-1A83-4D04-9501- 00DD9D5E16A5@microso
ft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.access.conversion
> | Path: TK2MSFTNGXA01.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.conversion:3107
> | NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
> | X-Tomcat-NG: microsoft.public.access.conversion
> |
> | My environment is DB2 database v8.2 on AIX as server, Windows XP with
> MDAC2.8
> | and Access 2002 (Office XP w/SP3) as client.
> |
> | I have a table in the database whose key is [ field1(decimal,16,0)
+
> | field2(char16) + field3(timestamp) ]
> |
> | In DB2, timestamps go to six places to the right of the nearest second
> (e.g.
> | 2004-06-02-13.07.53.123456)
> |
> | Evidently the degree of precision is messing up Access' ability to
> display
> | recordsets from rows in this table if the fractional seconds portion
> contains
> | a number other than Zero in the fourth, fifth, and sixth position.
> That's
> | the ten-thousandths, hundred-thousandths, and millisecond positions if
> I'm
> | not mistaken. For instance, the above example would fail to be
> displayed,
> | but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.
> |
> | Ill-affected rows display as #Deleted in all columns if using Datasheet
> | view, and the resultset of a query cannot be manipulated either as in an
> | Update or Delete.
> |
> | Is there no way to solve this issue? Would upgrading to Access 2003
> help?
> | I don't have this problem in code using ADO, but of course the task I
> have
> | before me requires I build some temporary tables in Access to update the
> data
> | in DB2 with. I shouldn't be spending this much time doing something so
> | simple.
> |
> | About the only other alternative I can see is to create a temporary table
> in
> | DB2 (requires much red tape) and upload my data from Access into it.
> Then I
> | would have to use some other ODBC tool to run the SQL bypassing Access
> | entirely. That doesn't make our ROI look very favorable. Any other
> | suggestions would be very much appreciated.
> |
> | TIA,
> | ~mr.bill
> |
>
>

Peter Yang [MSFT]

2005-04-14, 3:25 am

juvete

2005-11-18, 4:05 pm

Hello,

Please, refer to this forum (in German):

http://www.ruban.de/cgi-bin/yabb/Ya...0795
3134

It works for me (DB2 8.1 ODBC client and DB2 7.1 database on z/OS)
In few words:

1. On the client side, add these two lines in db2cli.ini:
[in Database Section]
MapTimestampDescribe
=1
MapTimestampCDefault
=1

2. On DB2 (7 or 8) for z/OS (OS390) side, apply:
APAR/Fix PQ96188 / UQ96410
APAR/Fix PQ83561/ UQ87586

They also say there could be some errors on z/OS side when running INSERT
statements from MS Access that involve timestamps without microseconds or
with minutes less than 10. I didn't test these issues yet. If you only
read timestamps, it should be fine.

quote:
Originally
posted by mr.bill
My environment is DB2 database v8.2 on AIX as server, Windows XP with MDAC2.8
and Access 2002 (Office XP w/SP3) as client.

I have a table in the database whose key is [ field1(decimal,16,0)
+
field2(char16) + field3(timestamp) ]

In DB2, timestamps go to six places to the right of the nearest second (e.g.
2004-06-02-13.07.53.123456)

Evidently the degree of precision is messing up Access' ability to display
recordsets from rows in this table if the fractional seconds portion contains
a number other than Zero in the fourth, fifth, and sixth position. That's
the ten-thousandths, hundred-thousandths, and millisecond positions if I'm
not mistaken. For instance, the above example would fail to be displayed,
but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.

Ill-affected rows display as #Deleted in all columns if using Datasheet
view, and the resultset of a query cannot be manipulated either as in an
Update or Delete.

Is there no way to solve this issue? Would upgrading to Access 2003 help?
I don't have this problem in code using ADO, but of course the task I have
before me requires I build some temporary tables in Access to update the data
in DB2 with. I shouldn't be spending this much time doing something so
simple.

About the only other alternative I can see is to create a temporary table in
DB2 (requires much red tape) and upload my data from Access into it. Then I
would have to use some other ODBC tool to run the SQL bypassing Access
entirely. That doesn't make our ROI look very favorable. Any other
suggestions would be very much appreciated.

TIA,
~mr.bill

peregenem@jetemail.net

2005-11-21, 7:24 am

John Nurick wrote:
> In principle you could convert the DB2 timestamp into a number
> and store in a Decimal field - but see (allen browne) first.

Alternatively take a reality check <g> and use the DECIMAL data type.

The problem lies not with the data type itself but with the cursor
engine: it can't sort negative DECIMAL values correctly. No big deal
because sorting should *always* be the final operation (proprietary
features which return different resultsets based on the ORDER BY
clause are unrelational - TOP N springs to mind - and should always be
avoided), thus the sorting can be performed successfully in the
middleware e.g. a recordset's Sort property.

I used to think there was another problem: the CDEC() function is
broken in Jet (Q225931) but then I realized the problem is non-existent
because Jet uses the DECIMAL data type natively e.g.

SELECT TYPENAME(0.99)

returns Decimal. That's why if DECIMAL did really live up to the bad
press it gets in these groups we'd all be in trouble. Fortunately, it's
merely unfounded propaganda :)

Sponsored Links