Re: PostGreSQL to Access Updatable recordset

From: "Sim Zacks" <sim(at)nospam(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: PostGreSQL to Access Updatable recordset
Date: 2004-11-16 06:48:02
Message-ID: cnc7tq$3a5$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 in message
news:B2C547DF42419645804F05B54290755ADC7CD6(at)DAYTONEX(dot)oneilinc(dot)net(dot)(dot)(dot)
> 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(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Sim Zacks
> > Sent: Tuesday, November 09, 2004 6:53 AM
> > To: pgsql-general(at)postgresql(dot)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
>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Peter Eisentraut 2004-11-16 07:20:34 Re: Schemas?
Previous Message Anatoly Okishev 2004-11-16 05:19:01 Re: How the function written on pl/pgsql can be called by the function written on plperl.