Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-17-2012, 02:00 AM   #1
Timoo
Newly Registered User
 
Join Date: May 2012
Posts: 31
Thanks: 9
Thanked 1 Time in 1 Post
Timoo is on a distinguished road
Replace, or Append if NOT exist

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
      
            'Import or update the dealer file
            DoCmd.TransferSpreadsheet acImport, , _
            "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
         
    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 is offline   Reply With Quote
Old 12-17-2012, 03:17 AM   #2
Timoo
Newly Registered User
 
Join Date: May 2012
Posts: 31
Thanks: 9
Thanked 1 Time in 1 Post
Timoo is on a distinguished road
Re: Replace, or Append if NOT exist

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
Timoo is offline   Reply With Quote
Old 12-17-2012, 12:09 PM   #3
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,798
Thanks: 635
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Lightbulb Re: Replace, or Append if NOT exist

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.

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Old 12-18-2012, 12:56 AM   #4
Timoo
Newly Registered User
 
Join Date: May 2012
Posts: 31
Thanks: 9
Thanked 1 Time in 1 Post
Timoo is on a distinguished road
Re: Replace, or Append if NOT exist

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
Timoo is offline   Reply With Quote
Old 12-18-2012, 07:18 AM   #5
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,798
Thanks: 635
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Re: Replace, or Append if NOT exist

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.
Attached Images
File Type: gif SHLBHL Archive.gif (19.0 KB, 100 views)

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
Reply

Tags
append , import , replace

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Append record if exist or Update if not exist Mist Modules & VBA 9 01-18-2016 06:07 AM
Append Only Where The Record does not Exist belly0fdesire Modules & VBA 1 05-30-2006 11:28 AM
Append records where non exist CraigBFG Queries 0 08-18-2005 07:34 AM
Save As..but Filename already exist..howto Replace with YES? alienscript Macros 1 10-05-2004 04:11 AM




All times are GMT -8. The time now is 01:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World