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("name") = "reg"


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


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

Can anyone help me?

My thanks in advanced

In line

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

dbOpenTable applies to Jet databases only. Try dbOpenDynaset instead.

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

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

in the Microsoft document
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

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.