Re: ODBC connection string - Mailing list pgsql-odbc

The problem might not be your connection string.
I would recommended do some logging.
Try to do update while MyLog is enabled using the pgodbc and a DSN. (The DSN is just a testing measure.). The ODBC database Administrator interface gives you a means to set and reset MyLog.
Or log updates with ODBC Trace turned on.
Or even turn on command logging at the server by setting it postgresql.conf and restarting the PG postmaster.

What I suspect is that without primary keys your Access has to identify rows to update by using the long where clause
"WHERE field_a='value_a' and field_b='value_b' and field_c='value_c',...".
Either because of case or some specific syntax issue, it cannot find the record to update.

Either that or you might be using DAO to do an update like
rs.Edit
rs!field_a = "New value for A"
rs.Update
Here you have a recordset that you are holding the cursor open on across statements,...a server side cursor,..whose support is always sketchy with PostgreSQL.
If you had coded
sql = "UPDATE field_a='new value for A' WHERE field_b='value_b'.
CurrentDB.Execute sql
This simple type of Update statement should work. Of course you cannot really do this is users are directly updating a table/tabular view of a table or query, or bound form.

You seem to be using a good version of the ODBC driver. Perhaps the logging will turn up some bug that needs to be addressed.

Detailed logging will tell you what statements are going to the PostgeSQL database server engine,and what it is returning, as opposed to my speculations.

Editorial Note: Primary keys change the interactions of interface and database. It makes it possible to more quickly identify A record. Almost all well designed tables have primary keys. Now I'll step off my soap box.




Greg Campbell ENG-ASE/Michelin US5
Lexington, South Carolina
803-951-5561, x75561
Fax: 803-951-5531
greg.campbell@us.michelin.com

"Biswajit Bardalai" <bisbar@gmail.com>

"Biswajit Bardalai" <bisbar@gmail.com> Sent by: pgsql-odbc-owner@postgresql.org 08/08/2006 17:19

To pgsql-odbc@postgresql.org cc Subject [ODBC] ODBC connection string

Hi,

I'm using the following connection string to programatically link PostgreSQL tables to MS Access.

strConn="ODBC;DRIVER={PostgreSQL};DATABASE=myDb;SERVER= 192.168.0.91 ;" & _
"PORT=5432;UID=myDbUser;PWD=pass;A0=0;A1=7.4;A2=1;A3=0;A4=1;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_"

Dim tdf As TableDef

Set tdf = db.CreateTableDef(clients, dbAttachSavePWD, public.clients, strConn)
db.TableDefs.Append tdf
db.TableDefs.Refresh

I can create the linked tables and insert rows into them. The problem is tables without primary keys cannot be updated, though an insert is okay.

However, tables linked through a system DSN, have no updation problem. Is there an error in the connection string? Where can one find the complete list of parameters?

I don't want to create a DSN as I intend to distribute the Access file and don't want savvy users to be able to access the tables through the DSN. I'm not keen on creating a primary key for every table, either.

Is there a solution to this problem? Been looking for a one for several days.

FYI, the PostgreSQL ODBC driver version is: 8.02.00.02

Thanks,
Biswajit


--
Call Toronto free: http://www.vbuzzer.com/index.php?aid=biswajit
Free SMS: http://www.atrochatro.com/ptnr.php?ptnr=12259