Thread: No Current Record
I am experiencing a very frustrating problem with postgresql and access 2000. I have a postgresql 7.2.4-5.80 database and am accessing it using access 2000. Tables are linked using odbc. I can read a table. I can add records to a table. I can edit and delete existing records in the table, but I can not edit or delete records in the table that were added using the odbc connection. By this I mean that records that are imported into the postgresql (from a dump from pg_dump) can be edited or deleted, but if I add a record from access, I can't edit or delete from access. From within psql the records look identical. The error message I get is No Current Record. Guy Steven
Troubleshoot how Access sees your table. In Access, choose to view the design of your table and ignore messages about that you will not be able to change things on a linked table. Are the fields what you think they should be numeric and character. Does your table have a primary key? It should. Is the postgres primary key float or int8 or serial8? That would be bad. Access/ODBC behaves best when your fields are int (int4) or serial (serial4). There is some indication that adding a timestamp field with a time of NOW() will allow Access to determine its (client) view of key matches the Postgres servers view of the key and match records for updating, etc. -- otherwise you tend to get non-updatable recordsets. There is some indication that tables that HAVE record OIDs help maintain the client-server key connection, avoiding the "no current record problems". I am a littles surprised at the ability to delete. Access tends to use a syntax of 'DELETE * FROM table' where Postgres insist on 'DELETE FROM table' with no *. I have to use pass-throughs to delete records. I not sure any of this will help but I figure it is worth a shot. Guy Steven wrote: > > I am experiencing a very frustrating problem with postgresql and access > 2000. > > I have a postgresql 7.2.4-5.80 database and am accessing it using access > 2000. > Tables are linked using odbc. > > I can read a table. I can add records to a table. I can edit and delete > existing records in the table, but I can not edit or delete records in the > table that were added using the odbc connection. By this I mean that records > that are imported into the postgresql (from a dump from pg_dump) can be > edited or deleted, but if I add a record from access, I can't edit or delete > from access. > From within psql the records look identical. > The error message I get is No Current Record. > > Guy Steven > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
--- Greg Campbell <greg.campbell@us.michelin.com> wrote: > Troubleshoot how Access sees your table. > In Access, choose to view the design of your table > and ignore messages > about that you will not be able to change things on > a linked table. Are > the fields what you think they should be numeric and > character. > > Does your table have a primary key? It should. > Is the postgres primary key float or int8 or > serial8? That would be bad. > Access/ODBC behaves best when your fields are int > (int4) or serial > (serial4). There is some indication that adding a > timestamp field with > a time of NOW() will allow Access to determine its > (client) view of key > matches the Postgres servers view of the key and > match records for > updating, etc. -- otherwise you tend to get > non-updatable recordsets. If you use "timestamp" for any data field that will be used by Access, be sure to make it "timestamp(0)", i.e. no fractional seconds: Access doesn't like them. > > There is some indication that tables that HAVE > record OIDs help maintain > the client-server key connection, avoiding the "no > current record > problems". Make sure that you have "row versioning" checked in the ODBC driver options for your DSN. > > I am a littles surprised at the ability to delete. > Access tends to use a > syntax of 'DELETE * FROM table' where Postgres > insist on 'DELETE FROM > table' with no *. I have to use pass-throughs to > delete records. I am more than a little surprised at this. For me, Access accepts the "delete from table" syntax just fine. > > I not sure any of this will help but I figure it is > worth a shot. > > > Guy Steven wrote: > > > > I am experiencing a very frustrating problem with > postgresql and access > > 2000. > > > > I have a postgresql 7.2.4-5.80 database and am > accessing it using access > > 2000. > > Tables are linked using odbc. > > > > I can read a table. I can add records to a table. > I can edit and delete > > existing records in the table, but I can not edit > or delete records in the > > table that were added using the odbc connection. > By this I mean that records > > that are imported into the postgresql (from a dump > from pg_dump) can be > > edited or deleted, but if I add a record from > access, I can't edit or delete > > from access. > > From within psql the records look identical. > > The error message I get is No Current Record. I've never seen this error. I suggest searching in the MS Knowledge Base and on Access groups, using the error string, to get more information about what triggers that error. FWIW, most of us find that Access and PostgreSQL get along just fine, subject to a small number of gotchas which are fairly easily worked around (check the ODBC driver site on http://gborg.postgresql.org for more information). If you don't find a solution based on the above, please post your ODBC driver settings, along with the table definitions for the tables that are giving you problems. __________________________________ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus
This is really starting to confuse me. The primary key in postgres was on a field defined as an integer. access viewed this as a long integer. my understanding is that these both correspond to int4, so that should be fine. I added another column, using timestamp, to extend the primary key. This has made no difference, although in doing so I noted that the behaviour I am having trouble with only occurs if the new record is added using the form. If I add a record using the datasheet view of the table, I can do what I like with it, either in datasheet view or through a form. If however the record is added using a form, (this is a bound form using the linked table as its datasource), then problems arise. Guy Steven -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Greg Campbell Sent: Friday, 9 January 2004 3:41 a.m. To: Guy Steven Cc: pgsql-odbc@postgresql.org Subject: Re: [ODBC] No Current Record Troubleshoot how Access sees your table. In Access, choose to view the design of your table and ignore messages about that you will not be able to change things on a linked table. Are the fields what you think they should be numeric and character. Does your table have a primary key? It should. Is the postgres primary key float or int8 or serial8? That would be bad. Access/ODBC behaves best when your fields are int (int4) or serial (serial4). There is some indication that adding a timestamp field with a time of NOW() will allow Access to determine its (client) view of key matches the Postgres servers view of the key and match records for updating, etc. -- otherwise you tend to get non-updatable recordsets. There is some indication that tables that HAVE record OIDs help maintain the client-server key connection, avoiding the "no current record problems". I am a littles surprised at the ability to delete. Access tends to use a syntax of 'DELETE * FROM table' where Postgres insist on 'DELETE FROM table' with no *. I have to use pass-throughs to delete records. I not sure any of this will help but I figure it is worth a shot. Guy Steven wrote: > > I am experiencing a very frustrating problem with postgresql and access > 2000. > > I have a postgresql 7.2.4-5.80 database and am accessing it using access > 2000. > Tables are linked using odbc. > > I can read a table. I can add records to a table. I can edit and delete > existing records in the table, but I can not edit or delete records in the > table that were added using the odbc connection. By this I mean that records > that are imported into the postgresql (from a dump from pg_dump) can be > edited or deleted, but if I add a record from access, I can't edit or delete > from access. > From within psql the records look identical. > The error message I get is No Current Record. > > Guy Steven > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
I built a test for the condition you stated. Mine works fine. My table has a primary key of int4. There are no int8 fields. There are no var fields of unspecified maximum size. There are no text and no blobs. I do not believe that the timestamp field needs to be a part of the primary key. Your problem sounds like when you are using a form bound to a linked Postgre table, it is using the ADODB Snapshot mode instead of a Dynaset mode, and not letting you keep cursor synchronized with your own changes. Let me correct myself. In snapshots our client cannot see the changes on the source made by others. In dynaset, our client gets updates of what other clients are doing. We should always see our own changes. Of course we do need to synchronize to the PostgreSQL table. I do not know of a setting to affect this type of snapshot/dynset thing with an Access form. I do know that my PostgreSQL ODBC settings has UPDATEABLE CURSOR checked and I now avoid int8's like the plague. Does the Postgres table have OIDs? The form is not append only mode? Would running a REQUERY macro command after an update change things? I agree with what Jeff Eckermann posted yesterday. Perhaps you should post your Postgre table schema SQL and key ODBC settings. Guy Steven wrote: > > This is really starting to confuse me. > > The primary key in postgres was on a field defined as an integer. > access viewed this as a long integer. my understanding is that these both > correspond to int4, so that should be fine. > I added another column, using timestamp, to extend the primary key. This has > made no difference, although in doing so I noted that the behaviour I am > having trouble with only occurs if the new record is added using the form. > If I add a record using the datasheet view of the table, I can do what I > like with it, either in datasheet view or through a form. If however the > record is added using a form, (this is a bound form using the linked table > as its datasource), then problems arise. > > Guy Steven > > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Greg Campbell > Sent: Friday, 9 January 2004 3:41 a.m. > To: Guy Steven > Cc: pgsql-odbc@postgresql.org > Subject: Re: [ODBC] No Current Record > > Troubleshoot how Access sees your table. > In Access, choose to view the design of your table and ignore messages > about that you will not be able to change things on a linked table. Are > the fields what you think they should be numeric and character. > > Does your table have a primary key? It should. > Is the postgres primary key float or int8 or serial8? That would be bad. > Access/ODBC behaves best when your fields are int (int4) or serial > (serial4). There is some indication that adding a timestamp field with > a time of NOW() will allow Access to determine its (client) view of key > matches the Postgres servers view of the key and match records for > updating, etc. -- otherwise you tend to get non-updatable recordsets. > > There is some indication that tables that HAVE record OIDs help maintain > the client-server key connection, avoiding the "no current record > problems". > > I am a littles surprised at the ability to delete. Access tends to use a > syntax of 'DELETE * FROM table' where Postgres insist on 'DELETE FROM > table' with no *. I have to use pass-throughs to delete records. > > I not sure any of this will help but I figure it is worth a shot. > > Guy Steven wrote: > > > > I am experiencing a very frustrating problem with postgresql and access > > 2000. > > > > I have a postgresql 7.2.4-5.80 database and am accessing it using access > > 2000. > > Tables are linked using odbc. > > > > I can read a table. I can add records to a table. I can edit and delete > > existing records in the table, but I can not edit or delete records in the > > table that were added using the odbc connection. By this I mean that > records > > that are imported into the postgresql (from a dump from pg_dump) can be > > edited or deleted, but if I add a record from access, I can't edit or > delete > > from access. > > From within psql the records look identical. > > The error message I get is No Current Record. > > > > Guy Steven > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Hello,
I have also experienced similar odd things like that with linked tables and access. Not exactly like in you
description,but for me, deleting all linked tables, and relinking them works fine. This is apparently not the same as
usingthe tool from the Option Menu ("Gestionnaire Tables liées" in French), that works fine when you have added a
column,though.
I have problems like that when I move an Access database from my computer, linked to a development database, to
another,linked to an identical (but separate) production database.
I don't know exactly what Access permanently stores in a linked table connection. Does anyone know?
Philippe Lang
-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Guy Steven
Envoyé : jeudi, 8. janvier 2004 01:32
À : pgsql-odbc@postgresql.org
Objet : [ODBC] No Current Record
I am experiencing a very frustrating problem with postgresql and access 2000.
I have a postgresql 7.2.4-5.80 database and am accessing it using access 2000.
Tables are linked using odbc.
I can read a table. I can add records to a table. I can edit and delete existing records in the table, but I can not
editor delete records in the table that were added using the odbc connection. By this I mean that records that are
importedinto the postgresql (from a dump from pg_dump) can be edited or deleted, but if I add a record from access, I
can'tedit or delete from access.
From within psql the records look identical.
The error message I get is No Current Record.
Guy Steven
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html