Thread: Incorrect import of timestamp fields in MS Access with 7.2.x
Hi, After switching from PostgreSQL 7.1.3 to 7.2, I'm having trouble with timestamp fields in ODBC-linked tables in MS Access (both '97 and XP). The problem is that MS Access somehow thinks these are 'Text' fields instead of 'Date/Time' fields. It worked fine when using the 7.1.3 server, but not with 7.2 and 7.2.1. I tried different ODBC driver versions, but 7.1.0007, 7.1.0010 and 7.2.0001 all show the same problem. As this seems to be an ODBC driver related problem, I hope this question is appropriate on this mailing list. If not, feel free to direct me to another one. Edwin
Edwin Woudt wrote: > > Hi, > > After switching from PostgreSQL 7.1.3 to 7.2, I'm having trouble with > timestamp fields in ODBC-linked tables in MS Access (both '97 and XP). > > The problem is that MS Access somehow thinks these are 'Text' fields > instead of 'Date/Time' fields. It worked fine when using the 7.1.3 server, > but not with 7.2 and 7.2.1. What kind of problem do you have concretely ? regards, Hiroshi Inoue
Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>> After switching from PostgreSQL 7.1.3 to 7.2, I'm having trouble with
>> timestamp fields in ODBC-linked tables in MS Access (both '97 and XP).
>>
>> The problem is that MS Access somehow thinks these are 'Text' fields
>> instead of 'Date/Time' fields. It worked fine when using the 7.1.3
>> server, but not with 7.2 and 7.2.1.
>
> What kind of problem do you have concretely ?
Apologies if I was not clear.
I want MS Access to recognize the timestamp field as a Date/Time field and
not as a Text field. This has some advantages as access can then handle
formatting and input itself.
I did some more debugging and found in de psqlodbc log file that in 7.1.3
the field has type 1184 (PG_TYPE_DATETIME), while in 7.2.x the field has
type 1114 (PG_TYPE_TIMESTAMP_NO_TMZONE).
This led me to the untested patch below against pgtypes.c version 1.46. I'd
be willing to test this myself, if only I knew how to build the driver.
Edwin
--- pgtypes.c.orig Tue Apr 16 13:06:36 2002
+++ pgtypes.c Tue Apr 16 13:08:25 2002
@@ -311,10 +311,11 @@
#endif /* ODBCVER */
return SQL_TIME;
case PG_TYPE_ABSTIME:
case PG_TYPE_DATETIME:
case PG_TYPE_TIMESTAMP:
+ case PG_TYPE_TIMESTAMP_NO_TMZONE:
#if (ODBCVER >= 0x0300)
if (EN_is_odbc3(env))
return SQL_TYPE_TIMESTAMP;
#endif /* ODBCVER */
return SQL_TIMESTAMP;
Edwin Woudt wrote:
>
> Hiroshi Inoue <Inoue@tpf.co.jp> wrote:
>
> >> After switching from PostgreSQL 7.1.3 to 7.2, I'm having trouble with
> >> timestamp fields in ODBC-linked tables in MS Access (both '97 and XP).
> >>
> >> The problem is that MS Access somehow thinks these are 'Text' fields
> >> instead of 'Date/Time' fields. It worked fine when using the 7.1.3
> >> server, but not with 7.2 and 7.2.1.
> >
> > What kind of problem do you have concretely ?
>
> Apologies if I was not clear.
>
> I want MS Access to recognize the timestamp field as a Date/Time field and
> not as a Text field. This has some advantages as access can then handle
> formatting and input itself.
>
> I did some more debugging and found in de psqlodbc log file that in 7.1.3
> the field has type 1184 (PG_TYPE_DATETIME), while in 7.2.x the field has
> type 1114 (PG_TYPE_TIMESTAMP_NO_TMZONE).
Oh I see. I would fix it.
> --- pgtypes.c.orig Tue Apr 16 13:06:36 2002
> +++ pgtypes.c Tue Apr 16 13:08:25 2002
> @@ -311,10 +311,11 @@
> #endif /* ODBCVER */
> return SQL_TIME;
> case PG_TYPE_ABSTIME:
> case PG_TYPE_DATETIME:
> case PG_TYPE_TIMESTAMP:
> + case PG_TYPE_TIMESTAMP_NO_TMZONE:
> #if (ODBCVER >= 0x0300)
> if (EN_is_odbc3(env))
> return SQL_TYPE_TIMESTAMP;
> #endif /* ODBCVER */
> return SQL_TIMESTAMP;
I'm suspicious if the above is sufficeient.
regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/
I wrote: > > I did some more debugging and found in de psqlodbc log file that in 7.1.3 > > the field has type 1184 (PG_TYPE_DATETIME), while in 7.2.x the field has > > type 1114 (PG_TYPE_TIMESTAMP_NO_TMZONE). > > Oh I see. I would fix it. Try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/. regards, Hiroshi Inoue http://w2422.nsk.ne.jp/~inoue/
>>> I did some more debugging and found in de psqlodbc log file that in >>> 7.1.3 the field has type 1184 (PG_TYPE_DATETIME), while in 7.2.x the >>> field has type 1114 (PG_TYPE_TIMESTAMP_NO_TMZONE). >> >> Oh I see. I would fix it. > > Try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/. Thanks. That fixed the problem. Edwin