Dates from DB2 displaying as 12:00:00 AM

[Tiana Arylle]

Hi -

I'm building a front end for a DB2 database using Access 2007. One of the
source data fields stores a placeholder date value as
"9999-12-31-23.59.59.999999" (12/31/9999 11:59:59 pm). I need to be able to
run queries on this field and then overwrite the data with the current time.
Access, however, insists on thinking that this data is 12/30/1899 12:00:00
AM. Unfortunately, I do not have the ability to change the default data in
the DB2 source.

Help? Thanks for saving me more hours of frustration!

 

[PieterLinden via AccessMonster.com]

Hi -

I'm building a front end for a DB2 database using Access 2007. One of the
source data fields stores a placeholder date value as
"9999-12-31-23.59.59.999999" (12/31/9999 11:59:59 pm). I need to be able to
run queries on this field and then overwrite the data with the current time.
Access, however, insists on thinking that this data is 12/30/1899 12:00:00
AM. Unfortunately, I do not have the ability to change the default data in
the DB2 source.

Help? Thanks for saving me more hours of frustration!

One way of handling this might be to grab the unique ID of the record(s) you
intend to change and using them in the WHERE clause of your update statement.
Failing that, if the date is stored as a text value in the source DB, you
might be able to query it as a text value.

 

[Jerry Whittle]

12/30/1899 12:00:00 is date/time zero in Access.

Debug.Print CDbl(#12/30/1899 12:00:00#) = 0

Therefore Access is seeing it as 0.

Access can't handle milliseconds so the .999999 might be a problem. Also
#9999-12-31 23.59.59# is the very largest date that Access can handle. Even
adding another secod will cause an error.

How are you connection Access to the DB2 database? Linking tables or ODBC?
You may need a better ODBC driver that can handle dates and time better.

 

[Tiana Arylle]

Thanks for the answers Gentlemen -

In response to Jerry -

I'm using ODBC to connect to the database and linking to the tables I need,
then building all the queries/forms in Access. Cognos will be generating
reports from the DB2, so I'm only concentrating in Access on building forms
for data entry and validation. The ODBC driver available to me (I'm in a
Corporate environment so it's pretty locked down) is the IBM DB2 ODBC Driver,
version 9.01.300.370, dated 11/8/2007. Right now I set it up as a system
DSN, but I'll need to eventually set it up as File DSN so multiple can access
the database without doing individual workstation setups (right...? I'm still
learning about ODBC).

In response to Pieter:

The unique ID for this particular table is a combination of fields, so I've
been making a "dummy" field in my queries that is a concatentation to serve
when I need to find unique records. In the source table, this particular
field is unfortunately a date/time field, not text. Is there a way to
convert the data to text format in a query, so I could then query on that?

Thanks again for your help!
~ Tiana

 

[Mary Chipman [MSFT]]

For the form, you'll probably need to do the conversion to a string
when you load the data since the ODBC driver doesn't appear to handle
it. I ran a search on converting DB2 to VBA, and came up a couple of
good hits, I'm sure there are more:
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html
http://stackoverflow.com/questions/166210/how-to-convert-a-db2-date-time-string-into-an-excel-date

As far as the DSN issue, I'd recommend connecting in code and not
using them at all -- your Access solution will be easier to deploy,
not having to worry about additional files. connectionstrings.com has
the syntax for every flavor of connection string out there.

Once you have the connection string, the VBA/DAO code is
straightforward for linking tables. Here's an example that links a
single table. You can iterate through the TableDefs collection to link
multiple tables, and when the app closes, remove the linked TableDefs
if you choose, relinking again on startup.

Public Function LinkTableDAO( _
strLinkName As String, _
strDBName As String, _
strTableName As String, _
strConnectionString As String) As Boolean

' Links or re-links a single table.
' Returns True or False based on Err value.

Dim db As DAO.Database
Dim tdf As DAO.TableDef

On Error Resume Next
Set db = CurrentDb

' Check to see if the table link already exists;
' if so, delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
db.TableDefs.Delete strLinkName
db.TableDefs.Refresh
Else
' Ignore error and reset
Err.Number = 0
End If

Set tdf = db.CreateTableDef(strLinkName)
' Set the Connect and SourceTableName
' properties to establish the link
tdf.Connect = strConnectionString
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
db.TableDefs.Append tdf

LinkTableDAO = (Err = 0)
End Function

--Mary