After Insert Data Macro - Fire for Multiple Records Simultaneously (1 Viewer)

Hi,

I'm trying to update a linked SQL Server table whenever data is appended to an Access table in a linked back end. I setup an after insert data macro over the table to call a saved query, but I couldn't get this to work.

After researching online I found that others had achieved what I was aiming to do by setting the data macro to call a function (using the SetLocalVar macro action), into which they pass a key value from the appended record. This function then executes a query built as a variable string, using the key value in its criteria.

I've set this up and it is working fine. So when I write to my linked Access table, the trigger is fired, the function called with the key value as a parameter, the query is run and the relevant record in my linked SQL Server table is successfully updated.

The scenario I'm struggling with is when multiple records are appended to my Access table. When I append two records simultaneously, the trigger is fired once and my function is called once, with the key value of the first record only in its parameters. This function then builds the query string using the value of the first record, updates the relevant record in the SQL Server table for that record, but not the second one.

Does anyone have any suggestions on how to get this working? It's almost like I want to run the macro action for each record in that append action that has called the trigger to fire. But I don't know how. Please help!

Thanks,

Matthew

queries run all records at once. Youd use a query that has a function in it, this would hit all records involved.
Either during the append,
or
afterwards, use a field say [DateAdded] , then run your function against those records that appended that time.

Ranman256 said:

queries run all records at once. Youd use a query that has a function in it, this would hit all records involved.
Either during the append,
or
afterwards, use a field say [DateAdded] , then run your function against those records that appended that time.

Click to expand...

Hi,

Thanks for your suggestions, but I'm not entirely sure that I follow. Are you suggesting that I store the date and time added to the table - say a new field with the default value of the now() function - and then pass the value of the new field into function via the data macro? The function would then execute the query for all records in the table where the new field is equal to the value passed into the function.

But then that would only work if all of the records simultaneously appended to the table had exactly the same time to the second in the new field. Which presumably wouldn't always be the case. Eventually the scenario will occur when they are processed with times differing by a second. Two records appended with times one second apart would result in the data macro calling the function but passing only the value contained in the first record, and therefore not triggering an update for the second.

If I don't include the time and store just the date, then every time something new is appended to the table, the corresponding records in the SQL Server table will be updated for every record appended that day. Sorry if I'm missing something obvious, I've never used these data macros before.

Thanks,

Matthew

That's what I was going to suggest on the other thread. Simply store the time before running the append (you could subtract 1 second to make sure things worked) - Then update all records where the time of the appended record is after the time you just stored. By definition it will be later than the stored time.

Minty said:

That's what I was going to suggest on the other thread. Simply store the time before running the append (you could subtract 1 second to make sure things worked) - Then update all records where the time of the appended record is after the time you just stored. By definition it will be later than the stored time.

Click to expand...

Thanks Minty, that's a great suggestion.