Обсуждение: 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;ShowOidColumn=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;UnknownsAsLongVarchar=0;BoolsAs
Char=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;;LFConversion=1;
UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=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: > 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)
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 listedin 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 intendedrecipient, is strictly prohibited.
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 > > 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@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) > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
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. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >