2013 Web App Data Macro to Append (1 Viewer)

dp73

New member
Local time
Today, 15:19
Joined
Jun 6, 2015
Messages
1
Hi
I thought this would be simple ;-/ Maybe it is, and I'm just missing something.

I have a simple Web App; single Table, Sheet and Form(view).

In addition to the generated PrimaryKey, one of the fields is always unique.

I need to append 200-300 records per day via a csv file.

Considering the constraints of the Web App I decided that this would be best achieved by uploading to a temporary table and running a Data Macro to append the unique, extra records. (the new csv contains all prior records, so I'm trying to filter them out in the Macro)

The main table is called "Master" and the new table is "NewTemp"

I've been getting errors upon saving the macro (either "..Macro AXL is invalid" or "Unable to resolve the reference to 'Master'....")

I've tried various combinations of Macros and Lookups along the logic of:

For Each Record in NewTemp

Lookup Record in Master
Where
[NewTemp].[Result Id] <> [Master].[Result Id]

Create Record in Master

etc.., etc...

I'm sure this can be done, but my logic and/or syntax is flawed.

Any help would be greatly appreciated.

Thank you.
 

AccessJunkie

Senior Managing Editor
Local time
Today, 08:19
Joined
May 11, 2006
Messages
278
Hi,

Good question. Your scenario is definitely possible in Access 2013 web apps using data macros. I've done this many times myself for similar scenarios in my apps. There are probably a couple of different ways you could design the macro logic for this, but here is one way to accomplish your goal.

I created a small sample following your provided information to test out and the data macro logic works just fine in my test runs. I've added some comments to the macro logic so you can understand a little more what is going on and so you can use it as a learning tool.

For my sample I just created three fields in the two tables - ID, Field1, and Result ID. You will of course have to modify this logic for your sample since you have different/other fields to copy/append from the NewTemp table to the Master table.

Create a new named data macro (Home > Advanced > Data Macro) and then copy and paste all of this data macro logic I have here directly into your macro window:

<?xml version="1.0" encoding="UTF-16" standalone="no"?>
<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2010/12/application"><DataMacro><Statements><Comment>Loop through each of the imported records in the NewTemp table</Comment><ForEachRecord><Data><Reference>NewTemp</Reference></Data><Statements><Comment>Create a local variable through each pass and set it to No to assume that each record is unique coming in</Comment><Action Name="SetLocalVar"><Argument Name="Name">varIDExists</Argument><ExpressionArgument Name="Value"><Expression><Original>"No"</Original><StringLiteral Value="No"/></Expression></ExpressionArgument></Action><Comment>Now attempt to lookup a record in the Master table that has the same Result ID value</Comment><LookUpRecord><Data><Reference>Master</Reference><WhereCondition><Expression><Original>[Master].[Result ID]=[NewTemp].[Result ID]</Original><FunctionCall Name="="><Identifier Name="Master.Result ID" Index="0"/><Identifier Name="NewTemp.Result ID" Index="1"/></FunctionCall></Expression></WhereCondition></Data><Statements><Comment>If we reached here inside the lookup record that means we found a match in the Master table for the Result ID field. This means the record already exists so change the local varilable value to Yes to for the next step</Comment><Action Name="SetLocalVar"><Argument Name="Name">varIDExists</Argument><ExpressionArgument Name="Value"><Expression><Original>"Yes"</Original><StringLiteral Value="Yes"/></Expression></ExpressionArgument></Action></Statements></LookUpRecord><Comment>Now that we are finished with the lookup record action, check the value of the local variable. If the variable is still No, that means no record was found in the Master table and the varilable wasn't changed. This means we are safe to create the new record in the Master table by copying each value in the NewTemp value for the specific record we are processing right now.</Comment><ConditionalBlock><If><Condition><Expression><Original>[varIDExists]="No"</Original><FunctionCall Name="="><Identifier Name="varIDExists" Index="0"/><StringLiteral Value="No" Index="1"/></FunctionCall></Expression></Condition><Statements><Comment>Create the record now in the Master table</Comment><CreateRecord><Data><Reference>Master</Reference></Data><Statements><Action Name="SetField"><Argument Name="Field">[Master].[Field1]</Argument><ExpressionArgument Name="Value"><Expression><Original>[NewTemp].[Field1]</Original><Identifier Name="NewTemp.Field1"/></Expression></ExpressionArgument></Action><Action Name="SetField"><Argument Name="Field">[Master].[Result ID]</Argument><ExpressionArgument Name="Value"><Expression><Original>[NewTemp].[Result ID]</Original><Identifier Name="NewTemp.Result ID"/></Expression></ExpressionArgument></Action></Statements></CreateRecord></Statements></If></ConditionalBlock></Statements></ForEachRecord><Comment>Now that we finished checking this record in the NewTemp table, move to the next record in the NewTable table. The macro will reset the varilable again at the start of the next loop.</Comment></Statements></DataMacro></DataMacros>


You should be able to see everything I did in your macro window. Before you try to save this data macro, you'll need to adjust of course for the Field1 reference I have in my sample and add in all the other extra field names you need to copy on separate SetField actions in the appropriate spot.

Now save that named data macro (perhaps name it AppendRecords) and then attempt to run it from the browser window. I assume you already know how to do this part - define a UI macro on a view that uses RunDataMacro and select your saved named data macro.

If you've done it correctly, Access should skip over the existing records and only add new ones to the Master table.

I should point out that as the number of records in your NewTemp table increases, this will take longer and longer to process. If possible, you might want to try experimenting with restricting the number of records for Access to loop through by applying some kind of Where clause in the outermost ForEachRecord action. That will speed up the time to append the new data.

Hope this helps.

I've also attached a screenshot of the macro window in my test example for your learning as well as anyone else reading this.



--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
Senior Content Developer - Modern Assistance and Support Experience - Microsoft Corporation

Author - Microsoft Access 2013 Inside Out
Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010/2013 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/en-us/legal/Copyright/Default.aspx
----------
 

Attachments

  • MacroWindowLogic.png
    MacroWindowLogic.png
    54 KB · Views: 2,919

DonLeverton

Registered User.
Local time
Today, 09:19
Joined
Apr 9, 2016
Messages
10
This is great. I can't wait to give this a try when I get home.

I'm also having the thought that you could create an index on a key data field that is unique, and would therefore prevent duplicate records from being appended.

I use this technique in the desktop version of Access, and it seems to work fine there.

I've also been working with Excel 2016's "Get & Transform" (Power Query) and was excited to see an "Append" option there. The problem there is that it creates a new table in Excel, rather than actually appending the linked AWA table. :(

Jeff, I'm sure that you're the man that could tell me why the Access Web App lacks the ability to LINK to an Excel table rather than IMPORT ???
 

afernando

New member
Local time
Tomorrow, 02:19
Joined
Mar 10, 2017
Messages
1
Hi,

I'm trying to add a record to table B when a record is added to a table A. The macro posted in this post does the job but the macro has to be run manually to achieve the result.

Is there any way to make the macro run automatically when a new record is added to table A?
 

CatGentry

New member
Local time
Today, 08:19
Joined
Feb 6, 2017
Messages
6
Hi,

I'm trying to add a record to table B when a record is added to a table A. The macro posted in this post does the job but the macro has to be run manually to achieve the result.

Is there any way to make the macro run automatically when a new record is added to table A?


If you select the table, under the Design tab at the top you should see three events to trigger your macro: On Insert, On Update, On Delete. On Insert should get you what you need.
 

Pragnya

New member
Local time
Today, 08:19
Joined
Apr 25, 2017
Messages
1
Hi,
I created a macro to create a record upon an insert on a table and the macro also sets a field to a value. Tracing the macro i see that, its setting the field value but i don't see the new record added to the table. what could i be missing?

I have two tables users and user-group. Every time a record is created in the user table, I would like it to create a record in the user-group table and copy the First Name field value in the user table to an existing field in the user-group table called FirstName. This seems straightforward, not sure what is going wrong. Thanks in advance.
 

Attachments

  • Access Query.JPG
    Access Query.JPG
    16.1 KB · Views: 225

Users who are viewing this thread

Top Bottom