PostGreSQL to Access Updatable recordset

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)

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@dcc.uchile.cl> wrote in message
news:20041113215908.GB12499@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)

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@nospam.com> wrote in message
news:cn6ut7$2r45$1@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" <alvherre@dcc.uchile.cl> wrote in message
news:20041113215908.GB12499@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

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sim Zacks
Sent: Tuesday, November 09, 2004 6:53 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostGreSQL to Access Updatable recordset

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;ShowOidCol

umn=0;RowVersi

oning=1;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096

;UnknownSizes=

0;MaxVarcharSize=4094;MaxLongVarcharSize=4094;Debug=0;CommLog=
0;Optimizer=1;
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=0;UnknownsAsLongVar

char=0;BoolsAs

Char=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;L

FConversion=1;

UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Byt

eaAsLongVarBin

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

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

http://www.postgresql.org/docs/faqs/FAQ.html

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.

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@oneil.com> wrote in message
news:B2C547DF42419645804F05B54290755ADC7CD6@DAYTONEX.oneilinc.net...

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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sim Zacks
Sent: Tuesday, November 09, 2004 6:53 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostGreSQL to Access Updatable recordset

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;ShowOidCol

umn=0;RowVersi

oning=1;ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096

;UnknownSizes=

0;MaxVarcharSize=4094;MaxLongVarcharSize=4094;Debug=0;CommLog=
0;Optimizer=1;
Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=0;UnknownsAsLongVar

char=0;BoolsAs

Char=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;L

FConversion=1;

UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0;Byt

eaAsLongVarBin

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

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

http://www.postgresql.org/docs/faqs/FAQ.html

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.