Apend Query for master and sub form

[lupis]

I am trying to update one single record each in a main form and subform to online SQL tables.

I have two append quries that are connected to a form, the main form also includes a subform.

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 able to update the main form with a append query to the online SQL Server table, but not the subform.

The sql for the main form is

INSERT INTO dbo_Contacts ( ID, FirstName, LastName )
SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName
FROM Contacts
WHERE (((Contacts.ID)=[Forms]![Contact Details].[ID]));

The sql for the subfrom is

INSERT INTO dbo_Actions_local ( AID, ActionDate, Stock, ID )
SELECT Actions_local.AID, Actions_local.ActionDate, Actions_local.Stock,  Actions_local.ID
FROM Contacts INNER JOIN Actions_local ON Contacts.ID=Actions_local.ID
WHERE (((Actions_local.AID)=[Forms]![Contact Details]![frmActions].[Form]![AID]) AND ((Actions_local.ID)=[Contacts].[ID]))

Both queries are executed with the same event with each OpenQuery action in a macro for the On Click event of a button

[ConneXionLost]

Hi lupis,

I haven't worked with "online SQL tables" before, so I don't know if there's much I can help you with here. Do you mean SQL Server?

You appear to be updating the remote tables every time you use your form. If so, you may be keeping everything synchronized, but at the cost of tying up your network very frequently, as well as making your local tables seem redundant. It might be a little more efficient to run an archive process after you've finished updating records with your form. i.e. Use queries to compare the local & remote tables, put the difference in a temp table, then run an append on that difference to move it to the archive. Do this process for each of your tables separately. That way you won't need to involve your form at all, or be forced to use joins in the append query.

The syntax here (in red) doesn't seem right:

INSERT INTO dbo_Actions_local ( AID, ActionDate, Stock, ID )
SELECT Actions_local.AID, Actions_local.ActionDate, Actions_local.Stock,  Actions_local.ID
FROM Contacts INNER JOIN Actions_local ON Contacts.ID=Actions_local.ID
WHERE (((Actions_local.AID)=[Forms]![Contact Details]![frmActions].[Form]![AID]) AND ((Actions_local.ID)=[Contacts].[ID]))

About the button:

Both queries are executed with the same event with each OpenQuery action in a macro for the On Click event of a button

Is your button forcing the form to save your changes before the queries run?

One thing I must point out; I think it's a bad habit to use a generic title of "ID" for your table keys. Try using something a little more descriptive, so they won't get confused in future queries. For example: ContactID, ActionID, or ContactDetailsID.

Hope this helps.

Cheers,

[lupis]

Hi,
Thanks a lot for your suggestion of creating a batch append query for the difference between the local and online tables for new records.

But is there a similar way of checking for diff between updated records, not only new ones, so I can just update the records that have been edited with new values.

Reg. the syntax for the subform update, I took this from the following page: http://www.mvps.org/access/forms/frm0031.htm Should I have used a different syntax there.

Thanks
Sohail

[ConneXionLost]

Hi,

But is there a similar way of checking for diff between updated records, not only new ones, so I can just update the records that have been edited with new values.

Yup, that would be with a query. Here's an example:

SELECT tblCompareA.*
FROM tblCompareA INNER JOIN tblCompareB ON tblCompareA.CompareID = tblCompareB.CompareID
WHERE (tblCompareA.Type)<>[tblCompareB].[Type]) OR (tblCompareA.System)<>[tblCompareB].[System]) OR (tblCompareA.value)<>[tblCompareB].[value]) OR (tblCompareA.Increment)<>[tblCompareB].[Increment]);

The example above compares a table with five fields. The first is the Primary key, and the remaining four are attributes. Each attribute is shown in the WHERE portion of the statement with a <> check. Note that the key does NOT have a <> check!

Reg. the syntax for the subform update, I took this from the following page: http://www.mvps.org/access/forms/frm0031.htm Should I have used a different syntax there.

Well, if you use the batch update approach, this point becomes moot. I could also be wrong about the syntax; it just looked strange. If you've successfully used it elsewhere, then no problem.

Cheers,

[lupis]

Thanks a lot.. I think I prefer the batch update method .