I have an input table.  Those records need to be added to another table IF 

AN EXISTING RECORD DOES NOT EXIST (based on SSN).  If the record exists, I 

need to update any changed info that is in the first table.  Need code for 

a module. 



Thanks!

The best way is probably to execute two 'action queries' from your code.  To

add unduplicated records you can first ID the records using the 'find

umatched' query wizard.  Once you confirm that you're selecting out the

right set of records, save that query & then use it as the source for an

append query.



To update pre-existing records you can do something similar--this time with

a plain query joining the input table and the real table, used as the source

for an update query.



To automate running both of these queries (and perhaps combine them in a

single database transaction), use DAO's QueryDef object, or ADO's Command

object.



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Karen Hoffman [mailto:karen.hoffman@e...]

Sent: Monday, December 10, 2001 6:21 AM

To: Access

Subject: [access] Add/update record based on records from another table





I have an input table.  Those records need to be added to another table IF 

AN EXISTING RECORD DOES NOT EXIST (based on SSN).  If the record exists, I 

need to update any changed info that is in the first table.  Need code for 

a module. 



Thanks!

Karen - It could be done without "coding".



Add a field to your Input Table ( Y/N named "exists").



Run a query that updates the new field ("exists") to

YES for those that are in your Master Table.



Then do an APPEND for those that that are still NO in

the "exists" field.



Dave 





--- Karen Hoffman <karen.hoffman@e...>

wrote:

I have an input table.  Those records need to be

added to another table IF AN EXISTING RECORD DOES NOT

EXIST (based on SSN). 



If the record exists, I need to update any changed

info that is in the first table.  Need code for 

a module. 

 

Thanks!







__________________________________________________

Do You Yahoo!?

Send your FREE holiday greetings online!

http://greetings.yahoo.com

Karen, if you want to try this DAO code sample (change field and table

names to suit), this will do the whole thing in one pass.



I can provide ADO code if you prefer.



The code uses SEEK which is efficient but only applicable to 'local' (i.e.

not attached) recordsets with a unique ID.

You will need to use the FIND method otherwise but e-mail for suitable code

if you need it.



Yes, you can easily write 2 queries and run them using minimal code but I

prefer to do things the way I have illustrated.





Public Function SyncRC_Options()

Dim DB As DAO.Database, CO As DAO.Recordset, RO As DAO.Recordset

Dim RETVAL As Variant, SEQ As Long, COUNTRECS As Long, MSGTXT As String

Dim CRITERIA As String



Set DB = CurrentDb()

Set CO = DB.OpenRecordset("Table_Comparison_Options", dbOpenTable)

Set RO = DB.OpenRecordset("dbo_RC_OPTIONS", dbOpenDynaset)

RO.Index = "PrimaryKey"       ' specify the key field in your target table



' next 5 lines get the source table recordcount and set up a progress bar

so you can follow how your process is going

CO.MoveLast

CO.MoveFirst

COUNTRECS = CO.RecordCount

MSGTXT = "Sync RC Options.."

RETVAL = SysCmd(acSysCmdInitMeter, MSGTXT, COUNTRECS)



' now loop through source recordset and use SEEK to see if the target

exists

' if it does (Not RO.NoMatch) then edit the existing record

' if it does not, insert a target record



Do Until CO.EOF

  RO.Seek "=", CO![Table_Comparison_Desc]     ' search for exact match on

key field



  If Not RO.NoMatch Then

    RO.Edit

      RO![Source_Server] = CO![Source_Server]

      RO![Source_Database] = CO![Source_Database]

      RO![Target_Server] = CO![Target_Server]

      RO![Target_Database] = CO![Target_Database]

    RO.Update

  Else

    RO.AddNew

      RO![Task_Description] = CO![Table_Comparison_Desc]

      RO![Source_Server] = CO![Source_Server]

      RO![Source_Database] = CO![Source_Database]

      RO![Target_Server] = CO![Target_Server]

      RO![Target_Database] = CO![Target_Database]

    RO.Update

  End If



 ' update the progress bar and get next record from source

  SEQ = SEQ + 1

  RETVAL = SysCmd(acSysCmdUpdateMeter, SEQ)

  CO.MoveNext

Loop

' now tidy up and release variables - not essential but good practice

CO.Close

RO.Close

DB.Close

Set CO = Nothing

Set RO = Nothing

Set DB = Nothing

RETVAL = SysCmd(acSysCmdRemoveMeter)



End Function







--- Karen Hoffman <karen.hoffman@e...>

wrote:

I have an input table.  Those records need to be

added to another table IF AN EXISTING RECORD DOES NOT

EXIST (based on SSN).



If the record exists, I need to update any changed

info that is in the first table.  Need code for

a module.







*************************************************************************



This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.



The CIS marketing group, which is regulated for Investment Business by the Personal Investment Authority, includes:

Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions

CIS Unit Managers Limited Registered in England and Wales number 2369965 (also regulated by IMRO) - for unit trusts and PEPs

CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name

Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...



CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.



CIS is a member of the General Insurance Standards Council



CIS & the CIS logo (R) Co-operative Insurance Society Limited



********************************************************************************

Thanks, this is what I was looking for.



However, when I compile the module, I get an error: "User-defined type not

defined" on "DB As DAO.Database" 



Do I need to declare something?







 -----Original Message-----

From: 	joe.dunn@c... [mailto:joe.dunn@c...] 

Sent:	Tuesday, December 11, 2001 3:57 AM

To:	Access

Subject:	[access] Re: Add/update record based on records from another

table





Karen, if you want to try this DAO code sample (change field and table

names to suit), this will do the whole thing in one pass.



I can provide ADO code if you prefer.



The code uses SEEK which is efficient but only applicable to 'local' (i.e.

not attached) recordsets with a unique ID.

You will need to use the FIND method otherwise but e-mail for suitable code

if you need it.



Yes, you can easily write 2 queries and run them using minimal code but I

prefer to do things the way I have illustrated.





Public Function SyncRC_Options()

Dim DB As DAO.Database, CO As DAO.Recordset, RO As DAO.Recordset

Dim RETVAL As Variant, SEQ As Long, COUNTRECS As Long, MSGTXT As String

Dim CRITERIA As String



Set DB = CurrentDb()

Set CO = DB.OpenRecordset("Table_Comparison_Options", dbOpenTable)

Set RO = DB.OpenRecordset("dbo_RC_OPTIONS", dbOpenDynaset)

RO.Index = "PrimaryKey"       ' specify the key field in your target table



' next 5 lines get the source table recordcount and set up a progress bar

so you can follow how your process is going

CO.MoveLast

CO.MoveFirst

COUNTRECS = CO.RecordCount

MSGTXT = "Sync RC Options.."

RETVAL = SysCmd(acSysCmdInitMeter, MSGTXT, COUNTRECS)



' now loop through source recordset and use SEEK to see if the target

exists

' if it does (Not RO.NoMatch) then edit the existing record

' if it does not, insert a target record



Do Until CO.EOF

  RO.Seek "=", CO![Table_Comparison_Desc]     ' search for exact match on

key field



  If Not RO.NoMatch Then

    RO.Edit

      RO![Source_Server] = CO![Source_Server]

      RO![Source_Database] = CO![Source_Database]

      RO![Target_Server] = CO![Target_Server]

      RO![Target_Database] = CO![Target_Database]

    RO.Update

  Else

    RO.AddNew

      RO![Task_Description] = CO![Table_Comparison_Desc]

      RO![Source_Server] = CO![Source_Server]

      RO![Source_Database] = CO![Source_Database]

      RO![Target_Server] = CO![Target_Server]

      RO![Target_Database] = CO![Target_Database]

    RO.Update

  End If



 ' update the progress bar and get next record from source

  SEQ = SEQ + 1

  RETVAL = SysCmd(acSysCmdUpdateMeter, SEQ)

  CO.MoveNext

Loop

' now tidy up and release variables - not essential but good practice

CO.Close

RO.Close

DB.Close

Set CO = Nothing

Set RO = Nothing

Set DB = Nothing

RETVAL = SysCmd(acSysCmdRemoveMeter)



End Function







--- Karen Hoffman <karen.hoffman@e...>

wrote:

I have an input table.  Those records need to be

added to another table IF AN EXISTING RECORD DOES NOT

EXIST (based on SSN).



If the record exists, I need to update any changed

info that is in the first table.  Need code for

a module.







*************************************************************************



This e-mail may contain confidential information or be privileged. It is

intended to be read and used only by the named recipient(s). If you are not

the intended recipient(s) please notify us immediately so that we can make

arrangements for its return: you should not disclose the contents of this

e-mail to any other person, or take any copies. Unless stated otherwise by

an authorised individual, nothing contained in this e-mail is intended to

create binding legal obligations between us and opinions expressed are those

of the individual author.



The CIS marketing group, which is regulated for Investment Business by the

Personal Investment Authority, includes:

Co-operative Insurance Society Limited Registered in England number 3615R -

for life assurance and pensions

CIS Unit Managers Limited Registered in England and Wales number 2369965

(also regulated by IMRO) - for unit trusts and PEPs

CIS Policyholder Services Limited Registered in England and Wales number

3390839 - for ISAs and investment products bearing the CIS name

Registered offices: Miller Street, Manchester M60 0AL   Telephone

0161-832-8686   Internet  http://www.cis.co.uk   E-mail cis@c...



CIS Deposit and Instant Access Savings Accounts are held with The

Co-operative Bank p.l.c., registered in England and Wales number 990937,

P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS

Policyholder Services Limited as agent of the Bank.



CIS is a member of the General Insurance Standards Council



CIS & the CIS logo (R) Co-operative Insurance Society Limited



****************************************************************************

****

Yes, "Microsoft DAO 3.5x Object Library" (In references)

Another stumble..........



I get this error "Operation is not supported for this type of object." 



On this line: RO.Index = "fldSSN"       ' specify the key field in your

target table



fldSSN is a primary key, Text, 11, Required "No", Allow Zero Length "Yes",

Indexed "Yes (No Duplicates)", Unicode Compression "Yes"





(sorry to keep bothering you.............)







 -----Original Message-----

From: 	Walt Morgan [mailto:wmorgan@s...] 

Sent:	Tuesday, December 11, 2001 8:02 AM

To:	Access

Subject:	[access] Re: Add/update record based on records from another

table



Yes, "Microsoft DAO 3.5x Object Library" (In references)

Try this:



ro.index = "NameOfIndexHere" 'Not field name



OR it may have to do with the type of recordset your are returning; I know

that a table type supports indexed access (Seek)



Walt





Another stumble..........



I get this error "Operation is not supported for this type of object."



On this line: RO.Index = "fldSSN"       ' specify the key field in your

target table



fldSSN is a primary key, Text, 11, Required "No", Allow Zero Length "Yes",

Indexed "Yes (No Duplicates)", Unicode Compression "Yes"





(sorry to keep bothering you.............)