Updating Access Database and Multiple Tables

Results 1 to 4 of 4

Thread: Updating Access Database and Multiple Tables

  1. #1
    Wes Hegge Guest

    Default Updating Access Database and Multiple Tables

    I have an Access database that has 3 tables in it. Table1 has a field called IDCode. Tables2 and Table3 have the same field setup as a relation to Table1. I want to update Table1&#039s IDCode field along with Table2 and Table3. I can not figure out the sql call to do this. Any help would be greatly appreciated!!<BR><BR>Thanks

  2. #2
    Chris Willey Guest

    Default RE: Updating Access Database and Multiple Tables

    Assuming that these tables have another relationship, I think you can do<BR><BR>UPDATE Table1 INNER JOIN(Table2 INNER JOIN Table3 ON Table3.commonfield = Table2.commonfield) ON Table1.commonfield = Table2.commonfield SET IDCode = &#039newcode&#039 WHERE conditions<BR><BR>If IDCode is your only common field (and especially if it&#039s a primary key), you should be careful before updating it, and the above probably won&#039t work. I&#039m not even sure it will work in the first case, it&#039s just a guess.

  3. #3
    Wes Hegge Guest

    Default RE: Updating Access Database and Multiple Tables

    Well lets see if I can explain this more for you. The Access database is call TimeTracker. The Tables in question are Publisher, Contacts, and Magazine. I am editing the table Publisher. It could have none or many relations to the Contacts and Magazine tables. The relationship defined in Access is by a field called PublisherCode. If I change PublisherCode when I edit the Publisher table I need the Contacts and Magazine tables to update also.<BR><BR>I tried your solution and so far it does not work but I am continuing to try variations of it.<BR><BR>Thanks

  4. #4
    Wes Hegge Guest

    Default RE: Updating Access Database and Multiple Tables

    Figured it out!!<BR><BR>THought you would like to know. The solution was actually an Access fix. When I created the relationships in Access I needed to select &#039enforce data integrity&#039 and the &#039cascade&#039 for update and delete.


Posting Permissions

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