Query Switch

[racer25]

Hi

I am not rightly sure where to start with the logic with this and not exactly an expert either

The outline is I need to update client information in main table with information provided by my colleagues. This information may either exist already or be an additional client.

I have a Primary Table "ClientData" and another table called "TempUpdate" - my goal is to run some code that checks to see if the client code exists in ClientData and if it does run an update query with that record else run an append query for it to add the record. The source of the new data is TempUpdate which would contain many records for updating/adding.

Has anyone any suggestions please on how I would go about such a task, reviewing each row in my temp table to decide whether it should be added or updated?

Thanks in advance for reading this and for any suggestions you can offer,

Regards

Rob

 

[jal]

I don't know what client code is, but you'll need two queries.
INSERT INTO ClientData
SELECT T.* FROM TempUpdate as T
LEFT JOIN ClientData as C
ON C.ClientCode = T.ClientCode
WHERE C.ClientCode IS NULL
(To test this, run the SELECT part to find out which records will be inserted).

The other query will be this:

UPDATE ClientData as C
INNER JOIN TempUpdate as T
ON T.ClientCode = C.ClientCode
SET C.Column1 = T.Column1, C.column2 = T.Column2 (and so on).

 

[rapsr59]

Hi,

If you want to do a batch edit/add record I would use the following.

    Dim db As DAO.Database
    Dim rstTempUpdate As DAO.Recordset
    Dim rstClientData As DAO.Recordset
    Dim strSQL As String
    Dim x As Long
 
    Set db = CurrentDb
    Set rstTempUpdate = db.OpenRecordset("TempUpdate", dbOpenSnapshot)
 
 
    While Not rstTempUpdate.EOF 'Loop All Records
        'Use the ClientID to look for an existing record in ClientData
        strSQL = "SELECT * FROM [ClientData] " & _
        "WHERE [ClientID] = " & rstTempUpdate![ClientID]
        'Open ClientData and check if a record exists
        Set rstClientData = db.OpenRecordset(strSQL)
        If rstClientData.EOF Then                           'Check If Record Exists
            rstClientData.AddNew                            'No - Add A New Record
        Else
            rstClientData.Edit                              'Yes - Edit The Record
        End If
 
        'Add/Edit each field in ClientData using the Fields in TempUpdate
        For x = 0 To rstTempUpdate.Fields.Count - 1
            rstClientData.Fields(x) = rstTempUpdate.Fields(x)
        Next x
 
        rstClientData.Update                                'Update the record
 
        'Move to the next record in TempUpdate
        rstTempUpdate.MoveNext                         
    Wend
 
    Set rstTempUpdate = Nothing
    Set rstClientData = Nothing
    Set db = Nothing

Hope this helps,


Richard

 

[racer25]

Hi Richard,

You certainly seem to have hit the nail on the head with what I am trying to do. Time to set up a Test DB and ensure it works.

Thanks for your time,

Rob

Hi,

If you want to do a batch edit/add record I would use the following.

    Dim db As DAO.Database
    Dim rstTempUpdate As DAO.Recordset
    Dim rstClientData As DAO.Recordset
    Dim strSQL As String
    Dim x As Long
 
    Set db = CurrentDb
    Set rstTempUpdate = db.OpenRecordset("TempUpdate", dbOpenSnapshot)
 
 
    While Not rstTempUpdate.EOF 'Loop All Records
        'Use the ClientID to look for an existing record in ClientData
        strSQL = "SELECT * FROM [ClientData] " & _
        "WHERE [ClientID] = " & rstTempUpdate![ClientID]
        'Open ClientData and check if a record exists
        Set rstClientData = db.OpenRecordset(strSQL)
        If rstClientData.EOF Then                           'Check If Record Exists
            rstClientData.AddNew                            'No - Add A New Record
        Else
            rstClientData.Edit                              'Yes - Edit The Record
        End If
 
        'Add/Edit each field in ClientData using the Fields in TempUpdate
        For x = 0 To rstTempUpdate.Fields.Count - 1
            rstClientData.Fields(x) = rstTempUpdate.Fields(x)
        Next x
 
        rstClientData.Update                                'Update the record
 
        'Move to the next record in TempUpdate
        rstTempUpdate.MoveNext                         
    Wend
 
    Set rstTempUpdate = Nothing
    Set rstClientData = Nothing
    Set db = Nothing

Hope this helps,


Richard

 

[jal]

Hi Richard,

You certainly seem to have hit the nail on the head with what I am trying to do. Time to set up a Test DB and ensure it works.

Thanks for your time,

Rob

As you wish. Richard's approach should work just fine for small amounts of data.

With a large amount of data it would probably run into performance issues. Whereas my code is likely to execute much faster.

 

[rapsr59]

Jal was absolutly correct when he said...

Richard's approach should work just fine for small amounts of data.

With a large amount of data it would probably run into performance issues. Whereas my code is likely to execute much faster.

I have a table at work that I tested today. The table has 1,458,892 records with 10 fields per record.

I ran my "Stepping though each record" code and the transfer of records to another table took 21 minutes 26 seconds to complete. I then ran an Append query and it took 1 minute 40 seconds to transfer all the records. "What a time saver."

I did my programming of queries a little different than JAL.

In this particular case, since all the records were going to be affected, either by Appending (Adding) the new records and Updating (Editing) the existing records. I chose to run a Delete query on the existing records in ClientData and then an Append query to add all the records in TempUpdate to ClientData.

This decision was made after I ran the Append and Update queries as recommended by JAL. In all about 13 seconds were saved by using the Delete and Append queries instead of the Append and Update queries.

I used the following code to accomplish this.

I Built two queries. One query to delete the existing records in ClientData and the second query to append the records in TempUpdate to ClientDate.

The Delete query sql ...

DELETE ClientData.*
FROM ClientData;

The Append query sql...

INSERT INTO ClientData
SELECT TempUpdate.*
FROM TempUpdate;

After creating these two queries, I wrote the following code to run them...

Private Sub cmdUpdateRecords_Click()
 
    Dim strDeleteQry As String
    Dim strAppendQry As String
 
    'Delete the records in ClientDate
    strDeleteQry = "qryDeleteClientData"
    DoCmd.OpenQuery strDeleteQry
 
    'Append the records in TempUpdate to ClientData
    strAppendQry = "qryAppendToClientData"
    DoCmd.OpenQuery strAppendQry
 
End Sub

I stand corrected. JAL is Spot On!

Richard

 

[jal]

Very kind of you Richard to share that feedback.

My code is no more useful than yours. Your code exposes how to leverage DAO in ways that can open up new opportunities for the original poster, thereby expanding his toolset. So I don't feel myself to have "corrected" you. Instead, I see it is as a good thing that we both had something to contribute. And I'm still a beginner anyway, especially at DAO, and therefore I'm always eager to see code samples such as yours.

 

[rapsr59]

Thank you JAL! You are very kind. And I too feel that the forum is an excellent place to learn new and better things. I know that I have learned a lot from fellow programmers such as your self.

I love access and I see that you share the excitement when we can help others who are trying to better themselves in Access programming.

I do have second thoughts about the last Post I made.

What if there are records in the ClientData table that do not reside in the TempUpDate table? When the Delete query is executed those records will also be deleted. So again “I Stand Corrected” but this time I will correct myself.

Instead of using the sql…

[COLOR=black][FONT=Verdana]DELETE ClientData.*[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM ClientData;[/FONT][/COLOR]

… in the Delete query I would use the following sql to preserve those records in ClientData that do not exist in the TempUpDate table and delete the duplicate records only.

[COLOR=black][FONT=Verdana]DELETE ClientData.*[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]FROM ClientData INNER JOIN TempUpdate ON ClientData.ClientID = TempUpdate.ClientID;[/FONT][/COLOR]

Then the Append query, as described in my previous post, can Append all the records in TempUpdate to ClientData.

I do hope this helps racer25 in getting the bests results in Query Switch

Thanks again JAL for your very kind words.


Richard

PS: Again for speed I recommend JAL’s suggestion of using queries.