problem opening a record set on SQL back end

[mazza]

I am converting an MDb to an mdb linked to a SQL back end

In one of my normal mdb tables I had a table with service dates for a specific machine. This table had no primary key as this had an impact on my function that would update the service dates. It worked fine in access.
The problem with a autonumber was that i messes up the order in the record set.

Now that I have a SQL backEnd I had to assign a primary key + identity to the table in order to make it a table I can add/edit records.

Hwr now i am using my code I get the following error message
"You must use the dbseechanges option with openrecordset when accessing a SQL server table that has an Idenity Column"


This is the code i am using

Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Date
Dim g As Integer
Dim servicedate As Date

g = 365 / (AvgRunHrs / ServiceInt)

Set db = CurrentDb()
Set rs = db.OpenRecordset("TblServicebySerialSummary")
With rs
.FindFirst "SerialId = " & Forms![addserialdata].[SerId] & ""
servicedate = ![plannedServicedate]
Do Until rs.NoMatch
.FindNext "SerialId = " & Forms![addserialdata].[SerId] & ""
.Edit
![plannedServicedate] = servicedate + g
.Update
servicedate = ![plannedServicedate]
Loop
.Close

End With



ANY Suggestions here?

 

[pbaldy]

Set rs = db.OpenRecordset("TblServicebySerialSummary", dbOpenDynaset, dbSeeChanges)

But it would be more efficient to open the recordset using an SQL statement that only returned the relevant records.

 

[modest]

This table had no primary key as this had an impact on my function that would update the service dates. It worked fine in access.

I am trying very hard to understand when there is ever a time that a Primary Key is not necessary. Every table should have some form of Primary key - whether it is autonumbered by the database, created by the user, or dynamically generated via code.