Re: [INTERFACES] Access97 -> ODBC -> Postgres Problems

Weston Bustraan wrote:
>
> OK, here's my scenario:
>
> We have a client with a database app where the backend data resides in
> MS SQL server and the frontend was created with MS Access 97. Eventually
> they will be migrating to a Web/PostgreSQL system, but it will be done in
> two steps. The first is to change the backend to PostgreSQL. I imported
> the data from the MS SQL server into a Access MDB file. Then I broght it
> back to our office to work with. I tried various methods to try to
> transfer the data from Access97 into postgres, finally I gave up and
> decided to write something myself. I set up two ODBC datasources, the .mdb
> file and the Postgres Database; then I wrote a small Python script to pull
> records out of the one and insert them into the other. Worked like a
> charm. Now I am trying to link the frontend (in Access97) to PostgreSQL
> through ODBC. I have 6 tables, 3 of them have no problems, but the other
> 3 will not let me change any data. I can view it, but when I try to change
> something, it gives me this error message:
>
> "This has been changed by another user since you started editing
> it. If you save the record, you will overwrite the changes the
> other user made.
>
> Copying the changes to the clipboard will let you look at the
> values the other user entered, and then paste the changes back if
> you decide decide to make changes."
>
> There are three buttons: "Save Record", "Copy to clipboard", and "Drop
> changes". "Save Record" is greyed out.
>
> In the logs I get:
> conn=50895320, query='BEGIN'
> conn=50895320, query='UPDATE "agents" SET "eocoverage"='1' WHERE
> "agentid" = '381966990' AND "firstname" IS NULL AND "middleinitial" IS
> NULL AND "lastname" IS NULL AND "spouse" IS NULL AND "idtype" = 'FID' AND
> "dob" IS NULL AND "homeaddress" IS NULL AND "homecity" IS NULL AND
> "homestate" = 'MI' AND "homezip" IS NULL AND "homeareacode" IS NULL AND
> "homephone" IS NULL AND "email" IS NULL AND "agencyname" = 'A W Ayres
> Agency' AND "agencypobox" = '278' AND "agencyaddress" = '700 W Chicago
> Rd' AND "agencycity" = 'Sturgis' AND "agencystate" = 'MI' AND
> "agencyzip" IS NULL AND "agencyareacode" = 616 AND "agencyphone" =
> '6511761' AND "agencytollfreecode" IS NULL AND "agencytollfreenumber" IS
> NULL AND "agencyfaxareacode" = 616 AND "agencyfax" = '6513264' AND
> "agencycontact" IS NULL AND "agencycontact2" IS NULL AND "dedicatedfax"
> = '1' AND "eocoverage" = '0''
> conn=50895320, query='ROLLBACK'
>
> All six tables have primary keys. In the 'Agents' table, 'agentid' is the
> primary key.
>
> I have tried every combination of options for the PsqlODBC driver I can
> think of. None of them work. I tried turning on "Recognize Unique
> Indexes", "Row Versioning", "OID Show Columns", "OID Fake Index", etc. No
> combination seems to work.
>
> I had this problem once with the MySQL ODBC driver on some other
> computers. In that case, updating to the latest driver fixed the problem.
> I have installed the latest version of everything. PostgreSQL 6.5.3,
> psqlODBC 6.40.0008, MS MDAC 2.5. I just can't figure it out.
>
> ,------------------------------------------------------------,
> | Weston J. Bustraan | E-Mail: weston(at)itdonline(dot)net |
> | Development Manager | Phone: (616)249-3630 |
> | Infinity Tel-Data Inc. | Fax: (616)249-3067 |
> | 4723 S. Division Ave. | WWW: www.itdonline.net |
> | Wyoming, MI 49548 | PGP: finger weston(at)itdonline(dot)net |
> `------------------------------------------------------------`
>
> Linux renders ships, NT is rendering ships useless.
>
> ************

Cut and paste the exact same update statement into the psql monitor
program and see what you get. You will probably get 0 records updated
or something which is confusing access. There is probably a field there
that has a problem. try to narrow it down to that.

Byron