Access to SQL Server 2005

[dcmageddon]

Hello,
Hopefully someone can help with this.
I am trying to connect an Access front end to a SQL Server backend. At the moment i am successfully running pass-through queries from the front end.

The problem I have is editing the data on the backend using code. When I run the code I get an error saying Cannot Update. Database or object is read-only. I believe this is somthing to do with recordsets or dynasets etc. but I'm new to this and am finding it hard to get information.
The code goes as follows:

Private Sub Command6_Click()

Dim DataMdbLoc
DataMdbLoc = "Products"

Dim DataMDB As Database, ProductTable As Recordset
Set DataMDB = DBEngine.Workspaces(0).OpenDatabase("", True, True, "ODBC;Description=access to SQL Server;DRIVER=SQL Server;SERVER=(local);UID=Administrator;DATABASE=boc;Trusted_Connection=Yes")
Set ProductTable = DataMDB.OpenRecordset("Products", dbOpenDynaset)
'ProductTable.Index = "PrimaryKey"


If ProductTable.EOF Then
MsgBox "There are no records in this table !"
ProductTable.Close
End If

ProductTable.MoveFirst
Do Until ProductTable.EOF
If ProductTable![YearOfManufacture] < 2000 Then
ProductTable.Edit
ProductTable![NeedsNCT] = True
ProductTable.Update ' Update Recordset.
ElseIf ProductTable![YearOfManufacture] >= 2000 Then
ProductTable.Edit
ProductTable![NeedsNCT] = False
ProductTable.Update ' Update Recordset.
End If
ProductTable.MoveNext
Loop

ProductTable.Close ' Close Recordsets.

MsgBox "Update Complete !"

End Sub


If anyone can help with this, it would be greatly appreciated.
DC

[Foxer]

Are you using DAO or ADO? You can check in your project references. I suggest using ADO if you can get away with it.

Your code looks pretty good. The message you give is often provided when a recordset is based on a query and the query joins two tables. Is your PRODUCTS "table" really a query or a SQL Server view? If it is, open that view up in MS Access and see if you can edit data in datasheet mode. If you can - the problem is with your code; if not, then your code is probably fine and you need to look at the structure of your query/tables. Many to many joins can't be edited in the way you'd like.

You could also look at SQL Server permissions using Enterprise Manager for SQL Server. See if someone has changed something in there.

[RobDog888]

Yes, he is using DAO and if using Acess 2000 or newer you should use ADO.

The opening of the recordset takes another argument which tells it to open it as a table and or another that makes it updatable. Cant remember right now as DAO is old but check the other args and if you need more info I can write something.

[dcmageddon]

Thanks for the info guys,

there is only one table in the db for test purposes so there are no joins. I have checked the permissions and I don't think there is a problem there so I'm pretty sure it is the code.

If I was to use ADO how would I go about it? Can someone please post some sample code for connecting to and editing the SQL Server 2005 table in the DB?

[RobDog888]

Sure, se my example using VB6 but practically the same as using Access.

http://vbforums.com/showpost.php?p=2800782&postcount=2

[dcmageddon]

Thanks for that Rob!