PostGreSQL to Access Updatable recordset

[Sim Zacks]

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

[Alvaro Herrera]

On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote:

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.

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

[Sim Zacks]

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" <al******@dcc.uchile.cl> wrote in message
news:20********************@dcc.uchile.cl...

On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote:

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.
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

[Sim Zacks]

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" <si*@nospam.com> wrote in message
news:cn***********@news.hub.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" <al******@dcc.uchile.cl> wrote in message
news:20********************@dcc.uchile.cl...

On Tue, Nov 09, 2004 at 01:53:18PM +0200, Sim Zacks wrote:

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.
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