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?
AddNew after false EOF test bombs
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
AddNew after false EOF test bombs
Last edited by Pat on 06 Jul 2010, 01:39, edited 2 times in total.
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: AddNew after false EOF test bombs
Does it make a difference if you change the first line to
Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset)
Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset)
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: AddNew after false EOF test bombs
Sorry that line was already:
Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset, dbSeeChanges)
Set rs = currentDB.OpenRecordset("SELECT * FROM JobLocks Where ID = 123", dbOpenDynaset, dbSeeChanges)
-
- Administrator
- Posts: 78631
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: AddNew after false EOF test bombs
It might be specific to SQL Server - in Access itself you'd be able to add a new record.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: AddNew after false EOF test bombs
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.
I just wanted to know if i was right in my assumption or not.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: AddNew after false EOF test bombs
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.
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.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: AddNew after false EOF test bombs
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.
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.
-
- 5StarLounger
- Posts: 1148
- Joined: 08 Feb 2010, 21:27
Re: AddNew after false EOF test bombs
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