Database cleaning through excess

[ItIsHardToProgram]

Hey every one, this is a followup of my conversation in microsoft access JET SQL forum: [red]Converting text to number in access[/red]

What I am trying to do is to have a macro that modifies or removes the data accordingly to the excel sheet through DAO in a access database.

I am trying to find out wich of the two solutions is best, and if the 2nd solution is best, how to do it.

[blue]1: Should I remove all data, and rewrite everytime ? Any problem with this??


2: Should I modify data that needs modifying and remove data that shouldnt be there? (what are the bonuses of this method?)[/blue]

This is what I have so far:

Public Function CleanDatabaseTrash()
Dim iMaxLine            As Integer
Dim i                   As Integer
Dim iTemp               As Integer
Dim RstTimeSheet        As DAO.Recordset
Dim bDBwritten          As Boolean

Worksheets(1).Activate
Worksheets(1).Range("A1").Activate

iMaxLine = ActiveCell.End(xlToRight).Column
'dVal = Worksheets("Tableau").Range("F" & i).Value
'iTemp = Worksheets(1).Range("C16").Value
i = 1
    
    'Ouverture de la BD et du Rec.Set
    Call sGetRS(RstTimeSheet, "tblTimeSheet")
    
    'Loop sur les 16 ligne, 2 part 2 car la 2iem c pour le R&D du meme projet
    Do While j <> ""
      
      j = Worksheets(1).Cells(1, 1).Value
      
      'valid si il y a des hers dans la col K, si non skip
          bDBwritten = True
          'iTemp = Worksheets(1).Range("C" & i).Value
            With RstTimeSheet
              .AddNew
                !IdProjet = Worksheets(1).Range("A" & i).Value
                '!nom = Worksheets(1).Range("F9").Value
                !idemployer = Worksheets(1).Range("D" & i).Value
                !semaine = Worksheets(1).Range("E" & i).Value
                !Description = Worksheets(1).Range("B" & i).Value
                !DescriptionRD = Worksheets(1).Range("C" & i).Value
                !Lundi = IIf((Worksheets(1).Range("D" & i).Value) > 0, Worksheets(1).Range("F" & i).Value, 0)
                !mardi = IIf((Worksheets(1).Range("E" & i).Value) > 0, Worksheets(1).Range("G" & i).Value, 0)
                !mercredi = IIf((Worksheets(1).Range("F" & i).Value) > 0, Worksheets(1).Range("H" & i).Value, 0)
                !jeudi = IIf((Worksheets(1).Range("G" & i).Value) > 0, Worksheets(1).Range("I" & i).Value, 0)
                !vendredi = IIf((Worksheets(1).Range("H" & i).Value) > 0, Worksheets(1).Range("J" & i).Value, 0)
                !samedi = IIf((Worksheets(1).Range("I" & i).Value) > 0, Worksheets(1).Range("K" & i).Value, 0)
                !dimanche = IIf((Worksheets(1).Range("J" & i).Value) > 0, Worksheets(1).Range("L" & i).Value, 0)
                !LundiRD = IIf((Worksheets(1).Range("D" & i + 1).Value) > 0, Worksheets(1).Range("M" & i).Value, 0)
                !mardiRD = IIf((Worksheets(1).Range("E" & i + 1).Value) > 0, Worksheets(1).Range("N" & i).Value, 0)
                !mercrediRD = IIf((Worksheets(1).Range("F" & i + 1).Value) > 0, Worksheets(1).Range("O" & i).Value, 0)
                !jeudiRD = IIf((Worksheets(1).Range("G" & i + 1).Value) > 0, Worksheets(1).Range("P" & i).Value, 0)
                !vendrediRD = IIf((Worksheets(1).Range("H" & i + 1).Value) > 0, Worksheets(1).Range("Q" & i).Value, 0)
                !samediRD = IIf((Worksheets(1).Range("I" & i + 1).Value) > 0, Worksheets(1).Range("R" & i).Value, 0)
                !dimancheRD = IIf((Worksheets(1).Range("J" & i + 1).Value) > 0, Worksheets(1).Range("S" & i).Value, 0)
               .Update
            End With
       End If
    
    i = i + 1
    Loop
If bDBwritten = True Then
RstTimeSheet.Close
Set RstTimeSheet = Nothing
bDBwritten = False
Else
MsgBox ("Votre feuille de temps ne contient aucune donnée numérique")
End If

'BackupTimeSheet (Worksheets(1).Range("F11").Value)
 
End Function

Lets say I chose solution 2, for any reason, how would I go into comparing Worksheets(1).RAnge("A" & i).Value with what is written in the access table, and wouldnt that process be way to cpu costy because of the back and forth?

I would need at least to read three fields to validate that it is indeed the same entry, and then loop it like I currently am doing.

If the Worksheets(1) entry is not found, I would need to create it, and if the entry in the database dosnt match any entry in the worksheets(1) I would need to delete it.

If possible, I would like it to be doable through a specific range, lets say from one date to another, wich corresponds to an entry in the db.


If you want to broad an example, these three fields are what I would use: ProjectID, EmployeeID, Date. IF you use something else than DAO, or specific functions, please, tell me.

Cordialy,

Julien

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.