Replace, or Append if NOT exist (1 Viewer)

Timoo

Registered User.
Local time
Today, 19:19
Joined
May 9, 2012
Messages
30
Hi,

I am building this database, importing several excel files from different customers. The excel files are standardized. Every month or so they give updates. Then it is up to the database to update the old tables with standard info coming from these excel files.

Now I am looking for a way to automatically delete old customer information and replace it with the new, if the customer already exists.

This is the code I use for import:
Code:
     'cycle through the list of files & import to Access
     'appending to tables called DealerLists and DealerContacts
    For intFile = 1 To UBound(strFileList)
        
        'Get the counter value
        Set objApp = CreateObject("Excel.Application")
        objApp.Visible = False
        Set wb = objApp.Workbooks.Open(strPath & strFileList(intFile), True, False)
        counter = wb.Sheets("counters").Cells(2, "A").Value
        dealer = wb.Sheets("counters").Cells(2, "B").Value
        wb.Close
        Set objApp = Nothing

        'Import the parts list
        DoCmd.TransferSpreadsheet acImport, , _
        "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & counter
      
[I]            'Import or update the dealer file
            DoCmd.TransferSpreadsheet acImport, , _
            "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
[/I]         
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
This way I append all my records to the database.
But I want to delete the old records first.
So, I came up with this basic code:
Code:
        Dim dbTraderJoe As DAO.Database
        Dim tblContacts As DAO.Recordset
        Set dbTraderJoe = CurrentDb
        Set rstContacts = dbTraderJoe.OpenRecordset("DealerContacts")
        
        If rstContacts!DealerNumber <> dealer Then

            'Import or update the dealer file
            DoCmd.TransferSpreadsheet acImport, , _
            "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
        End If
        
        rstContacts.Close
        dbTraderJoe.Close

That should do it for new dealers only.
But how to detect and delete existing ones?

Thanks for your help,
Timo
 

Timoo

Registered User.
Local time
Today, 19:19
Joined
May 9, 2012
Messages
30
If I would use this, would that come in close?
I mean, would this delete the customers from the contactlist and their records from the list with items (dealerlist)?
Then afterwards I would import the excel sheet containing the customer contact info and material info.

Code:
        'Get the counter & customer value
        Set objApp = CreateObject("Excel.Application")
        objApp.Visible = False
        Set wb = objApp.Workbooks.Open(strPath & strFileList(intFile), True, False)
            counter = wb.Sheets("counters").Cells(2, "A").Value
            customer = wb.Sheets("counters").Cells(2, "B").Value
        wb.Close
        Set objApp = Nothing

        Dim dbTraderJoe As DAO.Database
        Dim rstContacts As DAO.Recordset
        Dim rstDealers As DAO.Recordset

        Set db = CurrentDb
        Set rstContacts = db.OpenRecordset("DealerContacts")
        Set rstDealers = db.OpenRecordset("DealerLists")
        DoCmd.SetWarnings False
        
        Do While Not rstContacts.EOF
            If rstContacts!DealerNumber = customer Then
                rstContacts.Delete
            End If
            rstContacts.MoveNext
        Loop
        
        Do While Not rstDealers.EOF
            If rstDealers!DealerNumber = customer Then
                rstDealers.Delete
            End If
            rstDealers.MoveNext
        Loop
        
        rstContacts.Close
        rstDealers.Close
        dbTraderJoe.Close
 

Rx_

Nothing In Moderation
Local time
Today, 12:19
Joined
Oct 22, 2009
Messages
2,803
Just as a matter of DB process.
Are you assigning a Customer ID (or dealer ID) to the table rows?
My preference would be to check for a duplicate (as you are doing). If a duplicate is found, then make a copy before deleting to a table with the same name with archive (e.g. Dealer_Archive). Copy the dealer ID, the entire record, and add a new field "date archived".

Then use an Update query to update the data.
Because, if you delete the record, and the new information fails to replace it ... you have lost that dealer information. By copying, then updating the existing record in place, this lowers the chance of loosing data.

If no dealer currently exist, then yes, use the append.
 

Timoo

Registered User.
Local time
Today, 19:19
Joined
May 9, 2012
Messages
30
Hi Rx_,

Thanx for your reply.
Dealer ID is unique, indeed.
How would you build such an error-check routine?

Currently it is not allowed to append a dealer ID that is already there, so I first have to copy the old duplicate into a tmp-table, append the new information, check if this is a success, delete the old duplicate if so, or re-append the old duplicate if not.

How would you do that?

Thanx in advance,
Timo
 

Rx_

Nothing In Moderation
Local time
Today, 12:19
Joined
Oct 22, 2009
Messages
2,803
Let me suggest getting rid of the temp table.

Take the table you are using - copy it name it the same <table>_Archive - remove the unique ID, then in front add an autocounter primary key. At the end of this _Archive table - add a DateTransaction field.
By using the Make Table query - and adding all the fields of your orginal table - the result will be the same data types. That will save some time. Then in design mode 1. remove the old primary key, 2. Add a new autocounter in front, 3. add the DateTransaction field with a default of NOW

For a new customer (dealer whatever) check - do they currently exist.
Yes - copy all fields from your existing record to be updated to the <Table>_Archive
Now, the orginal record is still intact - you have just copied it to the archive.

Note a Function was used to do this - the function returns a Pass / Fail (true/false) that the archive was completed successfully.
Now - use an Update Query to modify the existing record in place. A function could provide a Pass/Fail

If it is not unique (i.e. a New customer) - make an Append query in a function - add the new dealer to the last row. The Append Query function might also return a Pass/Fail (true/false)

See attachment -
The following code is shortened to give you the concept of archiving a record before Updating any field.

Code:
Function XactSHLBHLToArchiveTable(ArchiveTableName As String, UserAction As String) As Boolean
      '   Copy Production table structure only rename TableName_Archive, Turn primary key off on first field add a last field with the default value of Now()
      Dim dbArchive As DAO.Database
      Dim rstArchiveTable As DAO.Recordset
      Dim SQLArchiveTable    As String     ' Archive table is an exact copy of the table - with an additional time stamp field at the end plus Action taken
10    On Error GoTo Error_XactSHLBHLToArchiveTable
20    SQLArchiveTable = "SELECT " & ArchiveTableName & ".* FROM Wells_SHLBHL_Archive;"
30    Set dbArchive = CurrentDb()
40    Set rstArchiveTable = dbArchive.OpenRecordset(SQLArchiveTable, dbOpenDynaset, dbSeeChanges)
50    rstArchiveTable.AddNew
      ' ------------   Wrote code to genereate the following code ------------ Public Function ShowFieldsRSModify(strTable)- writes this code
  ' for generated code the ID_shlbhl needs to have the _archive removed
60    rstArchiveTable!ID_SHLBHL = Me.Recordset!ID_SHLBHL
70    rstArchiveTable!ID_SHLBHL = Me.Recordset!ID_SHLBHL
80    rstArchiveTable!ID_Wells = Me.Recordset!ID_Wells
90    rstArchiveTable!Lot_SHLBHL = Me.Recordset!Lot_SHLBHL
' you get the idea  Keeping field names in the Archive make it simple
280   rstArchiveTable!Well_Name = [Forms]![Wells_2]![Well_Name]             ' Changed code to obtain from form since no field exist in recordset
300   rstArchiveTable!UserName = Environ("username")                        ' OPTIOINAL get user name from function and substitute since no field exist in recordset
310   rstArchiveTable.Update
      '     Close the archive database
320   rstArchiveTable.Close
330   Set rstArchiveTable = Nothing
340   Set dbArchive = Nothing
350   Call LogUsage("Wells_SHLBHL Transact Log Current Record", "XactSHLBHLToArchiveTable function", "Success - Logged current record")
360   XactSHLBHLToArchiveTable = True
370   Exit Function
Error_XactSHLBHLToArchiveTable:
380   Err.Clear
390   XactSHLBHLToArchiveTable = False ' ERROR function returns False
400       Call LogUsage("Wells_SHLBHL Transact Log Current Record", "XactSHLBHLToArchiveTable function", "Failure - Logged record")
'400 is a transaction log that records all error information
End Function

In review: You basically clone the table to table_Archive - adding fields autocount in front - and date created in back
If the Table Record needs Updated - first copy all to the Table_archive, recive a Pass/Fail - then run Update on the Table record receiving a Pass/fail
If this is a new record - use Append to add it.

My systems transact all records and record all users actions as there is plenty of Quality Assurance and auditing task.
These kind of Archive tables can make a huge difference in troubleshooting.
 

Attachments

  • SHLBHL Archive.gif
    SHLBHL Archive.gif
    19 KB · Views: 440

Users who are viewing this thread

Top Bottom