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

Re: DAO VB ODBC "read only problems

From: DI Hasenöhrl <i(dot)hasenoehrl(at)aon(dot)at>
To: "Miller, Sam" <sam(dot)miller(at)intel(dot)com>
Cc: <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: DAO VB ODBC "read only problems
Date: 2001-10-30 08:42:20
Message-ID: 007901c1611e$ca326ce0$01011eac@irina (view raw or flat)
Thread:
Lists: pgsql-interfaces
Hi Sam,

AFAIK, in MsAccess the dbopenSnapshot format is read-only. Try dbOpenDynaset and it should work.

HTH,
Irina
  ----- Original Message ----- 
  From: Miller, Sam 
  To: pgsql-interfaces(at)postgresql(dot)org 
  Sent: Monday, October 29, 2001 7:01 AM
  Subject: [INTERFACES] DAO VB ODBC "read only problems


  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

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





  ---------------------------(end of broadcast)---------------------------
  TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/users-lounge/docs/faq.html

In response to

pgsql-interfaces by date

Next:From: Christof PetigDate: 2001-10-30 09:46:03
Subject: Re: ECPG: Automatic Storage allocation for NULL-pointing
Previous:From: Hiroshi InoueDate: 2001-10-30 07:49:27
Subject: Re: DAO VB ODBC "read only problems

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