the following code segment fails on the update line when intN = 7,
with the error below


Dim rs4 As ADODB.Recordset

for intn = 1 to 10
set rs4 = nothing
Set rs4 = New ADODB.Recordset
rs4.Open "SELECT * FROM tblSlsDetail WHERE 1 = 2", cnn,
adOpenDynamic, adLockOptimistic
rs4.Properties("Preserve On Commit") = True
rs4.Properties("Preserve On Abort") = True
rs4.AddNew
rs4!handoverId = lngHandoverId
rs4!lineNbr = intN
rs4.Update
next intn

ODBC--call failed. ,Microsoft Office Access Database
Engine ,-2147467259
- You must use the dbSeeChanges option with OpenRecordset when
accessing a SQL Server table that has an IDENTITY column.


if I change to this
rs4.Open "SELECT * FROM tblSlsDetail WHERE 1 = 2", cnn,
adOpenDynamic, adLockOptimistic, dbSeeChanges

I get the same error
now I'm assuming the IDENTITY column means that the link table would
show an 'autonumber' field, but it doesn't
nor is it created that way in sql server

this makes me think that the error is misleading but I don't know

any ideas ?

Not the problem you are encountering, but since 1 is not = 2, why would you
expect to get a record returned ever with a "WHERE 1=2" clause?

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

"Roger" <lesperancer (AT) natpro (DOT) com> wrote

I would expect he wants to open an empty recordset with the intention of
doing nothing but add new records. Of course, executing a sql INSERT
statement directly would be a better plan.

Access Developer wrote:

Roger wrote:
I've never seen these properties used. What do you expect them to do for
you?

SQL Server database?
Also, dbSeeChanges is a DAO property as well, and probably will not evaluate
to a meaningful constant when used in an ADO call

a. Use a DAO recordset.
b. Better yet, stop using a recordset when a sql INSERT statement would do
the job berrer.

Oh, well, I use DAO instead of ADO, and I'd just open a DAO Recordset and
use the .AddNew method.

My little experiece with ADP and ADO indicated it was usable, but more work
than DAO, and no faster than ODBC-linked tables across the same network. I'm
told there are things you can do with ADO that are either not possible or
not simple with DAO, but I've not suffered for lack of thT functionlity and
I've used Access daily since Jan. 1993.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

On Aug 24, 10:22*pm, "Bob Barrows" <reb01... (AT) NOSPAMyahoo (DOT) com> wrote:
you are correct about opening an empty recordset

as to an insert statement, it's a idea, but the example I've provided
highly simplifies the fields that are updated - actually there are
many fields and the contents is sometimes based on conditions - but
I'll look into an INSERT

On Aug 24, 11:35*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
I usually use DAO, but this insert is one of many within a
transaction
and there was a problem with DAO (I don't remember the details)

the ADO version worked fine until yesterday

we are in the process of updating from office2007 to office2010,
except for access which will stay at 2007

when we did the update on the development server, this MDB failed with
an ADO issue
looks like the msado15.dll was updated by the office2010 update to a
version incompatible with access2007

we ran the 'microsoft office diagnostics' which resolved the dll
issue, but further testing uncovered this error

I moved the mdb to a server still running office2007, and the error
persisted, leading me to think that it's not related to the office2010
upgrade, but maybe thats an invalid assumption

Roger wrote:
It's rarely a good idea to send a recordset to do a query's job. On further
review, however, I see you are doing multiple inserts, so a disconnected ADO
recordset would be a good way to do it. Not quite as good as using a stored
procedure in SQL Server, but pretty close. The keys are:
1. opening a direct ADO connection to the sql server rather than using the
linked table -
sConn= "Provider=SQLOLEDB;" & _
"DataSource=NameOfServer;Initial Catalog=dbname;"
If using integrated security:
sConn=sConn & "Integrated Security=SSPI"
Standard security:
sConn=sConn & "User Id=myUsername;Password=myPassword"

2. opening an empty, client-side recordset using the adLockBatchOptimistic
lock type
3. Disconnecting the recordset by setting its ActiveConnection to Nothing
4. Perform all the addnews in the loop
5. After the loop, reconnect the recordset to the database by setting its
ActiveConnection to the connection object (cnn)
6. Call the recordset's UpdateBatch method, which sends all the changes to
the database

The code would look like this (I prefer to always name the fields I'm
retrieving from the database so as to reduce the impact on local resources
and network traffic)

dim cnn as adodb.connection, rs as adodb.recordset
dim sConn as string,sSQL as string
sconn= 'as above
sSQL="SELECT handoverId,lineNbr " & _
"FROM tblSlsDetail WHERE 1 = 2"
on error goto handler
set cnn=new adodb.connection
cnn.open sConn
set rs=new adodb.recordset
rs.CursorLocation = adUseClient
rs.open sSQL, cnn,adOpenStatic, adLockBatchOptimistic
set rs.activeconnection=nothing
for intn = 1 to 10
rs.AddNew
rs(0)= lngHandoverId
rs(1)= intN
rs.Update
next intn
set rs.ActiveConnection=cnn
rs.UpdateBatch

exit_sub:
on error resume next
rs.close:set rs=nothing
cnn.close:set cnn=nothing
exit sub

handler:
'handle the error - notify user, etc, then
resume exit_sub
end sub

My preference would actually be to create a stored procedure on the sql
server to which I pass the lngHandoverID value, and let it do everything in
sql itself. You might not have access to the server so I'm not going to
bother describing how to do this. If you wish further details let me know.

Bob Barrows wrote:
Of course, you should substitute the actual name of the table in sql server
rather than using the name of the linked table (if different).

On Aug 25, 7:43*am, "Bob Barrows" <reb01... (AT) NOyahooSPAM (DOT) com> wrote:
in this case the original error message is misleading and incorrect

I debug.printed all the fields in the recordset and tried to create
the record manually
turns out one of the actual fields that I was updating has a foreign
key to a product table, and it was missing a record

the manual entry gave me the correct error message, and I fixed the
data