Member Avatar for game4tress

Hi

I need to migrate an application from MS Access to SQL Server (2005), and i'm using the code bellow, but i'm getting an error when the compiler reaches the AddNew instruction. It gives me "Run-time error '3027': Can't update. Database or object is read-only".

Dim DBTesteSQL As Database

Dim rsTesteSQL As Recordset



Set DBTesteSQL = OpenDatabase("", dbDriverNoPrompt, False, "driver={SQL Server};server=ABC\CDF;uid=XXX;pwd=XXXXX;Trusted_Connection=No;APP=Visual Basic;database=MyDatabase")



Set rsTesteSQL = DBTesteSQL.OpenRecordset("SELECT * FROM test", dbOpenTable, 0, dbOptimistic)



Debug.Print rsTesteSQL.Updatable





rsTesteSQL.AddNew

rsTesteSQL("name") = "reg"

rsTesteSQL.Update



rsTesteSQL.Close: DBTesteSQL.Close

Set rsTesteSQL = Nothing

Set DBTesteSQL = Nothing

As far as i can tell, the problem comes from the line:

Set rsTesteSQL = DBTesteSQL.OpenRecordset("SELECT * FROM test", dbOpenTable, 0, dbOptimistic)

because if i try to add records through the following line of code, it works:

DBTesteSQL.Execute "Insert Into teste (nome) VALUES ('abcdef')"

So the problem comes from the recordset,but the return value from the following line is False

Debug.Print rsTesteSQL.LockEdits

meaning that i should be able to write to the database, but the following line also returns false

Debug.Print rsTesteSQL.Updatable

meaning that i can't make updates to the database. So i can only conclude that the problem comes from the options parameter

dbOpenDynaset

but i can't seem to find a way to solve this.

Can anyone help me?

My thanks in advanced

Edited by kvprajapati because: Added [code ]tags.
  • 3 Contributors
  • 4 Replies
  • 371 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by AndreRet
Member Avatar for ChrisPadgham

In line

Set rsTesteSQL = DBTesteSQL.OpenRecordset("SELECT * FROM test", dbOpenTable, 0, dbOptimistic)

dbOpenTable applies to Jet databases only. Try dbOpenDynaset instead.

Edited by ChrisPadgham because: n/a
Member Avatar for game4tress

Thank you for your reply.
I've tryed that but the problem remains

Set rsTesteSQL = DBTesteSQL.OpenRecordset("SELECT * FROM teste", dbOpenDynaset, 0, dbOptimistic)

Member Avatar for game4tress

in the Microsoft document
http://msdn.microsoft.com/en-us/library/bb243815(v=office.12).aspx
there is the following statment:
"The tables underlying a Recordset object may not permit you to modify data, even though the Recordset is of type dynaset or table, which are usually updatable. Check the Updatable property of the Recordset to determine whether its data can be changed. If the property is True, the Recordset object can be updated."

So the question is, why can't the Recordset object modify data? I guess that is the problem happening in my situation

Member Avatar for AndreRet
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.