I understand... Raises another issue, however.... Is there a relatively simple way to remove these entries from the "dirty" table in the same process that stuffs them in the "clean" table?



JP

Well, since you are going to dump the dirty table after you use it, and before you use it, at the beginning AND end of this sub, do this: Create a delete query to empty the whole table called qryDELETEDirtyData (wasn't that a Kevin Bacon movie?), then:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDELETEDirtyData"
DoCmd.SetWarnings True

That will empty the entire table first to make sure there is nothing there before you move data into it, and then delete it all after, just to be safe.

I thought you were moving data in and then comparing it in the same event. If not, then don't put this at the beginning of the sub.

mmcdonal

Thanks again for the reply!

 Currently, the "load the dirty table" part of this process is in a separate VBScript, but the ultimate plan is to roll the "kill dups and move to clean table" into the same VBScript module, so I guess all I really need is to call that "empty the dirty table" query after both of the above processes have finished...

   So, it'll looks like:

1) load dirty table from .csv files
2) move only non-dup records to "clean" file
3) empty dirty table

oh, and FWIW, since the records being created represent occurrences of a situation that will take more than one second to resolve, the Date, Time, and machine number fields are enough to identify the record as "truly a dup", so I think I'd be safe to SELECT DISTINCT in the dirty table, as any records that are duplicates in those fields are indeed duplicates in all others, too.

Thanks again for your assistance!

Where do I send the case of single-malt (grin)?

Joe

JP

I would also clean the intermediate table before pulling data in just to make sure your subsequent processes don't include old data. And to keep the subsequent operations on a smaller scale.

IS IT ME OR DO THESE GOD_AWFUL SMILEY CENTRAL BANNERS DRIVE YOU FRIGGIN NUTS?!?!

Anyway, just a bottle of Balvanie Double Wood is enough.



mmcdonal

Sigh....

              The DoCmd.OpenQuery "DeleteDirtyData" generates a nasty-gram....
          "You can't carry out this action at the present time"
 Code:800A09B6

          Might it be because the connection is open? Do I need to close it first? All the dataset closes have been done before I issue any DoCmd.anythings

          Any ideas?
 Joe


JP

It depends on where you are.

If you are in code inside Access, then I am not sure why this wouldn't work.

If you were in a script outside Access, then you need to do the connection and recordset thing, and the sSQL string would be "DELETE FROM tblIntermediate". Then you would only need to OPEN the recordset, and the data would be deleted.

Where is your code? (VBScript or Access event?)

mmcdonal

Ok, it's in the same VBScript I've been working with.. down below the loop...

The "DELETE * FROM ProductionData" appears to happen ok, but I get a nasty-gram regrding the "rs4.close" after it... saying I can't do that when it's closed. Could it be that this delet closes automatically??

           Joe

 P.S. - Here's the code:

Dim rs1, rs2, rs3, rs4, cn, i, AccApp
Dim sT1, sT2, sT3
Dim sSQL1, sSQL2, sSQL3, sSQL4
set cn = CreateObject("ADODB.Connection")
Set rs1 = CreateObject("ADODB.Recordset")
Set rs2 = CreateObject("ADODB.Recordset")
Set rs3 = CreateObject("ADODB.Recordset")
Set rs4 = CreateObject("ADODB.Recordset")
cn.Open "DSN=EssexSmartSys;"
i = 0
sSQL3 = "SELECT * FROM PDDataClean" 'All records from "clean" table
rs3.Open sSQL3, cn, 3, 3
sSQL2 = "SELECT * FROM ProductionData" 'All records from "dirty" table
rs2.Open sSQL2, cn, 3, 3
rs2.MoveFirst
    Do Until rs2.EOF
        sT1 = rs2("KeyField")
        sSQL1 = "SELECT Count(*) As UnMatched FROM PDDataClean WHERE [KeyField] = '" & sT1 & "'"
        rs1.Open sSQL1, cn, 3, 3
            If IsNull(rs1("UnMatched")) Or rs1("UnMatched") = 0 Then
                rs3.AddNew
                rs3("KeyField") = sT1
                rs3("pdDate") = rs2("pdDate")
                rs3("pdTime") = rs2("pdTime")
                rs3("OperatorName") = rs2("OperatorName")
                rs3("CurrentShift") = rs2("CurrentShift")
                rs3("MachineName") = rs2("MachineName")
                rs3("StopCode") = rs2("StopCode")
                rs3("StopTime") = rs2("StopTime")
                rs3.Update
                i = i + 1
            End If
        rs1.Close
        rs2.MoveNext
    Loop
rs2.Close
rs3.Close
sSQL4 = "DELETE * FROM ProductionData"
rs4.Open sSQL4, cn, 3, 3
rs4.Close
MsgBox i & " records added."

JP

I have encountered that problem in the past, and unfortunately don't have a work around for it, other than '.

I just leave that out. That should be okay since when WScript.exe closes, it releases that connection.


mmcdonal

and recordset. See, you aren't even closing your connection.

mmcdonal

That's true.. There's no "cn.Close" anywhere, and THAT's never caused any trouble, so... Guess I'll just let WScript take care of it...

Joe

JP