Database Read-Only ODBC Connection to SQL Server

[hughesai]

Another day - Another problem !

I have a A97 front-end to a SQL Server back-end.
I am trying to add a new record to a table using the following code:

Private Sub cmdNewAction_Click()

Dim wsp As Workspace
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set wsp = DBEngine(0)
Set db = wsp.OpenDatabase(Name:="", Options:=False, ReadOnly:=False, Connect:=G_CONNECTION)
Set rst = db.OpenRecordset("select tblTask.* from tblTask", dbOpenDynaset, dbSeeChanges, dbPessimistic)

rst.AddNew
rst!ProjectID = Forms!frmProject!ProjectID
rst!ActionOpenDate = CDate(Int(CDbl(Now)))
rst.Update

Me.Requery

End Sub

My wsp and db definition I think are correct because they work elsewhere for reading records.
G_CONNECTION is a global constand defining my ODBC connection details.
When I reach the rst.AddNew command, I get a message
"Run Time Error 3027 : Can't Update - Database or Object is Read-only"
I can manually add records to the table, so I believe the problem is in the OpenRecordset line.
I've scanned the Help files and these forums, tried many combinations of options, but can not find where the problem is.

Can anyone see why this is not working ?
Any help as usual is much appreciated.

Aidan Hughes

 

[allanon]

We do the same thing here.

It is important that your link have a defined key on the server. If access thinks that there is no key then it makes the link read only.

If you want to test just link to the table manually and see if you can update. When you are linking to the table if there is no key you should be prompted to select a key. I am not sure if it will work if you just select a key on a table without a key.

 

[FancyPrairie]

One thing to check is to ensure that the SQL tables have at least one indexed field.

 

[hughesai]

Thanks guys.

Unfortunately, this has not solved the problem.
I can manually update, add, and delete records as you have suggested..
The SQL table has already a key defined.
The code still does not work.

Any other thoughts anyone???

Aidan Hughes

 

[cmmrfrds]

Are you absolutely sure that there is a UNIQUE key on the table. Also, you probably need an UNIQUE index. If you added a timestamp column to the table it will be unique. Missing a unique field may not be the case but it is something to be sure about.

 

[hughesai]

Hmmmm,

There is a field called TaskID which is an autonumber field.
This by itself is unique.

However, I have not defined this as the primary key.
There is also a field called ProjectID which may have multiple occurances.
I have defined the combination of ProjectID and TaskID as the primary key.
In retrospect, I'm not sure why I did this as TaskID by itself would be enough for the key. However, my current prinary key is unique and indexed.

I would not have thought that this would cause a problem, but knowing Access it might, so it is something which I will redefine when I get back to work on Monday.

Aidan.

 

[hughesai]

Another Follow-Up.

I have redefined the index in the SQL table. TaskID, an autonumber field, is now the primary key and is the only indexed field. I am now sure that my SQL table is set up correctly per all your responses which I thank you for.

However, I am still getting the same problem.

I can still add a record directly into the table, but when adding a record using the code above, I still get the 3027 error.

I am lost - any other ideas anyone?

Aidan Hughes.