Making/Updating Sub-selections within RecordSets t

Results 1 to 3 of 3

Thread: Making/Updating Sub-selections within RecordSets t

  1. #1
    Andrew Sinning Guest

    Default Making/Updating Sub-selections within RecordSets t

    Is it possible to make a sub-selection from a RecordSet?<BR><BR>Here&#039;s what I want to do: I have some n-number of records to either append to or update in a table. Some of the records will be new records, some of them will updated records. Therefore, for each record, I will need to check to see if there is an existing record to update, and then either update it or append a new record.<BR><BR>But, rather than create a new selection from the entire table for each record to be updated or appended, I would like to do the following:<BR> First, SELECT an RS which meets certain criteria which all of the new and updated records share: "SELECT * from table WHERE field1=x AND field2=y"<BR> Now, I would like to look in this RS for existing records, make changes to existing records within this RS, and append new records to this RS.<BR> Only then, after the entire RS has been modified, do I want to perform an Update on the RS.<BR><BR>Does this make sense? Is this possible?

  2. #2
    Join Date
    Dec 1969

    Default RE: Making/Updating Sub-selections within RecordSe

    You can&#039;t do an UPDATE on the whole RS. You *must* do an update after *each* changed record.<BR><BR>I think you are approaching this wrong.<BR><BR>You say you have a number of records to insert or update. Where do these records come from??? Do you mean a set of info from a form? Or from some other recordset?<BR><BR>Anyway, whichever...<BR><BR>Why not collect whatever is the key information from each of the candidate records and form that into a query:<BR><BR>SELECT recordID FROM table<BR>WHERE table.KeyField IN ( ...list of key info values ... )<BR><BR>That gives you a recordset that has the IDs of all the *existing* records in the table (that is, ones that match one of the candidates).<BR><BR>Convert *that* to an array, for efficiency and so it stays static:<BR><BR>alreadyIn = RS.GetRows<BR><BR>and then, as you process each candidate, you look into the alreadyIn array to see if you should do INSERT or UPDATE for that particular candidate.<BR><BR>

  3. #3
    Andrew Sinning Guest

    Default RE: Making/Updating Sub-selections within RecordSe

    Somehow I have it in my mind that it is most efficient to SELECT a singe RS from a table, add all of the NEW records to the RS, and then update the RS. I&#039;ve done this before. I (think I) know that for NEW records, you only have to update the RS once.<BR><BR>Mind you, I&#039;m a total newbie.<BR><BR>I also think that I can loop through the records in the RS and change the entries that I need changed.<BR><BR>Am I just being obtuse? Isn&#039;t it better to only have to execute a singe querry on the connection, make all the changes and addenda to the RS, and then update the database?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts