Re: ODBC connection string

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(dot)campbell(at)us(dot)michelin(dot)com

"Biswajit
Bardalai"
<bisbar(at)gmail(dot)com To
> pgsql-odbc(at)postgresql(dot)org
Sent by: cc
pgsql-odbc-owner@
postgresql.org Subject
[ODBC] ODBC connection string

08/08/2006 17:19

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