Help with data loss resulting from referential integrity

[April15Hater]

Hi guys-

I lost an incredibly large amount of information from my database....and I need some help. Let me give some background:

Let me begin by saying this is affecting most of the tables in my back end. Long story short- a record from tblClient was erased, and everything downstream from there has referential integrity enforced and was likewise erased. tblClient is 1-M to tblJob is 1-M to tblFunction is 1-M to tblProductioninput, 1-M to tblInvoiceFunction, and 1-M to tblContractorFunction, and all three of those are linked 1-M to other tables. So needless to say there is a lot of data that has been lost here.

Fortunately I have a backup which shows the client record that was erased and all of the information underneath it. There is simply too much that has changed to restore the backup in it's entirety, so we think that it would be best to try to restore this client. I don't even know where to being with this problem. I'm tempted to go through every table and copy everything over and create all new records, but I know that is going to be a time consuming and arduous task. I'm hoping there's an easier way to attack this. Does anyone have any suggestions?

Thanks,

Joe

 

[JANR]

To stop this from happening again uncheck Cascading delete from the relationships between your tables.

This will stopp records from deleting when the parent record one the ONE-side gets deleted.

The downside is that you yourself must create a deleterutine when the parent record is deleted on purpose.

JR

 

[georgedwilkinson]

Additionally, I NEVER delete records...just set the delete flag on them and filter them out of result sets.

You have a very large job ahead of you. You'll have to turn off all your auto-numbers before you re-insert the data. Then you'll have to turn the auto-numbers back on and re-seed them. Needless to say, you should only do this when no-one else can be on the system.

Otherwise, you'll pretty much have to re-insert the data one step at a time, taking note of the new PKs so you can insert those values into the FKs.

Lots of work either way.

Two things of REAL import here:
1. Don't allow users to delete data.
2. Turn off cascade delete on all relationships.

 

[April15Hater]

I figured I had my work cut out for me... George, how does one reseed a table? I was going to go with your second route, but the first way looks way more appealing.

 

[Banana]

Simplest way to reseed is to use DDL query:

ALTER TABLE <mytable> ALTER COLUMN <myID> COUNTER(<the new seed>, 1);


You also can insert into autonumbers without needing to changing it:

INSERT INTO <mytable> (<myID) VALUES (x);

So you just need to link a copy of production to the backup, locate the client, then insert the record, including the autonumber column into the copy, then repeat the process for each table where the client ID is used.

HTH.

 

[April15Hater]

What is '<the new seed>'?

Not quite sure I follow your second idea though...

 

[georgedwilkinson]

And be careful, the process I described earlier will not work!

Keep your auto-numbers turned on and use Banana's method.

Make backups and lock everyone else out of the back-end.

New seed will be the maximum of the current PK plus 1.

 

[georgedwilkinson]

But theoretically if you use Banana's method, you won't need to re-seed.

 

[Banana]

Oh, that's right, George. What was I thinking?

 

[Banana]

April15Hater,

The idea is that you just do a series of INSERT INTO... queries from the backup into the copy of production copying the missing records over. If you include the Autonumber column in INSERT INTO, it will insert the original ClientID for you even though you couldn't do this via UI.

 

[April15Hater]

Even though not everything is fk with Client ID?

For example:
tblClient (PK ClientID)
tblJob (PK JobID) [FK ClientID]
tblFunction (PK FunctionID) [FK Job ID]

In my database, all of the tblFunction records are missing even though they don't have ClientID as it's FK. In your solution, do I need to turn off autonumbering or does the insert into query override that?

 

[Banana]

Insert Into queries should have the ID, both as a autonumbering primary key and as a foreign key ID.

So...

INSERT INTO tblClient (ClientID, ...) SELECT ClientID, ... FROM BackUptblClient WHERE ClientID = <missing Client ID>;
INSERT INTO tblJob (JobID, ClientID, ...) SELECT JobID, ClientID ... FROM BackUptblJob WHERE ClientID = <ditto>;
INSERT INTO tblFunction (FunctionID, JobID, ...) SELECT FunctionID, JobID, ... FROM BackUptblFunction WHERE <missing JobIDs>;

BTW, the order of queries is important here as the subsequent queries will need to have the ClientID & JobID inserted into their table.

So, no re-seeding required.

HTH.

 

[April15Hater]

I'm still a little confused... So when it inserts the backup into the source table, isn't it going to want to renumber it? Or does the Insert Into query override that?

 

[Banana]

It only override if you explicitly insert the ID column that is the autonumber.

Thus, if we have a missing client, John Doe, and his ClientID is 7 and in your production copy, the seed is set at say, 42, then this query:

INSERT INTO tblClient (FName, LName) VALUES ('John', 'Doe');

will produce this:

43 	John 	Doe

But this query:

INSERT INTO tblClient ([color=red]ClientID[/color], FName, LName) VALUES ([color=red]7[/color], 'John', 'Doe');

will produce either this:

7 	John 	Doe

or raise an error if there's already a record using the ClientID of 7.


Did it clear things up?

 

[April15Hater]

Yes and no... I see the difference and what each does, but I'm still not sure if I need to turn auto number off..?

 

[Banana]

No. you don't need to turn off the autonumber at all, nor do you need to reseed.

 

[April15Hater]

Gotcha! And it seems to be working like a charm. I'm still going through it now, but I'm even making use of the subqueries you taught me earlier rather than typing out thousands of ID's:

INSERT INTO tblInvoiceFunction (InvoiceFunctionID, FunctionID, FunctionType, Price, InvoiceColumn)
SELECT InvoiceFunctionID, FunctionID, FunctionType, Price, InvoiceColumn
FROM tblInvoiceFunction1
WHERE InvoiceFunctionID IN
                (SELECT InvoiceFunctionID FROM tblInvoiceFunction1 WHERE FunctionID IN
                      (SELECT FunctionID FROM tblFunction1 WHERE JobID In
                          (SELECT JobID FROM tblJob1 WHERE ClientID = 6
                            )
                    )
            );

 

[neileg]

Let me clarify.

In a table with an autonumber field, inserting a record with a null in the field will result in a new autonumber. Inserting a record with a valid non-duplicate value in the field will result in that value being retained. Don't attempt to turn off the autonumber - it's not neccesary and it will cause further problems.

 

[April15Hater]

Thanks Neil-

Turns out this wasn't as bad of a deal as I thought. Along with the INSERT INTO SQL that I ran, I also wrote a VBA routine that verified everything for me. As it turns out my SQL idea didn't work so well as there were some things that were missing. All is fine and dandy now...Thanks for all the help guys! Here's the routine I wrote. It's fairly basic, but WTH:

Public Sub CheckRestoreCompleteness()
Dim rsJobList As ADODB.Recordset
Dim rsFunctionList As ADODB.Recordset
Dim rsContractorFunctionList As ADODB.Recordset
Dim rsInvoiceFunctionList As ADODB.Recordset
Dim rsProductionInputDetailList As ADODB.Recordset
Dim rsFunctionTrackingList As ADODB.Recordset
Dim rsProductionTrackingList As ADODB.Recordset
Dim rsProductionInvoiceDetailList As ADODB.Recordset
Set rsJobList = New ADODB.Recordset
Set rsFunctionList = New ADODB.Recordset
Set rsContractorFunctionList = New ADODB.Recordset
Set rsInvoiceFunctionList = New ADODB.Recordset
Set rsFunctionTrackingList = New ADODB.Recordset
Set rsProductionTrackingList = New ADODB.Recordset
Set rsProductionInputDetailList = New ADODB.Recordset
Set rsProductionInvoiceDetailList = New ADODB.Recordset

With rsJobList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT JobID FROM tblJob1 WHERE ClientID = 6"
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREJobID = strWHEREJobID & "JobID = " & !JobID & " OR "
        strWHEREtblFunctionJobID = strWHEREtblFunctionJobID & "tblFunction1.JobID = " & !JobID & " OR "
        intJobID = DLookup("JobID", "tblJob", "JobID = " & !JobID)
        If Nz(intJobID, "") = "" Then
            MsgBox "Fix Jobs"
        End If
        .MoveNext
    Loop
    .Close
End With

With rsFunctionList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT FunctionID FROM tblFunction1 WHERE " & Left(strWHEREtblFunctionJobID, Len(strWHEREtblFunctionJobID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREFunctionID = strWHEREFunctionID & "FunctionID = " & !FunctionID & " OR "
        strWHEREtblContractorFunctionFunctionID = strWHEREtblContractorFunctionFunctionID & "tblContractorFunction.FunctionID = " & !FunctionID & " OR "
        strWHEREtblProductionInputFunctionID = strWHEREtblProductionInputFunctionID & "tblContractorFunction.FunctionID = " & !FunctionID & " OR "
        strWHEREtblInvoiceFunctionFunctionID = strWHEREtblInvoiceFunctionFunctionID & "tblInvoiceFunction.FunctionID = " & !FunctionID & " OR "
        intFunctionID = DLookup("FunctionID", "tblFunction", "FunctionID = " & !FunctionID)
        If Nz(intFunctionID, "") = "" Then
            MsgBox "Fix Functions"
        End If
        .MoveNext
    Loop
    .Close
End With

With rsInvoiceFunctionList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT InvoiceFunctionID FROM tblInvoiceFunction1 WHERE " & Left(strWHEREFunctionID, Len(strWHEREFunctionID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREInvoiceFunctionID = strWHEREInvoiceFunctionID & "InvoiceFunctionID = " & !InvoiceFunctionID & " OR "
        intInvoiceFunctionID = DLookup("InvoiceFunctionID", "tblInvoiceFunction", "InvoiceFunctionID = " & !InvoiceFunctionID)
        If Nz(intInvoiceFunctionID, "") = "" Then
            MsgBox "Fix InvoiceFunctions"
        End If
        .MoveNext
    Loop
    .Close
End With

With rsFunctionTrackingList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT FunctionTrackingID FROM tblFunctionTracking1 WHERE " & Left(strWHEREFunctionID, Len(strWHEREFunctionID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREFunctionTrackingID = strWHEREFunctionTrackingID & "FunctionTrackingID = " & !FunctionTrackingID & " OR "
        intFunctionTrackingID = DLookup("FunctionTrackingID", "tblFunctionTracking", "FunctionTrackingID = " & !FunctionTrackingID)
        If Nz(intFunctionTrackingID, "") = "" Then
            MsgBox "Fix FunctionTrackings"
        End If
        .MoveNext
    Loop
    .Close
End With

With rsContractorFunctionList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT ContractorFunctionID FROM tblContractorFunction1 WHERE " & Left(strWHEREFunctionID, Len(strWHEREFunctionID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREContractorFunctionID = strWHEREContractorFunctionID & "ContractorFunctionID = " & !ContractorFunctionID & " OR "
        intContractorFunctionID = DLookup("ContractorFunctionID", "tblContractorFunction", "ContractorFunctionID = " & !ContractorFunctionID)
        If Nz(intContractorFunctionID, "") = "" Then
            MsgBox "Fix ContractorFunctions"
        End If
        .MoveNext
    Loop
    .Close
End With

With rsProductionTrackingList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT ProductionTrackingID FROM tblProductionTracking1 WHERE " & Left(strWHEREFunctionTrackingID, Len(strWHEREFunctionTrackingID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        strWHEREProductionTrackingID = strWHEREProductionTrackingID & "ProductionTrackingID = " & !ProductionTrackingID & " OR "
        intProductionTrackingID = DLookup("ProductionTrackingID", "tblProductionTracking", "ProductionTrackingID = " & !ProductionTrackingID)
        If Nz(intProductionTrackingID, "") = "" Then
            DoCmd.SetWarnings False
            DoCmd.RunSQL ("INSERT INTO tblProductionTracking (ProductionTrackingID, ProductionID, FunctionTrackingID, TrackingNumber, TrackingOnly, SourceData) " _
                        & "SELECT ProductionTrackingID, ProductionID, FunctionTrackingID, TrackingNumber, TrackingOnly, SourceData " _
                        & "FROM tblProductionTracking1 " _
                        & "WHERE ProductionTrackingID = " & !ProductionTrackingID)
            DoCmd.SetWarnings True
        End If
        .MoveNext
    Loop
    .Close
End With

With rsProductionInputDetailList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT * FROM tblProductionInputDetail1 WHERE " & Left(strWHEREContractorFunctionID, Len(strWHEREContractorFunctionID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        intProductionInputDetailID = DLookup("ProductionInputDetailID", "tblProductionInputDetail", "ProductionInputDetailID = " & !ProductionInputDetailID)
        If Nz(intProductionInputDetailID, "") = "" Then
            DoCmd.RunSQL ("INSERT INTO tblProductionInputDetail (ProductionInputDetailID, ContractorFunctionID, ProductionID, ProductionUnits) " _
                        & "SELECT ProductionInputDetailID, ContractorFunctionID, ProductionID, ProductionUnits " _
                        & "FROM tblProductionInputDetail1 " _
                        & "WHERE ProductionInputDetailID = " & !ProductionInputDetailID)
        End If
        .MoveNext
    Loop
End With

With rsProductionInvoiceDetailList
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT * FROM tblProductionInvoiceDetail1 WHERE " & Left(strWHEREInvoiceFunctionID, Len(strWHEREInvoiceFunctionID) - 4)
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open
    Do Until .EOF
        intProductionInvoiceDetailID = DLookup("ProductionInvoiceDetailID", "tblProductionInvoiceDetail", "ProductionInvoiceDetailID = " & !ProductionInvoiceDetailID)
        If Nz(intProductionInvoiceDetailID, "") = "" Then
            DoCmd.RunSQL ("INSERT INTO tblProductionInvoiceDetail (ProductionInvoiceDetailID, InvoiceFunctionID, ProductionInvoiceID, ProductionID, ProductionUnits, InvoiceDetailTotal, InvoiceNotes) " _
                        & "SELECT ProductionInvoiceDetailID, InvoiceFunctionID, ProductionInvoiceID, ProductionID, ProductionUnits, InvoiceDetailTotal, InvoiceNotes " _
                        & "FROM tblProductionInvoiceDetail1 " _
                        & "WHERE ProductionInvoiceDetailID = " & !ProductionInvoiceDetailID)
        End If
        .MoveNext
    Loop
End With
End Sub