Results 1 to 7 of 7
  1. #1
    lupis is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    23

    INSERT INTO and UPDATE to multiple tables

    I have a local access db that I use for exporting/importing and updating to a online sql server db.
    I have used INSERT INTO and UPDATE to accomplish this in a single table. But what if I have two tables with a FK in the second table, is there any way of exporting/importing and doing update to several tables in one singel query? Or do I need several quries, one for each table.

    thanks
    Sohail

  2. #2
    Robertt is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    12
    Each table will need to be updated via a separate SQL statement/query. Start by adding/updating records in the parent table, then you can add/update records in the child table.

    You may also want to use something similar to the criteria below in the insert statement for your child table to make sure you don't accidentally create orphans in your SQL database. "where not exists (select * from child where child.pk=parent.pk)"

    HTH,

    Robert

  3. #3
    lupis is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    23
    Hi Robert,

    I am using a master form with a subform to do the updates, but have been only been able to update the master table.

    I have previously been able to update and export one record based on recordID for one table in that form. I used the following criteria:

    [Forms]![Contact Details].[ID]

    This, the Contact Details form also includes a subform called frmActions. I have a query connected to the subform frmActions for INSERT INTO a online SQL version of this table. But how can I acomplish doing the INSERT INTO to two table from a form with a subform. I tried to do this using the criteria : [Forms]![frmActions].[AID] in the query for that subform, but in my masterform,can I update both forms incl the subform.

    I have previously updated the masterform with a button connected with a Embeded Macro with a Open Query action. Should I have two Open Query actions, one for the master form and one for the subfrom? Is the a better method so I can avoid all the popups Access is giving me.

  4. #4
    Robertt is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    12
    Lupis,

    If I understand you correctly, you want to perform BOTH update statements at once. Have you tried putting both of your INSERT statemetns in your [Contact Details]? You already know the primary key for the child form, so can you just do a INSERT INTO backupTable (...) SELECT ... FROM childTable where pk=XXX and include this in the same event that copies your master form? Regarding the popups, take a look at docmd.setWarnings().

    Robert

  5. #5
    lupis is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    23
    Hi Robert
    I am not quite following you, so in my master form (Contact Details)the query connected to the embeded macro should include INSERT INTO for both tables?
    I will explain the structure of my tables.
    Local tables:
    Contacts (PK=ID)
    Actions_local(PK=AID, FK=ID)

    Online SQL Server tables:
    dbo_Contacts (PK=ID)
    dbo_Actions_local(PK=AID, FK=ID)

    Masterform (Contact Details)
    Subform in Contact Details (frmActions)

    I am using this query to update the Contacts table in my Contact Details form:
    INSERT INTO dbo_Contacts ( ID, FirstName, LastName, Company, EmailAddress)
    SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName, Contacts.Company, Contacts.EmailAddress,
    FROM Contacts
    WHERE (((Contacts.ID)=[Forms]![Contact Details].[ID]));

    Currently I can update Contact Details for the local and online Contacts and dbo_Contacts. Left is the fields in the subform frmActions to update the dbo_Actions_local table from the local Actions_local table.
    These are the fields I need to update:
    INSERT INTO dbo_Actions_local ( AID, ActionDate, Stock, Amount, Cost, Depot, FundBankAgent, ID )
    SELECT Actions_local.AID, Actions_local.ActionDate, Actions_local.Stock, Actions_local.Amount, Actions_local.Cost, Actions_local.Depot, Actions_local.FundBankAgent, Actions_local.ID
    FROM Actions_local;

    So how can i pull these both actions togather, putting both of my INSERT INTO in my [Contact Details]? From the above information how would the SQL query synthax look like?

    -Sohail

  6. #6
    Robertt is offline Novice
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    12
    Lupis,

    I think you are overthinking this.

    Both sets of data have to be separate SQL statements/executions, but they can both be executed by the same event. Does that make sense? The syntax is not any different between the main form and subform, it just has be executed twice (with table specifics of course) in the same event; one time for your main form, the second time for your child form data.

    Remember that your child form is linked to the main form by the same id/pk fields. So you can use the same syntax that you are using in your main form, but adjust it for the subform and include the sql in your event that triggers the INSERT statement for your main form.

    HTH,

    Robert

  7. #7
    lupis is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    23
    Yes, I already have two different SQL statements, one for the Contacts table and executed from the main form(Contact Details)
    INSERT INTO dbo_Contacts ( ID, FirstName, LastName, Company, EmailAddress)
    SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName, Contacts.Company, Contacts.EmailAddress,
    FROM Contacts
    WHERE (((Contacts.ID)=[Forms]![Contact Details].[ID]));

    and the second qurery(without any reference to any Forms!FormName.ControlName as an criteria) for the subform frmActions for the Actions_local table:
    INSERT INTO dbo_Actions_local ( AID, ActionDate, Stock, Amount, Cost, Depot, FundBankAgent, ID )
    SELECT Actions_local.AID, Actions_local.ActionDate, Actions_local.Stock, Actions_local.Amount, Actions_local.Cost, Actions_local.Depot, Actions_local.FundBankAgent, Actions_local.ID
    FROM Actions_local;
    So how do I execute both these queries in the same event? and do I need to change my qeury connected to the subfrom frmActions for the Actions_local table.

    Currently I am firing the event for first query for the Contacts table from the Contact Details form from a button with a Open Query action, should I insert a similar Open Query action for the second query for the Actions_local table of the subfrom frmActions?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. if exists UPDATE else INSERT
    By lloyddobler in forum Programming
    Replies: 18
    Last Post: 01-04-2012, 11:35 PM
  2. Multiple UPDATE in single query
    By nishezaman in forum Access
    Replies: 0
    Last Post: 12-13-2009, 01:40 AM
  3. Update Multiple tables from one form
    By KenK in forum Forms
    Replies: 0
    Last Post: 10-30-2009, 08:44 PM
  4. Updating two tables using SQL Insert Into
    By glazzaro in forum Programming
    Replies: 0
    Last Post: 05-02-2008, 10:52 PM
  5. Update Tables
    By lschlitt in forum Programming
    Replies: 2
    Last Post: 05-28-2006, 02:11 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums