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

[paulked]

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

[paulked]

I've bitten the bullet and gone for the VBA option :think:

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 :whistle:

[OBP]

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.

[paulked]

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.

[OBP]

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.

[paulked]

Thanks again, I'll have a play around and see what i come up with!

Cheers:beerchug:

[OBP]

Let me know if you need an assist.

[paulked]

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

[OBP]

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

[paulked]

25429
Is the error I get from 4 of the 11 files I open.

The tblTemp is created and deleted without a problem.

[OBP]

Have you tried the Access Security settings?

https://support.office.com/en-us/article/introduction-to-access-2010-security-cae6d764-0318-4622-955f-68d9f186d6ca

[paulked]

Thanks, trusted locations... I was so wrapped up in my own little bubble! Now to tackle the query :thumb

[paulked]

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! :beerchug:

[OBP]

Great job Paul.:thumb

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

[paulked]

When it can cope with recording macros and Application.DisplayAlerts :hide: :rofl:

[OBP]

Well it has macro wizards, and I don't know display alerts, but it has lots of VBA messages. :cool:

Still it is horses for courses I suppose.

[paulked]

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 ;)

[OBP]

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?

[paulked]

I do use a lot of forms, this is a small business customer/job tracking project and the form list is
25436
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 :yes

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 ;)

[OBP]

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.

[paulked]

Sure thing :thumb

One thing that pisses me off a bit with Excel VBA forums is the amount of people who are saying "Power Query can do that". Yes, it's new to Excel, but I'd put my money on it coming from a Database background! Just a thought but maybe a new PQ forum should be set up :whistle:

[paulked]

Sorry about that rant, give me six pints and off I go!

[OBP]

I am not familiar with Power Queries, but if they use SQL then they are obviously of database origin.
I think it is good that MS recognised that many people use Excel for databasing and have continued to develop that side of it, it has come a long way since the days of Visicalc. Excel it not just a spreadsheet anymore and with VBA automation it is very powerful.

[paulked]

I haven't looked at them yet either, it's another 'to-do' item on the list!