Migration from MS Access to SQL 2005 in vb6.0 with DAO

[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

[ChrisPadgham]

In line

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

dbOpenTable applies to Jet databases only. Try dbOpenDynaset instead.

[game4tress]

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

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

[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

[AndreRet]

Have a look at the following links. They might contain the solution you need. It seems that primary and foreign keys might be the culprit here, not sure though...:)

http://support.microsoft.com/kb/242459
http://stackoverflow.com/questions/4028395/what-does-the-cannot-change-the-activeconnection-property-of-a-recordset-object
http://bytes.com/topic/access/answers/198968-recordset-not-updatable-why-not
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761