Thread: RE: Wierd ODBC access issue
Hello,
You can't alter table design from access using linked tables, if
thats what you meant by not being able to update columns. You say you can
only add/update/delete records, if you can't read them this could be a user
permissions thing on PG.
If you're co-worker is going through ODBC then it's possible s/he
has still has the read only option checked (there's the driver and data
source to uncheck). It could also be user permissions on PG (theres select,
update/delete, and insert rights). I've found PGAdmin handy as a windows gui
for PG
(http://www.greatbridge.org/project/pgadmin/projdisplay.php )
- Stuart
> -----Original Message-----
> From: radius [SMTP:radius@codegrinder.com]
> Sent: Friday, May 25, 2001 5:31 PM
> To: pgsql-odbc@postgresql.org
> Subject: Wierd ODBC access issue
>
>
> i've got an odd situation to where i can connect w/ access via odbc to my
> database .. but I can only add/update/delete records . not columns..
>
> a coworker at a remote location can only read the table?
>
> are there any permissions on the pgsql side that govern this?
>
> if anyone could give a suggestion of what to look at i'd appreciate it
> alot.. thanks.. cause i have no clue..
>
> -jason
>
is there anyway to be able to alter table design from within access? thanks.. -jason On Tue, 29 May 2001, Henshall, Stuart - WCP wrote: > Hello, > You can't alter table design from access using linked tables, if > thats what you meant by not being able to update columns. You say you can > only add/update/delete records, if you can't read them this could be a user > permissions thing on PG. > If you're co-worker is going through ODBC then it's possible s/he > has still has the read only option checked (there's the driver and data > source to uncheck). It could also be user permissions on PG (theres select, > update/delete, and insert rights). I've found PGAdmin handy as a windows gui > for PG > (http://www.greatbridge.org/project/pgadmin/projdisplay.php ) > - Stuart > > > -----Original Message----- > > From: radius [SMTP:radius@codegrinder.com] > > Sent: Friday, May 25, 2001 5:31 PM > > To: pgsql-odbc@postgresql.org > > Subject: Wierd ODBC access issue > > > > > > i've got an odd situation to where i can connect w/ access via odbc to my > > database .. but I can only add/update/delete records . not columns.. > > > > a coworker at a remote location can only read the table? > > > > are there any permissions on the pgsql side that govern this? > > > > if anyone could give a suggestion of what to look at i'd appreciate it > > alot.. thanks.. cause i have no clue.. > > > > -jason > > >
You could use pass through queries, but the editing of table design
doesn't seem to be one of PG's hotpoints. Generally its easier to drop and
recreate (which you can do using pass-throught queries. If you want to
preserve data then you'd have to create the new table, insert stuff from the
old. drop the old and then rename the new table (that should work, although
it might lead to problems with pgsql user functions that reference the
table).
The way I normally set my tables, etc.. up is to use a psql script
(I have cygwin installed on my development machine). I've just had a thought
about you're write problem. You're not using ADO recordsets are you? MS
seems to be trying to push this but only SQL Server using datashape seems
able to use ADO from MS Access (I use DAO, the normal default for MS
Access).
For a slightly easier way of designing tables there's PGAdmin on
windows and PG-Access in Linux (using tcl).
I think there is a FAQ on http://techdocs.postgresql.org about MS
Access and one on odbc on http://odbc.postgresql.org
You might just have to play around to find settings that work (I'm
assuming that you're using 7.01.0005 of the odbc driver). (Quick note I tend
to use row versioning as this seems to help MS Access not get confused about
wether someone else has edited a row or not).
- Stuart
P.S. if you use pass through queries for getting recordsets then they will
be dbsnapshot.
> -----Original Message-----
> From: radius [SMTP:radius@codegrinder.com]
> Sent: Tuesday, May 29, 2001 1:12 PM
> To: Henshall, Stuart - WCP
> Cc: pgsql-odbc@postgresql.org
> Subject: RE: Wierd ODBC access issue
>
> is there anyway to be able to alter table design from within access?
> thanks..
>
> -jason
>
> On Tue, 29 May 2001, Henshall, Stuart - WCP wrote:
>
> > Hello,
> > You can't alter table design from access using linked tables, if
> > thats what you meant by not being able to update columns. You say you
> can
> > only add/update/delete records, if you can't read them this could be a
> user
> > permissions thing on PG.
> > If you're co-worker is going through ODBC then it's possible s/he
> > has still has the read only option checked (there's the driver and data
> > source to uncheck). It could also be user permissions on PG (theres
> select,
> > update/delete, and insert rights). I've found PGAdmin handy as a windows
> gui
> > for PG
> > (http://www.greatbridge.org/project/pgadmin/projdisplay.php )
> > - Stuart
> >
> > > -----Original Message-----
> > > From: radius [SMTP:radius@codegrinder.com]
> > > Sent: Friday, May 25, 2001 5:31 PM
> > > To: pgsql-odbc@postgresql.org
> > > Subject: Wierd ODBC access issue
> > >
> > >
> > > i've got an odd situation to where i can connect w/ access via odbc to
> my
> > > database .. but I can only add/update/delete records . not columns..
> > >
> > > a coworker at a remote location can only read the table?
> > >
> > > are there any permissions on the pgsql side that govern this?
> > >
> > > if anyone could give a suggestion of what to look at i'd appreciate it
> > > alot.. thanks.. cause i have no clue..
> > >
> > > -jason
> > >
> >
thanks for the tips.. i've used pgadmin and that uses odbc as well doesn't it? oh .. an i have the write issues taken care of, i had to turn on recognize unique indexes -jason On Tue, 29 May 2001, Henshall, Stuart - WCP wrote: > You could use pass through queries, but the editing of table design > doesn't seem to be one of PG's hotpoints. Generally its easier to drop and > recreate (which you can do using pass-throught queries. If you want to > preserve data then you'd have to create the new table, insert stuff from the > old. drop the old and then rename the new table (that should work, although > it might lead to problems with pgsql user functions that reference the > table). > The way I normally set my tables, etc.. up is to use a psql script > (I have cygwin installed on my development machine). I've just had a thought > about you're write problem. You're not using ADO recordsets are you? MS > seems to be trying to push this but only SQL Server using datashape seems > able to use ADO from MS Access (I use DAO, the normal default for MS > Access). > For a slightly easier way of designing tables there's PGAdmin on > windows and PG-Access in Linux (using tcl). > I think there is a FAQ on http://techdocs.postgresql.org about MS > Access and one on odbc on http://odbc.postgresql.org > You might just have to play around to find settings that work (I'm > assuming that you're using 7.01.0005 of the odbc driver). (Quick note I tend > to use row versioning as this seems to help MS Access not get confused about > wether someone else has edited a row or not). > - Stuart > P.S. if you use pass through queries for getting recordsets then they will > be dbsnapshot. > > > -----Original Message----- > > From: radius [SMTP:radius@codegrinder.com] > > Sent: Tuesday, May 29, 2001 1:12 PM > > To: Henshall, Stuart - WCP > > Cc: pgsql-odbc@postgresql.org > > Subject: RE: Wierd ODBC access issue > > > > is there anyway to be able to alter table design from within access? > > thanks.. > > > > -jason > > > > On Tue, 29 May 2001, Henshall, Stuart - WCP wrote: > > > > > Hello, > > > You can't alter table design from access using linked tables, if > > > thats what you meant by not being able to update columns. You say you > > can > > > only add/update/delete records, if you can't read them this could be a > > user > > > permissions thing on PG. > > > If you're co-worker is going through ODBC then it's possible s/he > > > has still has the read only option checked (there's the driver and data > > > source to uncheck). It could also be user permissions on PG (theres > > select, > > > update/delete, and insert rights). I've found PGAdmin handy as a windows > > gui > > > for PG > > > (http://www.greatbridge.org/project/pgadmin/projdisplay.php ) > > > - Stuart > > > > > > > -----Original Message----- > > > > From: radius [SMTP:radius@codegrinder.com] > > > > Sent: Friday, May 25, 2001 5:31 PM > > > > To: pgsql-odbc@postgresql.org > > > > Subject: Wierd ODBC access issue > > > > > > > > > > > > i've got an odd situation to where i can connect w/ access via odbc to > > my > > > > database .. but I can only add/update/delete records . not columns.. > > > > > > > > a coworker at a remote location can only read the table? > > > > > > > > are there any permissions on the pgsql side that govern this? > > > > > > > > if anyone could give a suggestion of what to look at i'd appreciate it > > > > alot.. thanks.. cause i have no clue.. > > > > > > > > -jason > > > > > > > >