Database Update With Relations Issue (1 Viewer)

[SorenIX]

[SOLVED] Database Update With Relations Issue

Hello hello!

I'm making an Access DB and records are often updated through Excel importations.

My problem here is what my code does :

Before importing, it deletes every rows in the table. Hence, records with relations won't be deleted.

I was wondering if there was a certain way of importing the data and simply overwrite the information depending on the ID number. (The ID would never change and be the key used in other tables)

Thanks.

 

[plog]

Before importing, it deletes every rows in the table. Hence, records with relations won't be deleted.

That doesn't make sense. To paraphrase the above quote: "it deletes all rows, therefore some rows aren't deleted." You can't have it both ways.

However, to answer you question--the best way to update some data from a source, you should import the source data into a temporary table, determine what needs to be updated/added and then update and add the appropriate records from that temporary table.

 

[SorenIX]

How would you determine what needs to be updated and how would you update it?

 

[plog]

Specifically, only someone who knows your data can determine that (i.e. you). In general, you would bump the temporary table (tblTemp) up against the existing table (tblMain) to find out what data doesn't match. This would take 3 steps,

#1 A LEFT JOIN from tblTemp into tblMain to find out which records are in tblTemp that aren't in tblMain. These records will be added to tblMain.

#2 A LEFT JOIN from tblMain into tblTemp to find out which records are in tblMain that aren't in tblTemp. These, you will probably ignore. This tells you that you have more data than the new source file, it might trigger a call to whomever is providing you data to ask what about these records.

#3 An INNER JOIN between tblMain and tblTemp to find records that match (probably on ID or any other field that will remain the same) but have different data in certain fields. For this, if the tblTemp has the 'good' data, I would delete the matching records and just append them from tblTemp.

 

[SorenIX]

I would delete the matching records and just append them from tblTemp.

That's the point, I can't delete records that are in relations with other tables. I'd have to do an UPDATE query?

 

[plog]

The new data doesn't have a unique identifier? How do you match it up to the existing data. Can you provide samples of your data?

 

[SorenIX]

App_Tab:
 
AppID (PK)
AppName
ImpactScore
 
Impacts_Tab:
 
AutoNb (PK)
AppID (FK)
Impact

If I try to delete an ID that is refered in the Impacts_Tab, it asks to remove relations with this record before proceeding.

 

[plog]

It seems AutoNb is unnecessary if AppID is a primary key. You should use that instead of AutoNb for your relationships. That will make this process easier.

For #3 of my steps above, you could just run an update query--link the fields by AppID and then update all the records in Impacts_Tab with those in App_Tab

 

[SorenIX]

AutoNb is just a way to give the records a number as there could be several rows with the same AppID

Impacts_Tab
 
1 / 10 / AAA
2 / 10 / BBB
3 / 10 / CCC
4 / 15 / AAA
5 / 15 / CCC
. . .

 

[SorenIX]

For that update, I'm having trouble seeing it. I' have to make another temp table with the INNER JOIN results, and then do the UPDATE from this table?

 

[SorenIX]

Thanks plog, everything seems to be working.

Here's the code for the interested ones in the future.

Sub Import_Applications()
 
    Dim SQL_Update, SQL_LEFTJOIN As String
    Dim totalApps As Integer
 
    SQL_Delete_Rows_From_Tab = "DELETE * FROM Temp_Import_Tab;"
 
    DoCmd.SetWarnings False
 
    DoCmd.RunSQL ("DROP TABLE Temp_Import_Tab")
 
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "Temp_Import_Tab", CurrentProject.Path & "\TempResults_Applications.xlsx", True, "Applications$"
    SQL_LEFTJOIN = "INSERT INTO Applications_Tab (AppID, AppNom, Famille, Transit, Description, " & _
                                "NiveauCriticité, NiveauObsolescence) " & _
                   "SELECT Temp_Import_Tab.AppID, Temp_Import_Tab.AppNom, " & _
                           "Temp_Import_Tab.Famille, Temp_Import_Tab.Transit, Temp_Import_Tab.Description, " & _
                           "Temp_Import_Tab.NiveauCriticité, Temp_Import_Tab.NiveauObsolescence " & _
                   "FROM Temp_Import_Tab " & _
                   "LEFT JOIN Applications_Tab " & _
                   "ON Temp_Import_Tab.AppID = Applications_Tab.AppID;"
 
    DoCmd.RunSQL SQL_LEFTJOIN
 
    Me.Geter_Seter.RowSource = "SELECT MAX([AppID]) FROM Applications_Tab;"
    totalApps = [Geter_Seter].[ItemData](0)
    AppID = 1
 
    Dim AppIDFromTable, AppNom, Famille, Transit, Description, NiveauCriticité, NiveauObsolescence As String
 
    Do While AppID <> totalApps + 1
 
        Me.Geter_Seter.RowSource = "SELECT [AppID] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
        AppIDFromTable = [Geter_Seter].[ItemData](0)
 
 
        If IsNull(AppIDFromTable) = False Then
 
            Me.Geter_Seter.RowSource = "SELECT [AppNom] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            AppNom = [Geter_Seter].[ItemData](0)
 
            Me.Geter_Seter.RowSource = "SELECT [Famille] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            Famille = [Geter_Seter].[ItemData](0)
 
            Me.Geter_Seter.RowSource = "SELECT [Transit] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            Transit = [Geter_Seter].[ItemData](0)
 
            Me.Geter_Seter.RowSource = "SELECT [Description] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            Description = [Geter_Seter].[ItemData](0)
 
            Me.Geter_Seter.RowSource = "SELECT [NiveauCriticité] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            NiveauCriticité = [Geter_Seter].[ItemData](0)
 
            Me.Geter_Seter.RowSource = "SELECT [NiveauObsolescence] FROM Temp_Import_Tab WHERE [AppID] = " & AppID & ";"
            NiveauObsolescence = [Geter_Seter].[ItemData](0)
 
            SQL_Update = "UPDATE Applications_Tab " & _
                         "SET [AppNom] = '" & AppNom & "', " & _
                         "[Famille] = '" & Famille & "', " & _
                         "[Transit] = '" & Transit & "', " & _
                         "[Description] = '" & Description & "', " & _
                         "[NiveauCriticité] = '" & NiveauCriticité & "', " & _
                         "[NiveauObsolescence] = '" & NiveauObsolescence & "' " & _
                         "WHERE [AppID] = " & AppID & ";"
 
            DoCmd.RunSQL SQL_Update
 
        End If
 
        AppID = AppID + 1
 
    Loop
    DoCmd.SetWarnings True
End Sub

The [Geter_Seter] is an unbound combo box I use to fetch a SQL result value and use it afterward.