[AccessD] Copying Records

Presumably there's a column in the BE table that identifies whether a given
template is supplied or user-created. If so, then there's no need (I think)
to delete the supplied templates first. You can just do

<sql>
UPDATE myTable M
INNER JOIN vendorTable V ON M.ID = V.ID
SET M.col1 = V.col1
-- etc.
</sql>

This however presupposes that there won't be new records in the update sent
out, just replacements. To handle new templates included in the update, you
could

<sql>
INSERT myTable M
SELECT * FROM vendorTable V
WHERE V.ID NOT IN( SELECT ID FROM myTable)
</sql>

If you need the two-step, the code you wrote may be simpler. If not, I think
my first chunk of SQL will be faster than deleting the old rows and
appending the new ones.

hth.
Arthur

On Sat, Jan 10, 2009 at 9:30 AM, Rocky Smolin at Beach Access Software <
rockysmolin at bchacc.com> wrote:

> Asger:
>
> Thanks.  Don't know if I was clear about this but it's a whole batch of
> records that need to be transferred, not just one. These records are the
> templates for boilerplate actions in a legal matters tracking system which
> the client is going to make into a commercial product.
>
> So the task is to send out an update which will replace in the client's
> back
> end, all of those records in one go. Since the client may have also added
> their own records, the program must delete just the supplied templates and
> replace them with the updates.
>
> I needed to get it done yesterday so I wrote a chunk of code.  Didn't take
> long.   If anyone's interested I can post the code.
>
>
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Asger Blond
> Sent: Saturday, January 10, 2009 5:46 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Copying Records
>
> Hi Rocky,
>
> I guess you want the new value of an AutoNumber column.
> If so you can retrieve that immediately after your db.Execute "INSERT..."
> statement this way:
>
> Dim rs As DAO.Recordset
> Set rs = db.OpenRecordset("SELECT @@IDENTITY") Debug.Print rs.Fields(0)
>
> HTH
> Asge
>
> -----Oprindelig meddelelse-----
> Fra: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] På vegne af Rocky Smolin at
> Beach Access Software
> Sendt: 9. januar 2009 17:21
> Til: 'Access Developers discussion and problem solving'
> Emne: [AccessD] Copying Records
>
> Dear List:
>
> Three back end tables - ActionGroups, Actions, and ActionDates.  These are
> mirrored by ActionGroupsTemplates, ActionsTemplates, and
> ActionDatesTemplates in the front end.
>
> Actions is a child of ActionGroups - one to many relationship with
> ActionGroupsID as a FK.
>
> ActionDates is a child of Actions - one to many relationship with ActionsID
> as a FK.
>
> When the front end is updated, I need to delete all of the records in the
> three back end tables that have their Template flag set to True (there may
> be user defined Actions in the back end that need to stay there) and
> replace
> them with the front end tables' records.
>
> I thought a few simple db.execute "Delete *..."  and db.Execute
> "INSERT..."s
> would do it.  But since the PK of ActionGroups is going to change when the
> records are added to the back end, I'd need to make the new FK of Actions
> the PK of the new ActionGroups record and the new FK of ActionDates the PK
> of the new Actions record.
>
> So it can be done with a bunch of code - I've done that before.  But is
> there a more elegant way?
>
> MTIA
>
>
>
>
>
> Rocky Smolin
>
> Beach Access Software
>
> 858-259-4334
>
> www.e-z-mrp.com <http://www.e-z-mrp.com/>
>
> www.bchacc.com <http://www.bchacc.com/>
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>