Rocky Smolin at Beach Access Software
rockysmolin at bchacc.com
Sat Jan 10 10:40:49 CST 2009
Arthur:
Yeah, the new templates have to be a complete replace. Some templates may
be obsolete - so they can't be left behind. Some may be changed. Some new
ones added. But the code seems to be working OK and it was < 100 lines
including spaces and comments some of which was created by
cut/paste/replace. So, about an hour.
Regards,
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 Arthur Fuller
Sent: Saturday, January 10, 2009 7:55 AM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com