db.RecordsAffected... incorrect number

[Stanski21]

Hi folks,

I've written a module to import data from one table into another. I get a daily MTD table in excel that I import through a saved import procedure, then my module grabs the first date from the imported file, deletes all records >= that date, then imports the new data, adding in some other fields from a JOIN query (things like department info and manager name etc - cos these change monthly, I need to store them in the central table in 'real time'... I know it's not 'normal' but the best solution I could come up with).

Anyway, from a developer point of view, I print how many records were deleted in the immediate window, then how many were inserted in the second run.
We should be seeing that the number for inserted records is higher than what has been deleted... but it's not.

On checking, the INSERT INTO command is working correctly, but the number being displayed is wrong. I.e. It successfully inserted 1267 records this morning, but the RecordsAffected tells me there were only 1187... but the first figure (records deleted) is correct.

I've scoured these forums and other sites and can't figure out what's wrong.

The only thing I can think of that might affect it is that before I insert the records, I update any employee IDs with a preceding 0 if they are only 5 numbers - I need this to link to a seperate table.

Anyway, here's some of the code...

Private Sub btnimportnew_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim dtefirst As String
Dim strRSSQL As String
Dim strdelSQL As String
Dim strupdt As String
Dim empid As String
Dim intrecordsupd As Integer

DoCmd.SetWarnings (False)

'find first date in new data
strRSSQL = "SELECT * FROM NEWDATA ORDER BY [NEWDATA]![LAST CONTACT DATE];"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strRSSQL, dbOpenDynaset, dbReadOnly)
rs.MoveFirst
dtefirst = sqldate(rs![Last Contact Date])
rs.Close
Set rs = Nothing

'Debug.Print dtefirst

'delete records from DATA where dates are included in new data
strdelSQL = "DELETE * FROM DATA WHERE [DATA]![LAST CONTACT DATE]>=" & dtefirst & ";"
db.Execute strdelSQL
intrecordsupd = db.RecordsAffected

[I]This one works fine...[/I]
Debug.Print "There were " & intrecordsupd & " records deleted from the DATA table"

'add preceding zero to len=5 ids
Set rs = db.OpenRecordset("SELECT AGENTLNIATA FROM NEWDATA ORDER BY AGENTLNIATA;", dbOpenDynaset, dbSeeChanges)
rs.MoveFirst
    Do Until rs.EOF = True
        If Len(rs!AGENTLNIATA) = 5 Then
            empid = rs!AGENTLNIATA
            empid = "0" & empid
            rs.Edit
            rs!AGENTLNIATA = empid
            rs.Update
        End If
        rs.MoveNext
    Loop
              
Set rs = Nothing

'move new data into DATA
strupdt = "INSERT INTO DATA SELECT * FROM qry_NEW_DATA_CLEAN;"
db.Execute strupdt
intrecordsupd = db.RecordsAffected

[I]This one gives the wrong number...[/I]
[B]Debug.Print "There were " & intrecordsupd & " records inserted into the DATA table"
[/B]
'update tables
DoCmd.RunSQL ("DELETE * FROM NEWDATA;")
Set db = Nothing

DoCmd.SetWarnings (True)

MsgBox "The new data has been imported", , "Done!"

End Sub

I'm sure I'm missing something (I'm still quite new at this) but I cannot seem to find the answer anywhere.

Thanks in advance

 

[Ranman256]

use a select query and get the recordcount.
use that same select query in the delete.

 

[Stanski21]

That means running the same query twice though. That's why I thought RecordsAffected would be the best way...

 

[The_Doc_Man]

First, a comment. There are various schools of thought on this, but I always prefer to close a recordset before setting it to Nothing. There are those who will disagree with the need, but in my personal opinion, and as a matter of style more than substance, I would close the recordset before de-instantiating it. I believe that the recordset gets closed by de-instantiation, but my thought is more of a "habit" that is more helpful than not. Always close what you open when you are done with it. On recordsets it might not matter but on other classes of objects, it might.

Second, your recordset operations seem a bit clunky. You might get the same effect faster with

DTEFIRST = DMIN( "[LAST CONTACT DATE]", "NEWDATA", "[LAST CONTACT DATE] > #2-Jan-1900#" )

The criteria clause prevents you from taking a DTEFIRST of 0 if any date had been left blank or null, because the system reference date (day 0) is 31-Dec-1899. If your date field on input is null, this is what you might get back from it. The domain aggregate function DMIN will give you back the earliest valid date in your dataset.

UPDATE NEWDATA SET AGENTLNIATA = "0" & AGENTLNIATA WHERE 
LEN(AGENTLNIATA) = 5 ;

This will handle that recordset loop faster. The ORDER BY clause you had, at least in this context, has no effect when all is "said and done." The thing you are changing actually doesn't depend on the order of your AGENTLNIATA field like you might think because you are treating that field like a string, not a number, so the string will be sorted as a LEFT JUSTIFIED string. And that means that you will sort in the order 1, 10, 100, 1000, 10000, 11, 110, 111, ..., 19999, 2, etc. Just let SQL sort it out for you based on the only meaningful criterion - length of the field.

Third, is the table DATA internal or external to your DB file? I.e. is it residing on another machine that provides SQL services? Like MySQL, SQL Server, ORACLE, any other SQL database engine? I recall some articles where having an external SQL engine will sometimes not work as well, though what you are doing with the DAO.DATABASE variable is correct.

Fourth, other than the two points I mentioned, your routine looks OK. For your claim on being new, you are doing pretty well. Your logic flow is clean and your intent is clear just from reading the code.

Fifth, it occurs to me that the acid test is to make a couple of "SELECT..." SQL statements to match your DELETE and INSERT queries' WHERE clauses. Then open those queries to get the exact counts. Maybe even do each step by hand using the IMMEDIATE window to determine DTEFIRST, or just sort the datasheet for the appropriate SELECT statement.

The reason this is an "acid" test is because you can SEE what numbers come back at the bottom of each datasheet view. Then you can run the code in single-step mode to see what numbers come back. If you are new at this, you don't have a lot of debugging tools in your toolkit. (Actually you do, you just don't know how to use them all...)

 

[Stanski21]

Thanks!

Yeah I'll certainly update the Update line... it was on my to do list TBH... The code as it stands today was an 'accessified' version of what I needed to do in Excel way back when the database was held there (=IF(LEN(<cell>)=5, "0"&<cell>,<cell>) and autofill!).

And I appreciate the suggestion on grabbing the earliest date in a more efficient manner. I think when I wrote the routine a few months ago, I tried that but couldn't get it working and it was (from reading your explanation) probably because the import function would always drag in an empty record so the date was coming back as 0. I'll give that a go.

The DATA table is in Access on a backend database, but there are plans to move it onto a different server. I think the one we use is Oracle based but that's all I know at the moment.

Many thanks on your words of my work... I've learned a lot from these forums and other sites (shhh!). I have been working this for about a year now but still regard myself as new!

I've done a few tests as you suggest already and from them I can see that the correct number of records are being inserted. The main one being the final report that goes out shows the expected number of records in its count calculation... so I KNOW the SQL commands are working as they should be... but when the RecordsAffected class tells me different, I start to doubt myself.

I'll try, when I update the other bits, to close off the recordset before de-instantiation for the second run and see if that helps.

Is it worth closing the DAO.Database instance as well and re-starting it for the second check?

 

[The_Doc_Man]

Is it worth closing the DAO.Database instance as well and re-starting it for the second check?

No. Like the rs variable, the db variable is locally instantiated and will vanish when you Exit Sub or End Sub. The reason I suggested closing is actually not relevant for recordsets and database pointers, but it is the habit that I feel is important.

My comments about opening and closing, as I said, are probably NOT that important for recordsets declared in a subroutine, because they get dissolved or deinstantiated when you do the Exit Sub or End Sub. But as a habit, it is good to be aware of what you have open at the time and close it when you are done with it. That habit isn't SO terrible if you don't establish it in VBA for simple things, but if/when you start playing with application objects, it becomes more important.

In the original code, you used the recordset twice, deinstantiating each time. From a pure efficiency standpoint, you would have taken less time (by a millisecond or so) to just CLOSE the first recordset before you opened it again to a different SQL source. But what you did wasn't terrible at all and was unlikely to be the source of your problem. So as to your comments about closing things as a test, that is probably not going to pan out, but it is a simple code change to test it if you wanted.

As to getting the wrong answer for inserted records, that is puzzling. I see you don't have error handling in your code AND you have warnings turned off. For testing purposes only, might I suggest that you don't disable warnings? See if you get an error (not just the "you are about to delete" warning).

Also, when you get more comfortable with error handling, I have another suggestion for you to consider. When doing a db.Execute <sql> there is a second option that is super useful for making your life easier - and it is quieter than having to diddle with warnings all the time.

There is a second option on db.Execute, dbFailOnError, that does two things: First, if you take an error in the SQL statement, you get an error trap and can issue a message, take corrective action, terminate the current form, or some other appropriate action. Second, the SQL statement gets rolled back as though it had never been issued! So if there IS a corrective action that would allow a retry, your dataset is intact awaiting the retry.

I don't recommend this option to rank beginners but it looks like your VBA should be robust enough for you to figure this out.

Also, now that you KNOW that the queries are inserting the right number of records, you could (if you wished) put a breakpoint on the db.Execute, then single-step it. At that point, you could open the Immediate window to do a Debug.Print db.RecordsAffected and get the activity count you were interested in seeing.

Finally, if there is a reasonable way to do this, perhaps include a DCOUNT test for the number of records in NEWDATA < x = DCOUNT( "*", "NEWDATA" ) > and then get the records affected count as well. If they don't match across several attempts, see if you can match up the differences in some way. Like, is the error always the same amount? Is it always a variable amount?

You said this this an ORACLE back-end. So I've not played with an Access/ORACLE combination, but I'm wondering if your setup allows you to have pass-through queries for your actions. Shouldn't be that way without an explicit declaration as such, but my ORACLE experience was on a bigger system, mainframe class, and I know ORACLE is often more than just a bit twitchy. If you are NOT running synchronously, then I wonder if a brief delaying statement would make the counts more reliable.

Just for snorts & giggles, look at this article:

https://msdn.microsoft.com/en-us/library/office/ff823202.aspx

Then insert a DBEngine.Idle dbRefreshCache statement before you grab the records affected count. You should not need this, but if it works to make the count accurate then I might have an idea of what was happening. And if it has no beneficial effect, well, at least it is harmless.

 

[Galaxiom]

DoCmd.RunSQL ("DELETE * FROM NEWDATA;")

DoCmd.SetWarnings (True)

Although they will work, both these line of code are subtlety incorrect.

They are not functions so the parameters should not be surrounded by parentheses. Notice the space that is automatically inserted between the name and the open parenthesis (unlike in a function).

What those parentheses do is force the parameter to be passed ByVal. In these cases there is no effect on them but if the Method was expecting an object as a parameter then the call would fail.

 

[Galaxiom]

My comments about opening and closing, as I said, are probably NOT that important for recordsets declared in a subroutine, because they get dissolved or deinstantiated when you do the Exit Sub or End Sub. But as a habit, it is good to be aware of what you have open at the time and close it when you are done with it.

Although I have no hard proof, I believe that with Recordsets that the Close command is probably more important than the deinstantiation. Without it, the database engine would not receive an explicit notification to close it and release any locks. I suspect they might only be released after a timeout instead.

 

[The_Doc_Man]

Excellent thought, G!

And good catch on the parentheses. For a while I was looking at the forest and not seeing all of the trees.