I have a postgresql backend with an access front end and I am trying to
redefine the recordset of the form to use an ADO recordset. The problem is
that the CursorType always changes to AdOpenStatic, even if I choose
adOpenDynamic. If anyone has any thoughts, please let me know. The form
works great for viewing, but I cannot update or insert any new records.

Below is the code I am using:

connectstr = "DSN=SAP_PG;uid=postgres"
Set conn = New ADODB.Connection
conn.Open (connectstr)
rs.CursorLocation = adUseClient
rs.Open "select ProductID,ProductName,ProductTypeID,StockTypeID from
Products order by ProductName", conn, adOpenDynamic, adLockOptimistic
Set Me.Recordset = rs
Me.Requery

The conn object contains the following:
Provider=MSDASQL.1;Extended
Properties="DSN=SAP_PG;DATABASE=sap;SERVER=10.1.1. 76;PORT=5432;UID=username;
PWD=password;ReadOnly=0;Protocol=6.4;FakeOidIndex= 0;ShowOidColumn=0;RowVersi
oning=1;ShowSystemTables=0;ConnSettings=;Fetch=100 ;Socket=4096;UnknownSizes=
0;MaxVarcharSize=4094;MaxLongVarcharSize=4094;Debu g=0;CommLog=0;Optimizer=1;
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=0;Unkno wnsAsLongVarchar=0;BoolsAs
Char=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePre fixes=dd_;;LFConversion=1;
UpdatableCursors=1;DisallowPremature=0;TrueIsMinus 1=0;BI=0;ByteaAsLongVarBin
ary=0;UseServerSidePrepare=0"


In answer to your upcoming questions:

1) ProductID is the primary key on the table.
2) I do not want to use linked forms, they go much slower then connecting
directly through the ADO recordset.
3) I put a breakpoint right after the open statement and checked the value
of rs.CursorType and that is when I saw it was adOpenStatic
4) I am using PostGreSQL 8.0beta1
5) ODBC driver 7.03.02.00

On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote:
Postgres does not support updatable cursors yet. Maybe that's why the ADO
driver changes the cursor type from AdOpenDynamic to AdOpenStatic.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Nunca se desea ardientemente lo que solo se desea por razón" (F. Alexandre)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

hat pretty much solves my problem. Also I found that Access doesn'r support
ODBC updateable cursors until at least Access 2002 (we're using Access
2000), though I thought I had figured out how to get around that and it
didn't work.
I assumed the ODBC driver did support updateable cursors simply because
there is an option in the driver settings called "Updateable cursor"

Thank you for your clarification.
Sim Zacks
IT Manager
Compulab
to email me use: sim "the at sign" compulab "the dot" co "the dot" il


"Alvaro Herrera" <alvherre (AT) dcc (DOT) uchile.cl> wrote

On second thought, I am now confused. The data is updatable when using
linked tables, just not when using ADO recordsets connected to forms. I
tried connected to PostGreSQL using DAO, and I got the recordset back, but
it was also non-updatable.

For the time being I am using Linked Tables, even though it is much slower
to open the forms using that methodology.


--
Sim Zacks
IT Manager
Compulab
to email me it is sim "the at sign" compulab "the dot" co "the dot" il

"Sim Zacks" <sim (AT) nospam (DOT) com> wrote

Try this,

Set conn = New ADODB.Connection
Conn.open "DNS=SAP_PG;uid=postgres"
Set rsE = Conn.Execute(updateSQL)

Where updateSQL is your update statement.
Check permissions for updates/write for the user you are connecting.

- Goutam



Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Thank you for your response, however that is not what I am trying to do.
I would like to be able to add records and modify data directly in the form.
What I was trying to do was grab an ADO recordset and attach it to the form,
which works fine, but then the data is not updatable.

I found a MS article which states that before Access 2002 ADO recordsets for
ODBC sources could not be updated.
Linked tables works for updating, and while I didn't want to use them, I am
using those for the forms that need to be updateable and dynamic recordsets
for the ones that don't.

Sim

""Goutam Paruchuri"" <gparuchuri (AT) oneil (DOT) com> wrote

received this communication in error, please contact the sender at O'Neil &
Associates, Inc., immediately. Any copying, dissemination, or distribution
of this communication, other than by the intended recipient, is strictly
prohibited.