How to Update Selective records in Access DB ,Table via

[Avisek2013]

I have a DB table in MS Access of 200 rows & 10 col. I filter a specific value (say for e,g "open Issues") in 5th column & I get a result of 40 rows (i.e out of 200). Now I want to update these 40 rows/records via " COLLECT DATA & UPDATE THROUGH E-MAIL ". which I cant. The update through email Process takes by default the whole table of 200 rows.

so I Create a new table by copy/paste these 40 rows from the original table & start updating it.

Is there any way where I can dynamically link the two tables.

Link in the sense...changes made in the new table of 40 rows will automatically get reflected in the original dB. as they both have the same primary key.

& dynamic in the sense. ... The new table has to be created every 4-5 hours which can contain different number of records in different times. (like sometimes 40, sometimes 60 ).

Please suggest

[ADezii]

The entire process can be automated without any Dynamic Linking, and without any user intervention at all, any updates to the New Table in any Field can be recreated in the Original Table. This will require a common Primary Key in each Table and the same number of Fields in each. The Fields must be in the same Ordinal Position. Basically, once created, the New Table must be exactly the same as the Original but with fewer Rows. I am also assuming a 1 <==> 1 Relationship between the two Tables.

[zmbd]

I have a DB table in MS Access of 200 rows & 10 col. I filter a specific value (say for e.g. "open Issues") in 5th column & I get a result of 40 rows (i.e. out of 200). Now I want to update these 40 rows/records via " COLLECT DATA & UPDATE THROUGH E-MAIL ". which I cant. The update through email Process takes by default the whole table of 200 rows.

ACC2010:
Yes this feature takes either the entire table or query and ignores the filters.

To achieve your goal using this method:

- You need to create a query that has just the columns and then the criteria to pull out just the records of interest. Save this query!
- Now select the new query you have made and...
-Ribbon>External Data>Collect Data>Create Email

This will now walk you thru the wizard and only the columns and information you have in the query will be used in the email.

Please be advised:
Not only is this feature not well documented and very clumsy to use (IMHO), the "feature" has been depreciated (removed) from Office/Access-2013 and the rumor has it that there will not be a replacement offered anytime soon.

[Avisek2013]

Hi ADezii....

Thanks for the reply.....You are absolutely on the point... But How to create this 1-1 relationship between the two tables in the Aceess

[ADezii]

Hi ADezii....

Thanks for the reply.....You are absolutely on the point... But How to create this 1-1 relationship between the two tables in the Aceess

You do not need to Create a Relationship between the Tables. It can all be done in Code as long as the Tables are structurally the same. You can Update as many Fields as you like in the Secondary Table, and using VBA (Recordsets), persist these changes to the Original Table. Should you wish to proceed along these lines, let me know.

[Avisek2013]

Yes ADezii, Yes both table are structurally identical, as the new table has been created by copying selective records from the original table only .. & has the same primary key.... Yes Bro I want to proceed.

[ADezii]

We have already been through the Assumptions, so I will not go over them again. The following Code will check every Field in every Record in the Child Table (tblChild) against every Field in the Main/Parent Table (tblMain) where their Primary Keys are equal, namely: tblMain.[ID] = tblChild.[ID]. If the Fields are different, then the appropriate Fields in tblMain will be Updated. The Function will return a Boolean Value indicating Success/Failure as well as a Running Counter indicating the Total Number of Updates (all Fields in tblMain that were Updated). Simply pass to the Function the Name of the Primary Key Field in both Tables, the Name of the Main Table, and the Name of the Child Table. It will do the rest and notify you accordingly. Now to the meat and potatoes!

1. Declare a Public/Global Variable to track the number of Updates:
   

   Public intNumOfUpdates As Integer

2. Copy-N-Paste the Function Definition to a Standard Code Module:
   

   Public Function fUpdateMainTable(strMain As String, strChild As String, strPK As String) As Boolean
   On Error GoTo Err_fUpdateMainTable
   Dim MyDB As DAO.Database
   Dim rstMain As DAO.Recordset
   Dim rstChild As DAO.Recordset
   Dim intFldCtr As Integer
   Dim strSQL As String
   
   Set MyDB = CurrentDb
   Set rstChild = MyDB.OpenRecordset(strChild, dbOpenForwardOnly)
   
   With rstChild
     Do While Not .EOF
       strSQL = "SELECT * FROM " & strMain & " WHERE [" & strPK & "] = " & ![ID]
       Set rstMain = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
         For intFldCtr = 0 To .Fields.Count - 1
           If .Fields(intFldCtr) <> rstMain.Fields(intFldCtr) Then
             'Value in corresponding Field is different, Update tblMain
             rstMain.Edit
               intNumOfUpdates = intNumOfUpdates + 1
               rstMain.Fields(intFldCtr) = .Fields(intFldCtr)
             rstMain.Update
           End If
         Next
           .MoveNext
     Loop
   End With
   
   rstChild.Close
   rstMain.Close
   Set rstChild = Nothing
   Set rstMain = Nothing
   
   fUpdateMainTable = True
   
   Exit_fUpdateMainTable:
     Exit Function
   
   Err_fUpdateMainTable:
     fUpdateMainTable = False
       MsgBox Err.Description, vbExclamation, "Error in fUpdateMainTable()"
         Resume Exit_fUpdateMainTable
   End Function

3. You can Call the Function in the following manner:
   

   Private Sub Command10_Click()
   Dim strMainTable As String      'Name of Main Table
   Dim strChildTable As String     'Name of Child Table
   Dim strPK As String             'Primary Key Field
   
   strMainTable = "tblMain"
   strChildTable = "tblChild"
   strPK = "ID"
   
   intNumOfUpdates = 0     'RESET Global Update Counter
   
   blnRetVal = fUpdateMainTable(strMainTable, strChildTable, strPK)
   
   If blnRetVal Then
     MsgBox intNumOfUpdates & " Records(s)" & IIf(intNumOfUpdates = 1, " has ", " have ") & _
            "been Updated in " & _
            strMainTable, vbInformation, "Update Successful"
   Else
     MsgBox "Some or all Records in " & strMainTable & " were not " & _
            "not able to be Updated", vbCritical, "Update Failed"
   End If
   End Sub

4. The Code will not work correctly with certain Type Fields, namely: Attachment, OLE Object, etc... The Code has been tested with the Basic Field Types and appears to work quite well.
5. Good Luck and any Questions feel free to ask.

[zmbd]

I think we've drifted off topic?
Avisek2013
Original question has do with using the Auto-Email feature to collect and update data.
The difficultly in using this method is that one must either build a query first, select and use it, or one is stuck with using the entire table as I explaned in Post#3

ADezii is a gifted coder and I am sure that his solutions will be most helpful; however, IMHO, we've now drifted away from the OP-Question which I took to mean, "What am I doing wrong with the email collect and update data..."

If I'm wrong, then my applogies...

[Avisek2013]

Hi Zmbd, Thanks for the reply, Actually I can write a query -> get selective records from the master table -> update the Selective records via email -> But then How to join or link the updates made in the query table into the Master table.... thats the part Adezii is actually answering. I am already exploring further on his method & will update for final success of my project But any alternatives are always welcome... I am bit novice in this part & have been loaded with the task....so will always look for your good suggestions

[zmbd]

But then How to join or link the updates made in the query table into the Master table....

Then you mis-understand what a "query table" is...
A Query is NOT a new table.
It IS a VIEW of the table.
If you change something within this VIEW, you change it on the table upon which the VIEW is based. MS Access 2010 Tutorials

You can also use a query to update, delete, and create new tables; however, in this case, using the email collection feature, that should not be what you were doing, well, you may be updating old data using the email collection (^-^).

In Your Case, the email method should automatically parse the returned email information and update the table.
>Ribbon>Externa l Data>Collect Data>Managed Replies> Once the dialog box opens showing all of the messages you have sent. You can then select and alter how these messages are processed. It sounds to me that you should go in and set the processing to automatic for your least bother.