DAO 3.5, SQL recordset won't allow edit method
Troy Ketsdever
Hello all;
I am having a challenge getting a SQL recordset into an editable
state. I'm using the following code block (in VB 5.0) to try to do
this. I can't seem to find the right combination of <TYPE>,
<OPTIONS>, and <LOCKEDIT> values in my call to OpenRecordset.
...
Set ws = CreateWorkspace("MyName", "", "", dbUseODBC)
Set db = ws.OpenDatabase("MyODBCSource", dbDriverNoPrompt, False)
Set rs = db.OpenRecordset("SELECT * FROM MyTable", <type>,
<options>, <lockedit>)
'TYPE OPTIONS LOCKEDIT
'---- ------- --------
'dbOpenTable dbAppendOnly dbReadOnly
'dbOpenDynamic dbSQLPassThrough dbPessimistic
'dbOpenDynaset dbSeeChanges dbOptimistic
'dbOpenSnapshot dbDenyWrite dbOptimisticValue
' dbDenyRead dbOptimisticBatch
' dbForwardOnly
' dbReadOnly
' dbRunAsync
' dbExecDirect
' dbInconsistent
' dbConsistent
'The following line has no problems (only reading):
Print rs("MyField")
'The following line causes 1 of 2 errors:
rs.Edit
'The 2 errors:
'Error 3027 - Can't update. Database or object is read-only
'Error 3001 - Invalid argument
...
In this example, 'MyODBCSource' is defined through ODBC administrator
to be a SQL Server 32-bit datasource. I have no problems opening
recordsets and reading from them. It's just that I can't get it to
successfully execute an Edit or Addnew method on the open object.
So far, my searches in the VB 5.0 help file and on the MSDN haven't
yielded a solution. Can anyone help me (please e-mail to the troyk
address below)?
Thank you,
Syd Kahn
Make Very Sure that you have set primary keys on EACH table in the
querry. Without Primary keys - Jet connetcted through ODBC will not
allow updates.
Syd