Re: PostGreSQL to Access Updatable recordset

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.