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
