int8 vs text in odbc link

Hi all!

I'm accessing PostgreSQL 7.0.2 from MS Access 97 via
the ODBC driver. I can successfully link and query
tables. Unfortunately, all of the columns of the data
type int8 are interpreted as text in MS Access 97 when
you look at the design view of the table. Dates and
float8 seem to be ok.

1. Has anyone else encountered this?
2. Does anyone know of a solution?

In a similar vein....Does anyone know how to make MSAccess export a table to
Postgresql with fixed char length fields? I always get VARCHARs and for
some reason I can't do a join between a table that has a varchar and fixed
char. The system runs until it eventually runs out of memory. This means
that I have to export the table as is to Postgres, do a pg_dump on the
table, modify the CREATE TABLE definition to be CHAR(9), drop the table,
then re-import it with psql. If I could get MSAccess to put out a fixed
length field (which is how I've defined the table) I could avoid all of
this.

Len Morgan

Andrew Gould <andrewgould@yahoo.com> writes:

Unfortunately, all of the columns of the data
type int8 are interpreted as text in MS Access 97 when
you look at the design view of the table.

Offhand it looks like our ODBC driver will report the type of an int8
field as "SQL_BIGINT" (-5), which may or may not be a standard ODBC
type code --- and even if it is, Access might not know it. Anybody
know?

regards, tom lane

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Gould" <andrewgould@yahoo.com>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, October 15, 2000 9:12 PM
Subject: Re: [GENERAL] int8 vs text in odbc link

Andrew Gould <andrewgould@yahoo.com> writes:

Unfortunately, all of the columns of the data
type int8 are interpreted as text in MS Access 97 when
you look at the design view of the table.

Offhand it looks like our ODBC driver will report the type of an int8
field as "SQL_BIGINT" (-5), which may or may not be a standard ODBC
type code --- and even if it is, Access might not know it. Anybody
know?

Access 97 has Integer as 16-bit and Long Integer as 32-bit - those are the
only options available AFAICT.

- Richard Huxton

Thanks to everyone who responded. I created a table
with int4 fields; and they appear as long integers in
MS Access.

After a review of the data fields, I've decided I
don't need int8 fields. Last night I dumped the
schema of the database, changed int8 fields to int4;
and moved the data to new tables. The process was not
nearly as painful as I thought it would be. I'm
currently creating unique indexes. At various points,
I used pg_dump, a line of perl (thanks again,
Dominic), psql -c, copy, and \i. All-in-all, it
turned out to be a good exercise for this newbie.
Although, my brain is still a little tired.

Thanks again,

Andrew Gould

--- Richard Huxton <dev@archonet.com> wrote:

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Gould" <andrewgould@yahoo.com>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, October 15, 2000 9:12 PM
Subject: Re: [GENERAL] int8 vs text in odbc link

Andrew Gould <andrewgould@yahoo.com> writes:

Unfortunately, all of the columns of the data
type int8 are interpreted as text in MS Access

97 when

you look at the design view of the table.

Offhand it looks like our ODBC driver will report

the type of an int8

field as "SQL_BIGINT" (-5), which may or may not

be a standard ODBC

type code --- and even if it is, Access might not

know it. Anybody

know?

Access 97 has Integer as 16-bit and Long Integer as
32-bit - those are the
only options available AFAICT.

- Richard Huxton

__________________________________________________
Do You Yahoo!?
Yahoo! Messenger - Talk while you surf! It's FREE.
http://im.yahoo.com/