AddNew after false EOF test bombs

[Pat]

I have code that looks like the following. This is in a Access 2003 FE and SQL Server 2005 backend:
Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123")
If Not rs.EOF then
....do things
Else
rs.AddNew
..
End if
****The error is 3219 Invalid Operation on the rs.AddNew command ****

Ths AddNew command errors out (can't remember what it was, see my edit above) as i have changed the code to the following which works:
Set rs = currentDB.OpenRecordset("JobLocks")
rs.FindFast "ID = 123"
If Not rs.NoMatch then
....do things
Else
rs.AddNew
..
End if
Is it because of the WHERE clause in the SELECT statement?

[HansV]

Does it make a difference if you change the first line to

Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset)

[Pat]

Sorry that line was already:

Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset, dbSeeChanges)

[HansV]

It might be specific to SQL Server - in Access itself you'd be able to add a new record.

[Pat]

That's what i assumed, the code used to work with an Access backend.
I just wanted to know if i was right in my assumption or not.

[Pat]

Does anyone know if it is a problem (where you have a WHERE clause in a SET rs =) and SQL Server ojects to it?
I have a lot of .AddNew commands to look at and maybe change in this database, if there's a simpler solution i would love to hear it.

[Pat]

The 2nd option above will fail too if there are no records in the table, in fact FindFirst fails if there are no records in the table.
So I will need to test for EOF before trying a FindFirst.

As Hans pointed out earlier it works in Access but not for Sql Server linked tables.

[Pat]

Just thought i would show the code behind a test form. The blSELECT is a check box that i set or clear before executing the code. Both options work.

Code: Select all

    Dim rs As DAO.Recordset
    CurrentDb.Execute "DELETE * FROM JobLocks  WHERE jobNumber = " & Nz(Me.ServiceID, 0), dbSeeChanges
    If blSELECT = True Then
        Set rs = CurrentDb.OpenRecordset("SELECT lockedBy,jobNumber,LockedDate FROM JobLocks WHERE jobNumber = " & Nz(Me.ServiceID, 0), dbOpenDynaset, dbSeeChanges)
        If Not (rs.EOF) Then
        Else
            Dim sSql As String
            sSql = "INSERT INTO JobLocks (lockedBy,jobNumber,LockedDate)"
            sSql = sSql & " VALUES (" & UserID & ", " & Me.ServiceID & ", #" & Format(Now, "mm/dd/yyyy hh:nn:ss") & "#)"
            CurrentDb.Execute sSql
            'rs.addnew
            'rs!jobnumber = Me.ServiceID
            'rs!lockedBy = UserID
            'rs!lockedDate = Now
            'rs.Update
        End If
    Else
        Set rs = CurrentDb.OpenRecordset("JobLocks", dbOpenDynaset, dbSeeChanges)       'pt
        If rs.EOF Then
            rs.addnew
            rs!jobnumber = Me.ServiceID
            rs!lockedBy = UserID
            rs!lockedDate = Now
            rs.Update
        Else
            rs.FindFirst "JobNumber=" & Nz(Me.ServiceID, 0)                                 'pt
            If Not rs.NoMatch Then                                                          'pt
            Else
                rs.addnew
                rs!jobnumber = Me.ServiceID
                rs!lockedBy = UserID
                rs!lockedDate = Now
                rs.Update
            End If
        End If
    End If
    rs.close