Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: Replicating a subset of rows in table

    Hi all:

    I need to create a copy of a subset of rows from a table while changing the value in one of its columns. I apologize for the egregious use of a database to do this, but it ain't my setup, and is the lesser of all other evils. I need to be able to do this for a list of tables. The table names are found in a table called ListOfTables. The problem is that except for the column I need to update, all the tables have unique column names. All the tables have an ID column (indexed, autonumber no duplicates) that I don't care about or need to change, and an AccountID column (non indexed). The value I need to assign the AccountID column is guaranteed to be acceptable to the database (ie. no contraint violations etc.)

    For each table I need to update I create a temporary table to hold the rows whose AccountID values I need to copy. So I:
    INSERT INTO TempTarget
    SELECT * FROM Source
    WHERE AccountID = 1

    UPDATE TempTarget
    SET AccountID = 2

    And this is where I get stuck. I can't stuff all the columns from TempTarget back into Source since the ID column gets in the way.

    I can't use a VB approach, and must use SQL statements that would be appropriate in the SQL view of a query, although that is not where my solution would be run from.

    AdThanksVance,

    LostInDatabase

  2. #2
    Join Date
    Dec 2003
    Posts
    268

    Store in a collection

    Have you tried storing the data you are pulling out into a multideminsional collection, then making the changes to the collection then passing it back?

  3. #3
    Join Date
    Jun 2004
    Posts
    5

    Are multidimensional collections an Access feature?

    I guess I'll have to look into what Multidimensional collections are? If I can create and manipulate them via SQL queries, then I should be able to do it. If not the suggestion won't work for me since the only interface I am able to work with is what can be run in the SQL view of a query. It's a long story, but I'm using a tool that requires me to create only SELECT, UPDATE, INSERT, DELETE queries that it submits to Access via ODBC.

  4. #4
    Join Date
    Dec 2003
    Posts
    268

    Collections

    A collection can be treated like a table of data

    Multiple rows, mulitple columns

    Lets say this is the data you have pulled out from your recordset

    1 joe blow finance
    2 nancy jones HR
    3 fred gwynn IT

    you can create each row as a collection
    dim col as new collection
    dim col2 as collection 'used to store the previous collections
    dim i as integer

    for x = 0 to rs.recordcount
    for i = 0 to rs.columncount (not sure if this is correct, but you get my drift)
    col.add rs(i)
    next i
    col2.add (col)
    set col= nothing
    rs.movenext
    next x

    now we have col2 full of collection col for each row

    So col2.item(0) will return a collection we'll call it x
    So x(0) will equal '1'
    x(1) will equal 'joe'
    x(2) will equal 'blow'
    x(3) will equal 'finance'

    Now you can delete the information from your table or do what ever you need to do to the data.

    then you can just go through the reverse process from earlier to put the data back in.

    Does this make sense?
    Last edited by mjweyland; 06-22-04 at 17:49.

  5. #5
    Join Date
    Jun 2004
    Posts
    5
    Thanks for the information. It was very helpful. Can the code fragment you propose be run in the design view of an Access query? I guess that is why I'm confused.

  6. #6
    Join Date
    Jun 2004
    Posts
    5
    I wonder if there is SQL query that returns a list of column names for a given table. I would then be able to construct my insert back into the source table of only those columns I wish to insert. Essentially leaving out the autonumber 'ID' column which is the column thats getting in the way.

  7. #7
    Join Date
    Dec 2003
    Posts
    268

    Column Names

    When you do a select statement and store it as a Recordset there is some metadata that is transfered along with the information. You can get the column name, the data type etc. For instance

    tblEmployee has four columns
    empID, integer
    fname, string
    lname, string
    department, string

    when you pull records from the table and store them as a recordset:
    set rs = db.openrecordset("SELECT * FROM tblEmployee")
    'now for each column in the recorset you can get the information.
    rs.movefirst
    while not(rs.eof)
    for i = 0 to columncount
    msgbox "Field Name:" rs(i).name & "Field Value:" & rs(i).value
    next i
    rs.movenext
    wend

    As with other columns, you can store these column names in collection then dynamically create the SQL statement as you cycle through each item in the collection, then pass this back as an insert statement.

    Hopefull this makes sense.

    It should be enough to get you started.

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    update tableA
    set accountID = 2
    where accountID = 1

    If you need to do this for multiple tables then,

    Select table_name from tables;

    For each row in resultSet
    DoCmd.RunSQL(UPDATE_SQL)
    Next
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Jun 2004
    Posts
    5
    Good morning MJWeyland, and thanks once again for your help.

    Quote Originally Posted by mjweyland
    When you do a select statement and store it as a Recordset there is some metadata that is transfered along with the information. You can get the column name, the data type etc. For instance

    tblEmployee has four columns
    empID, integer
    fname, string
    lname, string
    department, string

    when you pull records from the table and store them as a recordset:
    set rs = db.openrecordset("SELECT * FROM tblEmployee")
    'now for each column in the recorset you can get the information.
    rs.movefirst
    while not(rs.eof)
    for i = 0 to columncount
    msgbox "Field Name:" rs(i).name & "Field Value:" & rs(i).value
    next i
    rs.movenext
    wend

    As with other columns, you can store these column names in collection then dynamically create the SQL statement as you cycle through each item in the collection, then pass this back as an insert statement.

    Hopefull this makes sense.

    It should be enough to get you started.
    I can't run VB code to do what I need to do. I understand what you are suggesting, but like I said earlier, my query must run in the design view of an SQL query in Access (it does not have to be a single query, it can be a series of SQL statements). Once I am able to replicate the rows in a single table, I can generalize the process to run the same sequence of queries and iterate over the remaining tables in the database. Remember I'm using a tool with an ODBC connection to the database in which I need to replicate rows in all tables copying all rows with AccountID = 1 and setting AccountID = 2. The tool does not understand VB either.

    Cheers.

Posting Permissions

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