Visual Basic&database, Dynaset not Updatable (SQL Server)

Dynaset not Updatable (SQL Server)

Author	Message
David Thureso #1 / 4	Dynaset not Updatable (SQL Server) I'm trying to use the Recordset.AddNew method against a SQL Server database, and getting run time error 3027: "Can't update. Database or object is read-only." If I use the Database.Execute method, I can do the insert, so that should mean I have the appropriate rights in the database. If I run the code against an Access database, both the Execute and AddNew methods work. My theory is that it's something in the way I'm calling OpenDatabase. Do you know where I would look to find the options in the connection string? Or is it something unrelated to that? Thanks in advance for any insights. ---------------------------------------------------- Here's the code: Dim DYN As Recordset Dim stConnect As String Dim db As Database stConnect = "ODBC;Uid=sa;PWD=;DSN=testvb;DBQ=ebc_db;LOGINTIMEOUT=180" Set db = OpenDatabase("", False, False, stConnect) ' This Execute works fine. db.Execute "insert into TestVB (TestText, TestInt) values (""def"", 6)" Set DYN = db.OpenRecordset("TestVB", dbOpenDynaset) '====== DYN.Updatable is now FALSE, leading to the error on the following line. ========= DYN.AddNew -- David Thureson -- Workflow Solutions
Tue, 01 Dec 1998 03:00:00 GMT

Paul Cho #2 / 4	Dynaset not Updatable (SQL Server) david: have you a unique index on on the table "testvb"? i recall that such is needed for access via dao. -paul- Quote: >I'm trying to use the Recordset.AddNew method against a SQL Server >database, and getting run time error 3027: "Can't update. Database or >object is read-only." >If I use the Database.Execute method, I can do the insert, so that should >mean I have the appropriate rights in the database. If I run the code >against an Access database, both the Execute and AddNew methods work. >My theory is that it's something in the way I'm calling OpenDatabase. Do >you know where I would look to find the options in the connection string? >Or is it something unrelated to that? >Thanks in advance for any insights. >---------------------------------------------------- >Here's the code: > Dim DYN As Recordset > Dim stConnect As String > Dim db As Database > stConnect = "ODBC;Uid=sa;PWD=;DSN=testvb;DBQ=ebc_db;LOGINTIMEOUT=180" > Set db = OpenDatabase("", False, False, stConnect) > ' This Execute works fine. > db.Execute "insert into TestVB (TestText, TestInt) values (""def"", 6)" > Set DYN = db.OpenRecordset("TestVB", dbOpenDynaset) > '====== DYN.Updatable is now FALSE, leading to the error on the following >line. ========= > DYN.AddNew > -- David Thureson > -- Workflow Solutions
Fri, 04 Dec 1998 03:00:00 GMT

Chris Westbroo #3 / 4	Dynaset not Updatable (SQL Server) Quote: > I'm trying to use the Recordset.AddNew method against a SQL Server > database, and getting run time error 3027: "Can't update. Database or > object is read-only."[...] David, You cannot use .addnew, .edit or .update on a recordset created from a MSSQL database. Opening a dynaset on a MSSQL database is equivalent to opening a snapshot on it. You need to use the execute, executesql or an updating query in a snapshot or dynaset. (Execute is the fastest.) Also, consider using the DB_SQLPASSTHROUGH (VB3) or dbSqlPassThrough (VB4) options. When you use this parameter you are bypassing the syntax checker and parser of Jet so you must use SQL syntax appropriate to the database, but your performance will likely improve. On the other hand, you cannot use Findfirst or Findnext on a passthrough query. (Who wants to use these anyway? :) Good luck, -- Chris Westbrook, SE \| Work:717-791-5752 Electronic Data Systems \| Fax: 717-791-5555 Communications Industry Group \| Customer Contact Management Center \|
Sat, 05 Dec 1998 03:00:00 GMT

Tim Lavert #4 / 4	Dynaset not Updatable (SQL Server) Chris: I'm trying to use this Passthrough option to execute a stored proc. & cannot seem to get VB3 to recognize that I'm trying to use the option. My snapshot code looks like: Set snpData = gdbWftables.CreateSnapshot("EXEC proctest", db_SQLPassThrough) It gives me a variable undefined error on db_SQLPassthrough if I try to run from a bas module. If I run it under a sub it says "cannot find input table or query 'proctest'. Any ideas? -- Thanks, Tim Laverty 805-822-3393 Quote: > > I'm trying to use the Recordset.AddNew method against a SQL Server > > database, and getting run time error 3027: "Can't update. Database or > > object is read-only."[...] > David, > You cannot use .addnew, .edit or .update on a recordset created > from a MSSQL database. Opening a dynaset on a MSSQL database is > equivalent to opening a snapshot on it. You need to use the execute, > executesql or an updating query in a snapshot or dynaset. (Execute is > the fastest.) > Also, consider using the DB_SQLPASSTHROUGH (VB3) or > dbSqlPassThrough (VB4) options. When you use this parameter you are > bypassing the syntax checker and parser of Jet so you must use SQL > syntax appropriate to the database, but your performance will likely > improve. On the other hand, you cannot use Findfirst or Findnext on a > passthrough query. (Who wants to use these anyway? :) > Good luck, > -- > Chris Westbrook, SE \| Work:717-791-5752 > Electronic Data Systems \| Fax: 717-791-5555 > Communications Industry Group \| > Customer Contact Management Center \|
Mon, 07 Dec 1998 03:00:00 GMT

Page 1 of 1

[ 4 post ]