Re: TEXT datatype and ODBC?

--- Andrew Ayers <aayers(at)eldocomp(dot)com> wrote:
> Basically, I have a legacy VB app that I am
> converting over to use
> PostgreSQL. Prior to this, the VB app used DAO to
> update an Access 97
> database. Certain tables on this database had fields
> which were of the
> Memo datatype. I converted these on the PG side to
> TEXT fields. That is
> when I noticed a problem, which seems to only be an
> issue with TEXT type
> fields.
>
> When I go to do an "add" (via the AddNew method in
> DAO/VB), and I update
> a field twice before committing the change (via the
> .update method in
> DAO/VB), I get an error of "can't perform this
> operation" on the second
> change of the field. My test code is as follows:
>
> Private Sub cmdTest_Click()
> '
> Dim ws As Workspace
> Dim cn As Connection
> Dim db As Database
> Dim rs As Recordset
> '
> Set ws = CreateWorkspace("WS", "", "", dbUseODBC)
> '
> ws.DefaultCursorDriver = dbUseODBCCursor
> '
> Set cn = ws.OpenConnection("", dbDriverComplete,
> False,
>
"ODBC;driver={PostgreSQL};server=mysvr;port=5433;database=mydb;uid=3Daayers;pwd=;ksqo=;")
> Set db = cn.Database
> '
> Set rs = db.OpenRecordset("SELECT * FROM
> zzz_dummy", dbOpenDynaset,
> dbExecDirect, dbOptimisticValue) ' Fails as "cant
> perform this operation"
> 'Set rs = db.OpenRecordset("SELECT * FROM
> zzz_dummy", dbOpenDynaset)
> ' Fails as "read only"
> 'Set rs = db.OpenRecordset("zzz_dummy",
> dbOpenDynaset) ' Fails as
> "read only"
> '
> With rs
> '
> If .RecordCount > 0 Then
> '
> .MoveLast
> .MoveFirst
> '
> End If
> '
> .AddNew
> '
> ![a] = 1
> ![a] = 2
> ![b] = "test1"
> ![b] = "test2"
> '
> .Update
> '
> End With
> '
> rs.Close
> db.Close
> cn.Close
> ws.Close
> '
> End Sub
>
> The table can be recreated in PostgreSQL via:
>
> CREATE TABLE zzz_dummy(a int4, b text, PRIMARY KEY
> (a));
>
> I am using DAO 3.51 methods and functions in this
> app. I have found that
> if I do the same with any other PG datatype (in this
> case, the field
> "a"), it works as expected - only on the TEXT
> datatype does it fail with
> the error message. I am using the latest (7.3.200?)
> ODBC driver under
> Windows XP Pro.

I'm not too knowledgable in DAO, but since no-one else
is answering, I'll have a go.

I assume you have driver setting "Text as LongVarChar"
checked? In that case, Access will see the PostgreSQL
"text" datatype as "Memo", and will see "varchar" as
"Text". In that case, code that worked against Memo
fields in other settings _should_ work here as well...

<wild guess> I'm wondering if this is not another
version of the old error "Another user etc". If so,
you could get around it by using:
.Update ,True
i.e. force the latest specified value to be used,
regardless of what Access thinks other users might be
doing. </wild guess>

I'm assuming that the code you posted is a simplified
example, because the logic of setting the same field's
value twice before updating is not apparent. Perhaps
there is another way to do what you want.

My only other suggestion is to try an Access list/NG.
I did a quick Google on comp.databases.ms-access and
found a number of read-only problems with updating
recordsets, but none that looked quite like yours.

__________________________________
Do you Yahoo!?
Yahoo! Calendar - Free online calendar with sync to Outlook(TM).
http://calendar.yahoo.com