 | |
error 3167 "record is deleted"
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
error 3167 "record is deleted" -
07-03-2009
, 01:53 PM
#2
| |||
| |||
|
|
using access97 with a table linked to sqlserver 2005 with the following dim rs as dao.recordset dim lngActionId as long Set rs = currentdb.openrecordset("SELECT * FROM tblNcoActionList") With rs .AddNew !NcNumber = NcNumber !issueDate = Date !subject = "Corrective Action" .Update .Bookmark = .LastModified lngActionId = !actionId End With set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? |
Hash: SHA1
I've found, sometimes, that the database variable has to be set:
set db = CurrentDb
set rs = db.OpenRecordset("SELECT * FROM tblNcoActionList")
For some reason it doesn't like it when the db variable isn't maintained
until the end of the connection.
HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBSk5JjIechKqOuFEgEQIqnQCgvX2WQlOmD0Bp749cC0nuj0 M4q9MAnRhe
BRgFYflQdLlWc3U9RkZ93WC8
=PLel
-----END PGP SIGNATURE-----
#3
| |||
| |||
|
|
Roger wrote: using access97 with a table linked to sqlserver 2005 with the following * * dim rs as dao.recordset * * dim lngActionId as long * * Set rs = currentdb.openrecordset("SELECT * *FROM tblNcoActionList") * * With rs * * * * .AddNew * * * * !NcNumber = NcNumber * * * * !issueDate = Date * * * * !subject = "Corrective Action" * * * * .Update * * * * .Bookmark = .LastModified * * * * lngActionId = !actionId * * End With * * set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've found, sometimes, that the database variable has to be set: set db = CurrentDb set rs = db.OpenRecordset("SELECT * *FROM tblNcoActionList") For some reason it doesn't like it when the db variable isn't maintained until the end of the connection. HTH, -- MGFoster:::mgf00 <at> earthlink <decimal-point> net Oakland, CA (USA) ** Respond only to this newsgroup. *I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSk5JjIechKqOuFEgEQIqnQCgvX2WQlOmD0Bp749cC0nuj0 M4q9MAnRhe BRgFYflQdLlWc3U9RkZ93WC8 =PLel -----END PGP SIGNATURE------ Hide quoted text - - Show quoted text - |
and I've decompiled / compacted the mdb... same problem
maybe I need to use a non-autoincrement key, that I can set myself ?
#4
| |||
| |||
|
|
using access97 with a table linked to sqlserver 2005 with the following dim rs as dao.recordset dim lngActionId as long Set rs = currentdb.openrecordset("SELECT * FROM tblNcoActionList") With rs .AddNew !NcNumber = NcNumber !issueDate = Date !subject = "Corrective Action" .Update .Bookmark = .LastModified lngActionId = !actionId End With set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? |
#5
| |||
| |||
|
|
This is SQL Server, so you have to open recordsets with dbSeeChanges set. Set rs = currentdb.openrecordset("SELECT * *FROM * tblNcoActionList", dbOpenDynaset, dbSeeChanges) I'm not sure if that's the problem, but I do know that any dynaset or action query has to have dbSeeChanges set for SQL Server. Good luck! Roger used his keyboard to write : using access97 with a table linked to sqlserver 2005 with the following * * dim rs as dao.recordset * * dim lngActionId as long * * Set rs = currentdb.openrecordset("SELECT * *FROM tblNcoActionList") * * With rs * * * * .AddNew * * * * !NcNumber = NcNumber * * * * !issueDate = Date * * * * !subject = "Corrective Action" * * * * .Update * * * * .Bookmark = .LastModified * * * * lngActionId = !actionId * * End With * * set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ?- Hide quoted text - - Show quoted text - |
#6
| |||
| |||
|
|
using access97 with a table linked to sqlserver 2005 with the following * * dim rs as dao.recordset * * dim lngActionId as long * * Set rs = currentdb.openrecordset("SELECT * *FROM tblNcoActionList") * * With rs * * * * .AddNew * * * * !NcNumber = NcNumber * * * * !issueDate = Date * * * * !subject = "Corrective Action" * * * * .Update * * * * .Bookmark = .LastModified * * * * lngActionId = !actionId * * End With * * set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? |
It is not necessary to set the db-variable. Set rs =
currentdb.openrecordset() always works, at least in my applications
(Access97 en up).
However, the assignment of IngActionId = rs!actionId has to be done
before rs.Update.
HBInc.
#7
| |||
| |||
|
|
On Jul 3, 7:53*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote: using access97 with a table linked to sqlserver 2005 with the following * * dim rs as dao.recordset * * dim lngActionId as long * * Set rs = currentdb.openrecordset("SELECT * *FROM tblNcoActionList") * * With rs * * * * .AddNew * * * * !NcNumber = NcNumber * * * * !issueDate = Date * * * * !subject = "Corrective Action" * * * * .Update * * * * .Bookmark = .LastModified * * * * lngActionId = !actionId * * End With * * set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? Hi Roger, It is not necessary to set the db-variable. Set rs = currentdb.openrecordset() always works, at least in my applications (Access97 en up). However, the assignment of IngActionId = rs!actionId has to be done before rs.Update. HBInc.- Hide quoted text - - Show quoted text - |
sql server assigns it an ID
#8
| |||
| |||
|
|
On Jul 3, 3:15*pm, hbinc <j.van.g... (AT) hccnet (DOT) nl> wrote: On Jul 3, 7:53*pm, Roger <lesperan... (AT) natpro (DOT) com> wrote: using access97 with a table linked to sqlserver 2005 with the following * * dim rs as dao.recordset * * dim lngActionId as long * * Set rs = currentdb.openrecordset("SELECT * *FROM tblNcoActionList") * * With rs * * * * .AddNew * * * * !NcNumber = NcNumber * * * * !issueDate = Date * * * * !subject = "Corrective Action" * * * * .Update * * * * .Bookmark = .LastModified * * * * lngActionId = !actionId * * End With * * set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? Hi Roger, It is not necessary to set the db-variable. Set rs = currentdb.openrecordset() always works, at least in my applications (Access97 en up). However, the assignment of IngActionId = rs!actionId has to be done before rs.Update. HBInc.- Hide quoted text - - Show quoted text - if actionId is an autonumber field, you need to do the .update before sql server assigns it an ID- Hide quoted text - - Show quoted text - |
In my Access97 the (autonumber) actionId is available immediately
after the AddNew instruction.
Are we talking about different things?
HBInc.
#9
| |||
| |||
|
|
using access97 with a table linked to sqlserver 2005 with the following dim rs as dao.recordset dim lngActionId as long Set rs = currentdb.openrecordset("SELECT * FROM tblNcoActionList") With rs .AddNew !NcNumber = NcNumber !issueDate = Date !subject = "Corrective Action" .Update .Bookmark = .LastModified lngActionId = !actionId End With set rs = nothing first time, lngActionId is correctly set 2nd time, I get error 3167 when trying to set lngActionId looking at the table, the data has been created actionId is an autonumber field and the table has a unique timestamp field I've searched and can't find what else could be causing this problem any ideas ? |
Object-Defined Error in Access 2003/07, so I suspect that is the actual
error that is occuring. Make sure you have the latest version of Jet,
and the latest version of the SQL Server ODBC drivers. You are using an
"ancient" version of Access on a newer version of SQL Server. Your code
works in 2003/07 for me. I would try running the code in the newest
version of Access you can get. If it works, you can break it down from
there.
John
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2010, Jelsoft Enterprises Ltd.