Skip site navigation (1) Skip section navigation (2)

Re: ODBC connection string

From: greg(dot)campbell(at)us(dot)michelin(dot)com
To: "Biswajit Bardalai" <bisbar(at)gmail(dot)com>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: ODBC connection string
Date: 2006-08-08 22:10:05
Message-ID: OF7C93E57F.2C53ED54-ON852571C4.00767DEA-852571C4.0079C5D6@michelin.com (view raw or flat)
Thread:
Lists: 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(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

In response to

pgsql-odbc by date

Next:From: Drew BalazsDate: 2006-08-09 13:40:50
Subject: Issues with psql 08.02.0002
Previous:From: Biswajit BardalaiDate: 2006-08-08 21:19:39
Subject: ODBC connection string

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group