Re: [INTERFACES] Access97 -> ODBC -> Postgres Problems - Mailing list pgsql-interfaces

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@itdonline.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@itdonline.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