Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 24

Thread: Get records deleted from the Main Database from 'Back Up' Databases

  1. #1
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location

    Get records deleted from the Main Database from 'Back Up' Databases

    Hi

    I have a main DB (KKDB.accdb) which I use as back-end storage from Excel. The VBA in Excel makes regular copies of the main DB as a form of backing up. The files are all in the same directory as the main DB and all start with KKDB eg KKDB-AlexMon.accdb, KKDB-JillTue.accdb, KKDB-BU.accdb etc.

    From time to time records get accidentally deleted from the main DB (more times than they should!).

    What I would like to do is loop through the other DB's in the folder, in date order, to try and find any missing records in them and restore them to the main DB.

    I really don't know how to go about this, should I use VBA or Append Queries?

    I am ok with VBA in Excel but have never written a line of VBA in Access! Likewise, Queries are new to me so I would have no idea how or where to start!

    If someone would point me in the right direction it would be most appreciated.

    Many thanks and best regards
    Semper in excretia sumus; solum profundum variat.

  2. #2
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I've bitten the bullet and gone for the VBA option

    The following code gives me all the "KKDB-" files in an array in date order:
    Option Compare Database
    Option Explicit
    
    
    Sub FindDBs()
        Dim a_dir, temp, i&, j&, oFSO As Object, oFolder As Object, oFile As Object, DBs$
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder("K:\")
        For Each oFile In oFolder.Files
            If Left(oFile.Name, 5) = "KKDB-" Then
                DBs = DBs & Format(oFile.DateLastModified, "YY/MM/DD HH:MM:SS") & " " & oFile.Name & ","
            End If
        Next
        DBs = Left(DBs, (Len(DBs) - 1))
        a_dir = Split(DBs, ",")
        For i = LBound(a_dir) To UBound(a_dir) - 1
            For j = i + 1 To UBound(a_dir)
                If a_dir(i) < a_dir(j) Then
                    temp = a_dir(j)
                    a_dir(j) = a_dir(i)
                    a_dir(i) = temp
                End If
            Next
        Next
        'Test array layout
        For i = LBound(a_dir) To UBound(a_dir)
            Debug.Print a_dir(i)
        Next
    End Sub
    Now I need help on getting the info out of those files... that's all
    Semper in excretia sumus; solum profundum variat.

  3. #3
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    A shame I didn't see this earlier, as I had that code and could have supplied it to you.
    Now we come to the updating I think that using VBA would work, however I am not sure I would be very quick on it's own.
    I would prefer to Import Append the data in to a temporary table.
    I would write a "Find Unmatched Query" to identify the missing records if you are not certain of their Index Key's integrity.
    If there is no possibility of duplication then I would use the VBA to run an Append Query (the original key will have been lost if it was an Autonumber) and then run a Delete query to remove the data from the Temp table. Rinse & Repeat as they say.
    Depending on how many records are involved you could append them all to the temp table and then run the append query.

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi there.

    Thanks for reply. The code was a nice exercise for me

    There are about 5,000 records in the particular table I'll be looking at (tblActJobs) and each record has a unique field (fldJobNo).

    So from your info, am I correct in going down the path of:

    1. Select file
    2. Import tblActJobs to a temporary table (say tblTemp)
    3. Loop through the records and if fldJobNo in tblTemp does not exist in tblActJobs then insert the record(s)
    4. Delete records from tblTemp
    5. If another file goto 1.
    Semper in excretia sumus; solum profundum variat.

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes you could do it that way.
    However using an Append query will just add the records that do not exist as the others will be rejected because of Key violations.
    No real programming involved other than getting the Query append to field correct, since 2007 the Access append wizard sometimes puts square brackets around the fields which creates an error.
    A "Delete" query will then remove all the records from the temp table ready for the next import.

  6. #6
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Thanks again, I'll have a play around and see what i come up with!

    Cheers
    Semper in excretia sumus; solum profundum variat.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Let me know if you need an assist.

  8. #8
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    OK, I've got a bit further (time isn't my friend at the moment!)

    The code below is working with one exception... I still get a potential security concern warning even with DoCmd.SetWarnings False. In Excel I would use Application.DisplayAlerts = False but it errored when I tried it, I don't know if there is a reference to this in the Access library?

        DoCmd.SetWarnings False
        For i = LBound(a_dir) To UBound(a_dir)
            temp = Split(a_dir(i), " ")
            If temp(2) <> "KKDB.accdb" Then
                DoCmd.TransferDatabase acImport, "Microsoft Access", "K:\" & temp(2), acTable, "ActJobs", "tblTemp", False
                'do query
                Debug.Print temp(2) & " completed."
                DoCmd.DeleteObject acTable, "tblTemp"
            End If
        Next
        DoCmd.SetWarnings True
    Semper in excretia sumus; solum profundum variat.

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    You are deleting the table, which is probably where the warning comes from, whereas if you use a Delete query the set warnings will work.
    Here is my code for importing various text files running some clean up queries an append query and then the delete query.

    Dim FileNm, FilePathName, Path, FileNameList() As String
        Dim FileCount As Integer
        DoCmd.SetWarnings False
        Path = Me.FilePath
        FileNm = Dir(Path & "")
        Search_path = Path ' where ?
    Search_Filter = "*.txt" ' what ?
    Docname = Dir(Search_path & "\" & Search_Filter)
    Do Until Docname = "" ' build the collection
        FilePathName = Path & Docname
        MsgBox FilePathName
    
        'DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="pp OUTINGS", TableName:="pp OUTINGS", FileName:=FilePathName, hasfieldnames:=True
        DoCmd.TransferText transferType:=acImportDelim, TableName:="Test", FileName:="C:\Users\A C\Downloads\Benjamins.txt", hasfieldnames:=True
    
    Docname = Dir
    Loop
    
    'MsgBox "Successful file imports. Updating Files", vbInformation
    DoCmd.OpenQuery "pp NQH Delete <1100"
    DoCmd.OpenQuery "pp NQH Delete Unknowns"
    DoCmd.OpenQuery "pp OUTINGS update 2A Query"
    DoCmd.OpenQuery "pp OUTINGS update 3A Query"
    DoCmd.OpenQuery "pp OUTINGS update 4A Query"
    DoCmd.OpenQuery "pp OUTINGS Append Query"
    DoCmd.OpenQuery "Delete pp OUTINGS Query"
    
    DoCmd.SetWarnings True
    MsgBox "Updates Complete"
    
    Exit Sub
    errorcatch:
    MsgBox Err.Description

  10. #10
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Error1.jpg
    Is the error I get from 4 of the 11 files I open.

    The tblTemp is created and deleted without a problem.
    Semper in excretia sumus; solum profundum variat.

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Have you tried the Access Security settings?

    https://support.office.com/en-us/art...f-68d9f186d6ca

  12. #12
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Thanks, trusted locations... I was so wrapped up in my own little bubble! Now to tackle the query
    Semper in excretia sumus; solum profundum variat.

  13. #13
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Sub FindDBs()
        Dim a_dir, temp, i&, j&, oFSO As Object, oFolder As Object, oFile As Object, DBs$, rc&
        Set oFSO = CreateObject("Scripting.FileSystemObject")
        Set oFolder = oFSO.GetFolder("K:\")
        For Each oFile In oFolder.Files
            If Left(oFile.Name, 5) = "KKDB-" Then
                DBs = DBs & Format(oFile.DateLastModified, "YY/MM/DD HH:MM:SS") & " " & oFile.Name & ","
            End If
        Next
        DBs = Left(DBs, (Len(DBs) - 1))
        a_dir = Split(DBs, ",")
        For i = LBound(a_dir) To UBound(a_dir) - 1
            For j = i + 1 To UBound(a_dir)
                If a_dir(i) < a_dir(j) Then
                    temp = a_dir(j)
                    a_dir(j) = a_dir(i)
                    a_dir(i) = temp
                End If
            Next
        Next
        DoCmd.SetWarnings False
        rc = DCount("*", "ActJobs")
        For i = LBound(a_dir) To UBound(a_dir)
            temp = Split(a_dir(i), " ")
            If temp(2) <> "KKDB.accdb" Then
                DoCmd.TransferDatabase acImport, "Microsoft Access", "C:\KK\" & temp(2), acTable, "ActJobs", "tblTemp", False
                DoCmd.OpenQuery "tblTemp Query"
                DoCmd.DeleteObject acTable, "tblTemp"
            End If
        Next
        DoCmd.SetWarnings True
        MsgBox DCount("*", "ActJobs") - rc & " Records were reclaimed.", , "Done retrieving..."
    End Sub
    And the query

    INSERT INTO ActJobs
    SELECT *
    FROM tblTempCopy;
    With the field JobNo Indexed - Yes (No Duplicates).

    Works like a charm

    Thank you for your help OBP, if you are ever in Llangollen give me a shout, I'll get you a pint!
    Semper in excretia sumus; solum profundum variat.

  14. #14
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Great job Paul.

    ps when you going to dump that old Excel for Access.

  15. #15
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    When it can cope with recording macros and Application.DisplayAlerts
    Semper in excretia sumus; solum profundum variat.

  16. #16
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Well it has macro wizards, and I don't know display alerts, but it has lots of VBA messages.

    Still it is horses for courses I suppose.
    Last edited by OBP; 11-18-2019 at 04:14 PM.

  17. #17
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    LOL. They are both great tools and have their own places in life. Used together they are very powerful and, as an Electrical Engineer, I use Excel for gathering, sorting, filtering and displaying data and Access to store it. I've never tried a live production graph in Access and, likewise, never stored hundreds of thousands of numbers in Excel.

    Harmony, perfect harmony
    Semper in excretia sumus; solum profundum variat.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It is odd that you should use Excel for gathering data and sorting as Access beats it hands down for both and for filtering and long term analysis like year over year etc.
    It can reproduce Excel maths, but it takes quite a bit of effort for more complicated stuff.
    Access Graphs are not easy.
    I used both when I worked, Access to gather, sort & filter and Excel for display purposes like graphs, especially as Excel links well with Queries.
    Excel is good for input when there is less data and is much quicker to set up for it.
    Do you use Forms in Excel?

  19. #19
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    I do use a lot of forms, this is a small business customer/job tracking project and the form list is
    Forms.jpg
    But for my core business I've developed an OLE based system that gets live data from PLC's into Excel and gives real-time graphs, process signals, throughput etc. This data is also written to Access so it can be looked back on though

    I will admit that I haven't given Access a fair crack yet, mainly because I started with Excel and have built up quite a big library of routines. That and time... if only there were 30 hours in a day
    Semper in excretia sumus; solum profundum variat.

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Yes Excel is very good for quick imports and data entry etc and users tend to just keep building on what they have.
    The problem with Access is that it is not as simple and intuitive as Excel.
    Although simple databases are easy enough, my biggest concern with it is the latest bunch of wizards do not create and design forms properly, which can lead to problems when trying to customise them.
    You have a lot of forms there and quite a few for searching, which is where Access would probably do a better job, but as you say you need time and when you are working as well it is even harder.
    If you do ever want to do more with your data in Access just let me know and I will create you some nice Queries and Forms for collating or searching data.
    I have noticed that my VBA is not as good as it was 8 to 10 years ago and I have to look up routines more often as well.
    I am sure will speak 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
  •