DAO VB ODBC "read only problems - Mailing list pgsql-interfaces

Folks,

I am having great difficulty getting a VB script (Excel) to talk to a linux
based PostgreSQL server.  I can read data just fine but get the following
error when I try to edit:

Next line produces error:
Run-time error '3027';
Connot update. Database or object is read-only

I have done the following so far:

1. Created a table with a "PRIMARY KEY" as:
CREATE TABLE data2 (x integer,y integer,symbol varchar(15),pin varchar(15) primary key); 

2. Made sure that the select statement does not end in a semi-colon.

3. Created the following table (don't think this is necessary).. but I saw
something that mentioned it:
CREATE TABLE "msysconf" (
"config" int2 NOT NULL,
"chvalue" character varying,
"nvalue" int4,
"comments" character varying);

4. Verified that the "read only" check box is not checked in the PostgreSQL
ODBC Advanced Options

5. Checked both "show columns" and "fake index" in the OID options section
of the Data Source advanced options. 

6. Tried dbOpenSnapshot option (??? static cursor) on the OpenRecordset
method (also tried without).

I don't know what else to try, 
Anyone have any ideas? (VB code below)

Sam.


-----------------------------------
Sub checkit()
Dim wrkODBC As Workspace
Dim conSource As Connection

Set wrkODBC = CreateWorkspace("TestWorkspace", "sgmiller", "", dbUseODBC)
Set conSource = wrkODBC.OpenConnection("template1", , ,
"ODBC;Database=template1;DSN=PostgreSQL")
Set rs = conSource.OpenRecordset("Select * From data2", dbOpenSnapshot)

Do While Not rs.EOF Debug.Print rs.Fields("pin").Value
 If rs.Fields("pin").Value = "C19" Then

'Next line produces error:
' Run-time error '3027';
' Connot update. Database or object is read-only
     rs.Edit     rs.Fields("symbol").Value = "Hello"     rs.Update End If rs.MoveNext
Loop

rs.Close
wrkODBC.Close

End Sub

---------------------

Global Options: Version='07.01.0007', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190               disable_optimizer=1, ksqo=1,
unique_index=1,
use_declarefetch=0               text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1               extra_systable_prefixes='dd_;', conn_settings=''
conn=69037888, query=' '
conn=69037888, query='set DateStyle to 'ISO''
conn=69037888, query='set geqo to 'OFF''
conn=69037888, query='set ksqo to 'ON''
conn=69037888, query='select oid from pg_type where typname='lo''   [ fetched 0 rows ]
conn=69037888, query='select version()'   [ fetched 1 rows ]   [ PostgreSQL version string = 'PostgreSQL 7.0.2 on
i686-pc-linux-gnu,
compiled by gcc 2.96' ]   [ PostgreSQL version number = '7.0' ]
conn=69037888,
PGAPI_DriverConnect(out)='DSN=PostgreSQL;DATABASE=template1;SERVER=bbfarm1;P
ORT=5432;UID=sgmiller;PWD=;A0=0;A1=6.4;A2=1;A3=1;A4=0;A5=0;A6=;A7=100;A8=409
6;A9=0;B0=254;B1=8190;B2=0;B3=1;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=d
d_;'
conn=69037888, query='Select * From data2'   [ fetched 3 rows ]
conn=69037888, PGAPI_Disconnect

---------------------