[AccessD] Copying Records

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