F**king Microsoft Access.

Discussion in 'OT Technology' started by deusexaethera, Dec 6, 2005.

  1. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    I'm developing a database for a client, and it has to be done in MS Access 2000. NOT Access xp, NOT Access 2003, it HAS to be Access 2000. I don't get to take advantage of the past five years of refinement and usability.

    Did you turn and walk away in disgust? No? Good. Can you help me? Here's what I need to do. There are two scenarios:

    1. I need to create a new record in Table B using the data from the currently-active record in Table A. Table B has more fields than Table A, and the non-matching fields need to remain blank while the matching fields recieve exact copies of the data from Table A.

    2. I need to detect when a field value in Table B has been changed, and then I need to find the only record in Table A which has the same primary key value as the newly-modified field in Table B. Then I need to re-copy the data from the new matching record in Table A into the newly-modified record in Table B, overwriting all previous data in the newly-modified record in the process.

    The more basic version of this (scenario 1) seems like it should be a common enough thing that Access would have a premade function to make it happen, but damned if I can find it. And the complicated version (scenario 2) is completely beyond my grasp of Access programming at this point. Please help me.
     
  2. Yep

    Yep Knick knack paddy whack, give the old dog a bone

    Joined:
    Jan 22, 2001
    Messages:
    4,603
    Likes Received:
    0
    Location:
    South Jersey
    No MySQL? It's free.
     
  3. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    I believe I already addressed that. It. HAS. to be written in Access 2000. The client is the government, arguing with a brick wall is more productive.
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    You need to back up a bit for your problem - you seem to be looking at a record by record approach and that isn't usually how you would do things. For example, why only the currently active record? Wouldn't it be easier to make a copy all at once. And if not, then why not use a form to display the data and then copy that data to the new table.

    The solution will be depenant on what you're really trying to achieve.

    BTW, there's little difference for what you are trying to do between Access 2.0 and Access 2003.
     
  5. mace

    mace i don't read

    Joined:
    Jun 6, 2004
    Messages:
    12,567
    Likes Received:
    0
    Location:
    Texas
    relationships and lookup fields?
     
  6. CyberBullets

    CyberBullets I reach to the sky, and call out your name. If I c

    Joined:
    Nov 13, 2001
    Messages:
    11,865
    Likes Received:
    0
    Location:
    BC, Canada/Stockholm, Sweden
    could be done fairly easy using VBScript within Access.
     
  7. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Can you please be more specific, or are you just telling me that you are capable of making it work? I, for one, am stumped; I've been trying SQL expressions, VBS, query and macro combinations, and I swear Access was specifically designed to discriminate against people who think like I do. Hence the help request.

    - - -

    I can't say what specifically the database pertains to, but it is a requirement that only the currently-selected record in Table A be copied into the more-expansive Table B. Not all of the records in Table A will be unique in Table B; some will be copied more than once, others not at all, and there is no way to predict which will be copied. It has to be something that happens right at the moment that the user decides to copy the record they are viewing.
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Then make a form based on Table A, add a command button at the bottom that runs an SQL command:

    docmd.runsql "INSERT INTO Table2 ( field1, field3, field5, field6 ) SELECT " & me!field1 & " AS Expr1, " & me!field2 & " AS Expr2, " & me!field3 & " AS Expr3, " & me!field4 & " AS Expr4"

    Similar thing for the second part of your question - use an update sql query instead.
     
  9. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Without having tested it yet, you are my hero.

    That solves one of my two problems. I also need to be able to tell when a key field in Table B has been changed, so I can load the "new" appropriate data from Table A.

    For example: let's say I have a field in Table B called "Table_A_Primary_Key". This data was originally copied from Table A. If this field changes in Table B, I need to go find the appropriate record in Table A (all possible values WILL be unique in Table A), and overwrite the existing record in Table B. This is the one I've really been stewing over.

    It may not be necessary to do this, but I would rather provide the functionality first and remove it later, than leave it out and have to figure it out later.
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    When would the record change?
     
  11. Zak8022

    Zak8022 New Member

    Joined:
    Apr 16, 2004
    Messages:
    4,012
    Likes Received:
    0
    Location:
    Maryland
    wait... so if a record in TableB changes, you copy over the values from TableA... thus undoing/overwriting any changes just made to TableB?

    thats what i understand of the second part of your problem... but i may be wrong.
     
  12. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Going back to my example:

    Let's say the active record in Table B is a copy of record 001 from Table A. If the field "Table_A_Primary_Key" in Table B changes from 001 to 005, I want to go find record 005 in Table A and overwrite the active record in Table B with it.

    Edit: Hmm. Never mind, I convinced them this was a very bad idea, considering that it would mix lingering information with new information in ways that were never intended and not safe at all.
     
  13. mace

    mace i don't read

    Joined:
    Jun 6, 2004
    Messages:
    12,567
    Likes Received:
    0
    Location:
    Texas
    use lookup fields?
    Table A will have the real record, while Table B will have an index of that record and display what record A is.
    If Table A is changed then Table B will reflect those changes. The index won't change, just what displays.

    you could also do this
     
  14. deusexaethera

    deusexaethera OT Supporter

    Joined:
    Jan 27, 2005
    Messages:
    19,712
    Likes Received:
    0
    Heh. Yeah, I'd love to be able to do this, but Table B has to actually store a separate copy of the record so it can be manually edited once copied without changing the associated record in Table A.
     

Share This Page