RecordsAffected property

S

stefania nj

Hi,
I need help understanding how the RecordsAffected property woks.

I am using the recordsaffected property to capture the number of
records inserted into a table.
the code is pretty simple

RQDb.Execute sql, dbFailOnError
lngCount = RQDb.RecordsAffected

However it is not working it returns 1 even when it fails to insert
the record due to a key violation and should return 0.
why?

If I run the query manually I get two messages
1) You're about to append 1 row(s) the I click on yes and I get the
other message
2) Microsoft Access cannot append all the records in the append query.

Thank you in advance for your help. Stefania

Note. I am using Access 2003
 
D

Dirk Goldgar

stefania nj said:
Hi,
I need help understanding how the RecordsAffected property woks.

I am using the recordsaffected property to capture the number of
records inserted into a table.
the code is pretty simple

RQDb.Execute sql, dbFailOnError
lngCount = RQDb.RecordsAffected

However it is not working it returns 1 even when it fails to insert
the record due to a key violation and should return 0.
why?

If I run the query manually I get two messages
1) You're about to append 1 row(s) the I click on yes and I get the
other message
2) Microsoft Access cannot append all the records in the append query.

Thank you in advance for your help. Stefania

Note. I am using Access 2003
Click to expand...


That shouldn't happen, and doesn't for me in a simple test. Is an error
raised by your call to the Execute method? If so, how are you getting back
to the line that sets lngCount = RQDb.RecordsAffected? Is it possible that
lnCount has been set by some previous statement, and is not reset because
the Execute statement raised an error?
 
S

stefania nj

That shouldn't happen, and doesn't for me in a simple test.  Is an error
raised by your call to the Execute method?  If so, how are you getting back
to the line that sets lngCount = RQDb.RecordsAffected?  Is it possible that
lnCount has been set by some previous statement, and is not reset because
the Execute statement raised an error?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...

No error is happening and lngCount is not used in any previous
statement and to make sure I did the following
RQDb.Execute sql, dbFailOnError
lngCount=0
lngCount = RQDb.RecordsAffected

and I still get lngCount=1

Could it be caused by the following:
Private Sub Form_Load()



Set wrks = DBEngine.Workspaces(0)
Set RQDb = wrks(0)

End Sub
 
S

stefania nj

No error is happening and lngCount is not used in any previous
statement and to make sure I did the following
RQDb.Execute sql, dbFailOnError
lngCount=0
lngCount = RQDb.RecordsAffected

and I still get lngCount=1

Could it be caused by the following:
Private Sub Form_Load()

   Set wrks = DBEngine.Workspaces(0)
   Set RQDb = wrks(0)

End Sub
Click to expand...

Please ignore the workspace is not the issue I tested it. I am
inserting into a linked access table. could that be the issue?
 
D

Dirk Goldgar

stefania nj said:
No error is happening and lngCount is not used in any previous statement
and to make sure I did the following
RQDb.Execute sql, dbFailOnError
lngCount=0
lngCount = RQDb.RecordsAffected

and I still get lngCount=1

Could it be caused by the following:
Private Sub Form_Load()

Set wrks = DBEngine.Workspaces(0)
Set RQDb = wrks(0)

End Sub
Click to expand...

I take it that wrks and RQDb are module-level, or maybe global, variables?
The only relevance I can think of for that is if some other process is
performing a database operation using RQDb, in between the time you run the
..Execute and the time you get .RecordsAffected. That's pretty farfetched,
and only you can say whether it's conceivable in your situation.

If no error is occurring, then I think the SQL must be executing
successfully and affecting one record. You can check that by modifying the
code for test purposes like this:

lngCount=0
Err.Clear
RQDb.Execute sql, dbFailOnError
lngCount = RQDb.RecordsAffected
MsgBox _
"Executed SQL = " & sql & vbCr & vbCr & _
"Error = " & Err.Number & vbCr & _
"Count = " & lngCount

Run it like that, please, and report the results.
 
S

stefania nj

I take it that wrks and RQDb are module-level, or maybe global, variables?
The only relevance I can think of for that is if some other process is
performing a database operation using RQDb, in between the time you run the
.Execute and the time you get .RecordsAffected.  That's pretty farfetched,
and only you can say whether it's conceivable in your situation.

If no error is occurring, then I think the SQL must be executing
successfully and affecting one record.  You can check that by modifyingthe
code for test purposes like this:

    lngCount=0
    Err.Clear
    RQDb.Execute sql, dbFailOnError
    lngCount = RQDb.RecordsAffected
    MsgBox _
        "Executed SQL = " & sql & vbCr & vbCr & _
        "Error = " & Err.Number & vbCr & _
        "Count = " & lngCount

Run it like that, please, and report the results.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
The results have not change. I still get 1. But if I run the query
manually I get the error.

Executed SQL = INSERT INTO TBL_REINSPECTIONS ( INSPECTION_ID,
GRP_PROFILE_ID, REINSPECTOR_USER_ID, REINSPECTOR_NAME,
REINSPECTION_DT, REINSPECTION_TYPE, EST_AMT, INSPECTION_ADDRESS,
INSPECTION_STATE, INSPECTION_ZIPCODE, CLOSED_DT, REINSPECTION_ADDRESS,
REINSPECTION_STATE, REINSPECTION_ZIPCODE, LOSS_TYPE, KEY_SYMBOL,
INSPECTION_LOC, SOURCE, REINSPECTION_STATUS, REINSPECT_TYPE,
REINSPECTION_REMARKS, BODY_RATE, FRAME_RATE, PAINT_RATE,
MECHANICAL_RATE, LABOR_TAX, PARTS_TAX, GROSS_AMT, SUP_AMT, ADD_DATE,
LAST_EDIT )SELECT U.INSPECTION_ID, R.GRP_PROFILE_ID,
R.REINSPECTOR_USER_ID, R.REINSPECTOR_NAME, R.REINSPECTION_DT,
R.REINSPECTION_TYPE, U.EST_AMT, U.ADJ_ADDR, U.ADJ_STATE, U.ADJ_ZIP,
U.CLOSED_DT, R.REINSPECTION_ADDRESS, R.REINSPECTION_STATE,
R.REINSPECTION_ZIPCODE, R.LOSS_TYPE, R.KEY_SYMBOL, R.INSPECTION_LOC,
R.SOURCE, R.REINSPECTION_STATUS, R.REINSPECT_TYPE,
R.REINSPECTION_REMARKS, R.BODY_RATE, R.FRAME_RATE, R.PAINT_RATE,
R.MECHANICAL_RATE, R.LABOR_TAX, R.PARTS_TAX, R.GROSS_AMT, R.SUP_AMT,
R.ADD_DATE, R.LAST_EDI
T FROM (TMP_UPLOAD AS U INNER JOIN TMP_INSPECTIONS AS I ON
U.TMP_INSPECTION_ID = I.INSPECTION_ID) INNER JOIN TMP_REINSPECTIONS AS
R ON I.INSPECTION_ID = R.INSPECTION_ID WHERE U.UPLOAD=-1;

Error = 0
Count = 1
 
D

Dirk Goldgar

It certainly seems to be saying that is executing successfully. Have you
verified that it isn't?

You are executing a SQL string here, but you say that when you run the query
manually, you get a key violation. How are you running it manually? Is
this SQL also in a stored query, or are you invoking DoCmd.RunSQL, or what?
Can you compare the SQL here with the SQL that you are executing manually,
to make 100% sure they are identical?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)


The results have not change. I still get 1. But if I run the query
manually I get the error.

Executed SQL = INSERT INTO TBL_REINSPECTIONS ( INSPECTION_ID,
GRP_PROFILE_ID, REINSPECTOR_USER_ID, REINSPECTOR_NAME,
REINSPECTION_DT, REINSPECTION_TYPE, EST_AMT, INSPECTION_ADDRESS,
INSPECTION_STATE, INSPECTION_ZIPCODE, CLOSED_DT, REINSPECTION_ADDRESS,
REINSPECTION_STATE, REINSPECTION_ZIPCODE, LOSS_TYPE, KEY_SYMBOL,
INSPECTION_LOC, SOURCE, REINSPECTION_STATUS, REINSPECT_TYPE,
REINSPECTION_REMARKS, BODY_RATE, FRAME_RATE, PAINT_RATE,
MECHANICAL_RATE, LABOR_TAX, PARTS_TAX, GROSS_AMT, SUP_AMT, ADD_DATE,
LAST_EDIT )SELECT U.INSPECTION_ID, R.GRP_PROFILE_ID,
R.REINSPECTOR_USER_ID, R.REINSPECTOR_NAME, R.REINSPECTION_DT,
R.REINSPECTION_TYPE, U.EST_AMT, U.ADJ_ADDR, U.ADJ_STATE, U.ADJ_ZIP,
U.CLOSED_DT, R.REINSPECTION_ADDRESS, R.REINSPECTION_STATE,
R.REINSPECTION_ZIPCODE, R.LOSS_TYPE, R.KEY_SYMBOL, R.INSPECTION_LOC,
R.SOURCE, R.REINSPECTION_STATUS, R.REINSPECT_TYPE,
R.REINSPECTION_REMARKS, R.BODY_RATE, R.FRAME_RATE, R.PAINT_RATE,
R.MECHANICAL_RATE, R.LABOR_TAX, R.PARTS_TAX, R.GROSS_AMT, R.SUP_AMT,
R.ADD_DATE, R.LAST_EDI
T FROM (TMP_UPLOAD AS U INNER JOIN TMP_INSPECTIONS AS I ON
U.TMP_INSPECTION_ID = I.INSPECTION_ID) INNER JOIN TMP_REINSPECTIONS AS
R ON I.INSPECTION_ID = R.INSPECTION_ID WHERE U.UPLOAD=-1;

Error = 0
Count = 1
 
R

Risse

stefania nj said:
Hi,
I need help understanding how the RecordsAffected property woks.

I am using the recordsaffected property to capture the number of
records inserted into a table.
the code is pretty simple

RQDb.Execute sql, dbFailOnError
lngCount = RQDb.RecordsAffected

However it is not working it returns 1 even when it fails to insert
the record due to a key violation and should return 0.
why?

If I run the query manually I get two messages
1) You're about to append 1 row(s) the I click on yes and I get the
other message
2) Microsoft Access cannot append all the records in the append query.

Thank you in advance for your help. Stefania

Note. I am using Access 2003
Click to expand...
 
S

stefania nj

Hi,
I am sorry for the blunder. It does work. I run the query manually and
this time did execute correctly.
I apologize for the distress. I guess it was a bad afternoon
yesterday.
Thanks again. Stefania
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

QueryDefs - Execute - Won't Run 4
RecordsAffected property 1
Append data - key violation 3
Append Query Fails, MS Access 2000 bug? 5
Append only Null records in table 2
Wierd Append error 6
Append Queries in 2007 5
Append Access Data to SQL Server Table 12

Top