Results 1 to 12 of 12
  1. #1
    Join Date
    May 2002
    Posts
    17

    Unanswered: rnealejnr.... Please comment

    Well I got a solution but it does not look real pretty. It is also convoluted using 4 temporary tables. Can you see a more efficient way? Here is the code and many thanks for steering me in the correct direction

    'Get all records Prior to them being changed
    MyDb.Execute _
    ("SELECT CQ2.* INTO Temp " _
    & "From CQ2 WHERE NOT Exists " _
    & "(SELECT * FROM [Contractor Qualified] AS CQ WHERE " _
    & "((CQ2.Area = CQ.Area) AND " _
    & "(CQ2.[Trade Type] = CQ.[Trade Type]) AND " _
    & "(CQ2.[Rate Above Par] = CQ.[Rate Above Par]) AND " _
    & "(CQ2.[Contractor Id] = CQ.[Contractor Id])))")

    'Create a table with all the records that have been added or changed
    MyDb.Execute _
    ("SELECT [Contractor Qualified].* INTO Temp2 " _
    & "From [Contractor Qualified] WHERE NOT Exists " _
    & "(SELECT * FROM CQ2 WHERE " _
    & "((CQ2.Area = [Contractor Qualified].Area) AND " _
    & "(CQ2.[Trade Type] = [Contractor Qualified].[Trade Type]) AND " _
    & "(CQ2.[Rate Above Par] = [Contractor Qualified].[Rate Above Par]) AND " _
    & "(CQ2.[Contractor Id] = [Contractor Qualified].[Contractor Id])))")

    'Compare 3 fields in temp against temp2 to find matching records that have been changed
    'And add them to temp FROM Temp 3

    MyDb.Execute _
    ("SELECT Temp2.* INTO Temp3 " _
    & "FROM Temp2 INNER JOIN Temp " _
    & "ON Temp2.Area = Temp.Area AND " _
    & "Temp2.[Trade Type] = Temp.[Trade Type] AND " _
    & "Temp2.[Contractor Id] = Temp.[Contractor Id];")

    MyDb.Execute ("INSERT INTO Temp SELECT * FROM Temp3;")

    MyDb.Execute _
    ("DELETE Temp2.* " _
    & "FROM Temp2 INNER JOIN Temp " _
    & "ON Temp2.Area = Temp.Area AND " _
    & "Temp2.[Trade Type] = Temp.[Trade Type] AND " _
    & "Temp2.[Contractor Id] = Temp.[Contractor Id];")

    'That leaves changed records before and after in Temp and new records in Temp2
    'Add the required fields. Name, Date etc to Temp
    MyDb.Execute "ALTER TABLE [Temp] ADD COLUMN [When Changed] DATETIME;"
    MyDb.Execute "ALTER TABLE [Temp] ADD COLUMN [By Whom] TEXT(25);"
    'Copy records FROM Temp to Change Transaction Table
    MyDb.Execute ("INSERT INTO [CQ Changes] SELECT * FROM Temp;")
    'Copy records FROM Temp2 to backup table
    MyDb.Execute ("INSERT INTO [CQ Backup] SELECT * FROM Temp2;")
    'Clean up Temporary Tables
    MyDb.Execute ("DROP TABLE CQ2")
    MyDb.Execute ("DROP TABLE Temp")
    MyDb.Execute ("DROP TABLE Temp2")
    MyDb.Execute ("DROP TABLE Temp3")
    MyDb.TableDefs.Refresh

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    What is cq2 ? Also, what is the primary key in [contractor qualified] ? Can this primary key be modified ? What is the primary table and which is the backup table ?

  3. #3
    Join Date
    May 2002
    Posts
    17
    Cq2 is a duplicate table of contractor qualified that is created prior to a user going into the form which allows them to add remove or change records.
    The primary key is the 3 fields, [contractor id],[trade type] and [area].
    (Long)

    I suppose it can be modified but it was designed to stop multiple rates being added for the same contractor ,area and trade.

    The primary table is Contractor Qualified, the Backup is CQ Backup.

    Incidentley I will be modifiying it (as soon as i find out how) to store the backup table in another access97 database.

    CQ Changes is the transaction table that only records a changed record, when it was changed and by whom.

    Regards and Thanks again.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    I started to give a solution but realized I needed additional information. In your original post, you were concerned about deleletions. Now, it seems that maybe you want to track all transactions. So for clarification, please provide what your goal is - be as descriptive as possible. Especially when it comes to the table that will hold the deletions/additions that you have discovered. What do you want to do with this information. Are you concerned with all transactions or just mainly the deletions ? And confirm that Access is the only database you will be using for this entire process.
    Last edited by rnealejr; 05-19-02 at 02:36.

  5. #5
    Join Date
    May 2002
    Posts
    17
    Sorry, yes there were two different goals. One was to track unauthorised deletions from the Items Used Table, the other was to track transactions in some other tables. The contractor qualified is to track changes being made to a contractors rate. The problem is that the users claim they are not changing the rates (which afect payments) so i need to be able to track the changes to provide management with either the proof that users are changing the figures or that the program is behaving erratically.

    The second problem with missing records is to be able to rebuild the transactions should the records be deleted inadvertenlyor on purpose. What I intend doing is to track the removal of a record by users but also in a separate table in a separate DB keep track of all the records that were ever associated with a particular job. This will enable me to rebuild the necessary transactions if a malicious user is getting into the db and mass deleting records.

    Access is the only DB that will be used

    Hope this helps

    Regards

  6. #6
    Join Date
    Feb 2002
    Posts
    2,232
    A couple of questions: Which version(s) of access are you using ? Do all the tables reside in the same database ? What program are you using to access the database (access, vb, ...) ? Do you have control over the software ? Is it a 3rd party software package ? How are you triggering the "select into" statements ?

  7. #7
    Join Date
    May 2002
    Posts
    17
    Access 97
    No the tables will reside in different database.
    VB v5 is accessing the database. I am not sure what you mean by control over the software and triggering the select statements.

    The routines will run after exiting certain forms or once a day as part of the initialiasing routines.

    Regards

  8. #8
    Join Date
    Feb 2002
    Posts
    2,232
    Are you the one that writes the vb software/code ? How do you know when to run your "select into" statements - what are you using to trigger these statements ? Where are the "select/into" statements being run ?

  9. #9
    Join Date
    May 2002
    Posts
    17
    Thanks for the quick reply,

    Yes I write the code.

    The routines will run after exiting certain forms or once a day as part of the initialiasing routines and all are being run in the VB front end

    As for trigger, I am assuming that you mean what is the scenario that causes the code to enter these routines if it is then it is as I said above

    Hope this makes it clear for you

    Regards

  10. #10
    Join Date
    Feb 2002
    Posts
    2,232
    I am thinking of 3 solutions.

    1. The solution you are proposing with a few modifications. I am
    concerned that the activity will cause performance problems as well
    as a higher potential for data corruption.
    2. Another solution is using replication in access.
    3. The last solution would mean modifying your software to create a
    transaction log table for each table you want to track. This last
    option will be more efficient. Basically, you can create a table
    that is a copy with additional fields including a guid (like a time/date
    stamp) and a type of transaction (delete, update, insert). When
    perform an update/delete/insert just propagate this transaction to
    log table.The benefit of this over your initial solution is you have less
    database activity.

    Let me know if you additional direction/discussion.

    Good luck.

  11. #11
    Join Date
    Feb 2002
    Posts
    2,232
    Which route did you take ? How is the solution working out ? Your other post requested help with external databases/tables - do you still need help ?

  12. #12
    Join Date
    May 2002
    Posts
    17
    Many thanks for the follow up,

    As i understand replication it is applied to the whoule db not tables so I have decided against that

    I like the third option of transaction logs. I am heading that way using a combination of my original solution with some modifications and transaction logs.

    The funny thing is the database has not "lost" a single record over the last 3 weeks since it has been monitored. This leads me to 2 conclusions,

    1. Some one has access to my password and is maliciously deleting records direct from the database

    2 The system when working over the network is causing mass inconsistencies and is dropping records haphazardly.

    I think the real problem is no 2. I have been trying to open the database with readonly access when on the network but it does not sem to work. I am still able to modify recordsets.

    The code I am using is as follows
    Set MyDb = OpenDatabase(StrDb, False, True, ";pwd=" & pPassword)

    I am able to open tables in an external db but I am still learning the syntax of a few joins.

    Again, many thanks for your help and replies. If you can suggest anything re the use over the database over a network it would be appreciated.

    Thanks Again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •