pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedMS Access & PsqlODBC: Invalid field name 'name'
8+ messages / 4 participants
[nested] [flat]
* MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 10:49 Ewan Mellor <em@nexus.co.uk>
0 siblings, 2 replies; 8+ messages in thread
From: Ewan Mellor @ 1998-05-12 10:49 UTC (permalink / raw)
To: pgsql-interfaces
I am using Byron's ODBC driver (version .0239 downloaded today) with
Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
into Access. Most tables work fine, but any field with the name 'name'
or 'sortname' or even 'garbagename' may not be used as part of an
index. This occurs whether the index is picked up automatically by the
driver, or if you are asked to choose a unique field by Access.
Can this be fixed? As a workaround, could the driver optionally not
tell the client application about indices? In this way, I could tell
Access to ignore them and then (I think) I would be able to get at my
data.
Ewan Mellor.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 14:34 Byron Nikolaidis <byronn@insightdist.com>
parent: Ewan Mellor <em@nexus.co.uk>
1 sibling, 1 reply; 8+ messages in thread
From: Byron Nikolaidis @ 1998-05-12 14:34 UTC (permalink / raw)
To: Ewan Mellor <em@nexus.co.uk>; +Cc: pgsql-interfaces
Set the "Recognize Unique Indexes" to disabled (unchecked).
Then, when Access asks you for a unique field, don't select anything and
hit ok.
Thus, you are telling access you have no index.
This should allow you to get at your data until we figure out what "name"
has to do with this problem.
Byron
Ewan Mellor wrote:
> I am using Byron's ODBC driver (version .0239 downloaded today) with
> Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
> into Access. Most tables work fine, but any field with the name 'name'
> or 'sortname' or even 'garbagename' may not be used as part of an
> index. This occurs whether the index is picked up automatically by the
> driver, or if you are asked to choose a unique field by Access.
>
> Can this be fixed? As a workaround, could the driver optionally not
> tell the client application about indices? In this way, I could tell
> Access to ignore them and then (I think) I would be able to get at my
> data.
>
> Ewan Mellor.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 14:53 Hannu Krosing <hannu@trust.ee>
parent: Ewan Mellor <em@nexus.co.uk>
1 sibling, 1 reply; 8+ messages in thread
From: Hannu Krosing @ 1998-05-12 14:53 UTC (permalink / raw)
To: Ewan Mellor <em@nexus.co.uk>; +Cc: pgsql-interfaces
Ewan Mellor wrote:
>
> I am using Byron's ODBC driver (version .0239 downloaded today) with
> Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
> into Access. Most tables work fine, but any field with the name 'name'
> or 'sortname' or even 'garbagename' may not be used as part of an
> index. This occurs whether the index is picked up automatically by the
> driver, or if you are asked to choose a unique field by Access.
Starting from v2.0 of Access the worn "name" became kind of reserved
word in Access, as the table itself aquired an _attribute_ name, which
contains the name of the table.
so having a field called name is a problem in anyway (for Access).
I have no idea why "sortname" or "garbagename" does not work.
> Can this be fixed? As a workaround, could the driver optionally not
> tell the client application about indices? In this way, I could tell
> Access to ignore them and then (I think) I would be able to get at my
> data.
you can still do
ALTER TABLE yourtable RENAME name TO not_name_any_more;
-------------
Hannu
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 16:08 Ewan Mellor <em@nexus.co.uk>
parent: Hannu Krosing <hannu@trust.ee>
0 siblings, 1 reply; 8+ messages in thread
From: Ewan Mellor @ 1998-05-12 16:08 UTC (permalink / raw)
To: pgsql-interfaces
Hannu Krosing wrote:
>
> Ewan Mellor wrote:
> >
> > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
> > into Access. Most tables work fine, but any field with the name 'name'
> > or 'sortname' or even 'garbagename' may not be used as part of an
> > index. This occurs whether the index is picked up automatically by the
> > driver, or if you are asked to choose a unique field by Access.
>
> Starting from v2.0 of Access the worn "name" became kind of reserved
> word in Access, as the table itself aquired an _attribute_ name, which
> contains the name of the table.
>
> so having a field called name is a problem in anyway (for Access).
So it's a "we're Microsoft and we can do what we want" reserved word,
and not an "internationally recognised standard SQL" reserved word :-(
> I have no idea why "sortname" or "garbagename" does not work.
>
> > Can this be fixed? As a workaround, could the driver optionally not
> > tell the client application about indices? In this way, I could tell
> > Access to ignore them and then (I think) I would be able to get at my
> > data.
>
> you can still do
>
> ALTER TABLE yourtable RENAME name TO not_name_any_more;
I'd rather not - there is a reasonable amount of code sitting on top of
this DB :-(
Thanks a lot for your help,
Ewan.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 16:24 Ewan Mellor <em@nexus.co.uk>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 1 reply; 8+ messages in thread
From: Ewan Mellor @ 1998-05-12 16:24 UTC (permalink / raw)
To: pgsql-interfaces
Byron Nikolaidis wrote:
>
> Set the "Recognize Unique Indexes" to disabled (unchecked).
> Then, when Access asks you for a unique field, don't select anything and
> hit ok.
> Thus, you are telling access you have no index.
>
> This should allow you to get at your data until we figure out what "name"
> has to do with this problem.
I tried that, but it does not seem to help. Perhaps Access is asking
for the index information of its own accord?
I have just discovered it also objects to:
rt_url,
url,
genre,
name_en,
stored,
stored1,
address, and
server.
Note that many have succeeded. Curiouser and curiouser...
Thanks, Byron, both for your help and for what looks like it will be a
really useful driver. If only the whole planet didn't use Access...
> Byron
>
> Ewan Mellor wrote:
>
> > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
> > into Access. Most tables work fine, but any field with the name 'name'
> > or 'sortname' or even 'garbagename' may not be used as part of an
> > index. This occurs whether the index is picked up automatically by the
> > driver, or if you are asked to choose a unique field by Access.
> >
> > Can this be fixed? As a workaround, could the driver optionally not
> > tell the client application about indices? In this way, I could tell
> > Access to ignore them and then (I think) I would be able to get at my
> > data.
> >
> > Ewan Mellor.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 17:00 Byron Nikolaidis <byronn@insightdist.com>
parent: Ewan Mellor <em@nexus.co.uk>
0 siblings, 1 reply; 8+ messages in thread
From: Byron Nikolaidis @ 1998-05-12 17:00 UTC (permalink / raw)
To: Ewan Mellor <em@nexus.co.uk>; +Cc: pgsql-interfaces
Ewan Mellor wrote:
> Byron Nikolaidis wrote:
> >
> > Set the "Recognize Unique Indexes" to disabled (unchecked).
> > Then, when Access asks you for a unique field, don't select anything and
> > hit ok.
> > Thus, you are telling access you have no index.
> >
> > This should allow you to get at your data until we figure out what "name"
> > has to do with this problem.
>
> I tried that, but it does not seem to help. Perhaps Access is asking
> for the index information of its own accord?
>
> I have just discovered it also objects to:
>
> rt_url,
> url,
> genre,
> name_en,
> stored,
> stored1,
> address, and
> server.
>
Wait, I think I have it!
Access will not allow you to index on LongVarchar data types OR character types
that are longer than 254 characters (255 with null). I bet these columns you
are having trouble with are Postgres TEXT types or varchars/chars that are over
254.
Check out the odbc driver setup options dialog. You can map TEXT fields to
plain varchar; then set the LongVarChar size to 254, and it should work!
Byron
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-12 18:17 Ewan Mellor <em@nexus.co.uk>
parent: Byron Nikolaidis <byronn@insightdist.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ewan Mellor @ 1998-05-12 18:17 UTC (permalink / raw)
To: pgsql-interfaces
Byron Nikolaidis wrote:
>
> Ewan Mellor wrote:
>
> > Byron Nikolaidis wrote:
> > >
> > > Set the "Recognize Unique Indexes" to disabled (unchecked).
> > > Then, when Access asks you for a unique field, don't select anything and
> > > hit ok.
> > > Thus, you are telling access you have no index.
> > >
> > > This should allow you to get at your data until we figure out what "name"
> > > has to do with this problem.
> >
> > I tried that, but it does not seem to help. Perhaps Access is asking
> > for the index information of its own accord?
> >
> > I have just discovered it also objects to:
> >
> > rt_url,
> > url,
> > genre,
> > name_en,
> > stored,
> > stored1,
> > address, and
> > server.
> >
>
> Wait, I think I have it!
>
> Access will not allow you to index on LongVarchar data types OR character types
> that are longer than 254 characters (255 with null). I bet these columns you
> are having trouble with are Postgres TEXT types or varchars/chars that are over
> 254.
>
> Check out the odbc driver setup options dialog. You can map TEXT fields to
> plain varchar; then set the LongVarChar size to 254, and it should work!
Well done indeed! With that you have elevated yourself to the higher
echelons of gurudom. Congratulations. :-)
One for the FAQ methinks...
Ewan.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: [INTERFACES] MS Access & PsqlODBC: Invalid field name 'name'
@ 1998-05-13 10:13 Jose' Soares Da Silva <sferac@bo.nettuno.it>
parent: Ewan Mellor <em@nexus.co.uk>
0 siblings, 0 replies; 8+ messages in thread
From: Jose' Soares Da Silva @ 1998-05-13 10:13 UTC (permalink / raw)
To: Ewan Mellor <em@nexus.co.uk>; +Cc: pgsql-interfaces
On Tue, 12 May 1998, Ewan Mellor wrote:
> Hannu Krosing wrote:
> >
> > Ewan Mellor wrote:
> > >
> > > I am using Byron's ODBC driver (version .0239 downloaded today) with
> > > Access 7.00 under Win95 with Postgres 6.3. I am trying to link tables
> > > into Access. Most tables work fine, but any field with the name 'name'
> > > or 'sortname' or even 'garbagename' may not be used as part of an
> > > index. This occurs whether the index is picked up automatically by the
> > > driver, or if you are asked to choose a unique field by Access.
> >
I'm using Byron's ODBC v6.30.0238 with M$-Access-97 under Win95 with
PostgreSQL v6.3. I can successful link tables into M$-Access even if they have
the word 'name' as column name or table name.
M$-Access picked column 'name' as unique index and it seems work. I can
read and write data into may table named 'gname'.
this is my example:
odbc=> create table gname ( name name, pname int);
CREATE
odbc=> insert into gname values ( 'name',1234);
INSERT 528554 1
odbc=> select * from gname;
name|pname
----+-----
name| 1234
(1 row)
odbc=> \d gname
Table = gname
+-------------------------------+----------------------------------+-------+
| Field | Type | Length|
+-------------------------------+----------------------------------+-------+
| name | name | 32 |
| pname | int4 | 4 |
+-------------------------------+----------------------------------+-------+
Jose'
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~1998-05-13 10:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-05-12 10:49 MS Access & PsqlODBC: Invalid field name 'name' Ewan Mellor <em@nexus.co.uk>
1998-05-12 14:34 ` Byron Nikolaidis <byronn@insightdist.com>
1998-05-12 16:24 ` Ewan Mellor <em@nexus.co.uk>
1998-05-12 17:00 ` Byron Nikolaidis <byronn@insightdist.com>
1998-05-12 18:17 ` Ewan Mellor <em@nexus.co.uk>
1998-05-12 14:53 ` Hannu Krosing <hannu@trust.ee>
1998-05-12 16:08 ` Ewan Mellor <em@nexus.co.uk>
1998-05-13 10:13 ` Jose' Soares Da Silva <sferac@bo.nettuno.it>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox