[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