How can I insert data without deleting the existing record in MS Acces

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 5222 Views - Last Post: 09 April 2012 - 06:32 PM

#1 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

How can I insert data without deleting the existing record in MS Acces

Posted 21 February 2012 - 09:15 AM

I want to add records into access table which are new and some updated
I want to add data into table with existing data, without deleting or updating the existing record.
Right now its done like this
First delete data from tbl_request
then insert into tbl_request

i want this to be updating the table with updated data and inserting new data
data looks like

Request id , req_type,description, so on..

Thanks !

Is This A Good Question/Topic? 0
  • +

Replies To: How can I insert data without deleting the existing record in MS Acces

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 21 February 2012 - 04:34 PM

Use an INSERT SELECT sql action to create new record from an existing record.

Use an UPDATE sql action to change record.

Why do you need to do this?

Are you using bound forms for data entry and edit?
Was This Post Helpful? 0
  • +
  • -

#3 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 22 February 2012 - 10:19 AM

Hi June

My requirement is to update 2 tables in timely fashion so this will be a bat file runs every 2 hrs to get some new and updated data from another db(oracle) .
so right now its done action delete the records in the table and then action insert which give us some issues like when users use the ms access db reports are blanked out or incorrect data shows up, while bat is running.

so I want this to be like search the table and then update or insert .
any clue?
Thanks !
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 24 February 2012 - 05:59 PM

So you need to determine if record already exists and if not add it and if it does exist update the data.

This will require two SQL actions.

One will be to insert new records from table that are not already found in other table. This will require a subquery. See http://stackoverflow...e-with-an-extra

The other is an update.

All assumes a unique identifier common to both tables.
Was This Post Helpful? 0
  • +
  • -

#5 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 23 March 2012 - 09:12 AM

INSERT INTO ABREQ ( REQUEST_ID, REQ_TYPE, DESCRIPTION, APPLICATION, REQUEST_TYPE_NAME, BILLABLE, REQ_GROUP, EST_DESIGN, EST_DEV, EST_SYS_TEST, EST_FUNC_DESIGN_DOC, EST_TECH_DESIGN_DOC, EST_SA_OTHER, EST_BA_OTHER, EST_FA_OTHER, EST_DBA_OTHER, EST_FUNC_PEER_REVIEW, EST_TECH_PEER_REVIEW, INTERNAL_CATEGORY, BILLING_DATE, CANCEL_DATE, CLOSE_DATE, REQ_CAT, WARR, WARR_RSN, ORIG_REQ, ACT_ANALYSIS, EST_DESIGN_HOURS, EST_SYS_HOURS, EST_FUNC_DOC_HOURS, EST_FUNC_PEER, EST_FA_OTH_HOURS, EST_DEV_HOURS, EST_TECH_DOC_HOURS, EST_TECH_PEER_HOURS, EST_IMPL_HOURS, REQ_STATUS )
SELECT E.REQUEST_ID, E.REQ_TYPE, E.DESCRIPTION, E.APPLICATION, E.REQUEST_TYPE_NAME, E.BILLABLE, E.REQ_GROUP, E.EST_DESIGN, E.EST_DEV, E.EST_SYS_TEST, E.EST_FUNC_DESIGN_DOC, E.EST_TECH_DESIGN_DOC, E.EST_SA_OTHER, E.EST_BA_OTHER, E.EST_FA_OTHER, E.EST_DBA_OTHER, E.EST_FUNC_PEER_REVIEW, E.EST_TECH_PEER_REVIEW, E.INTERNAL_CATEGORY, E.BILLING_DATE, E.CANCEL_DATE, E.CLOSE_DATE, E.REQ_CAT, E.WARR, E.WARR_RSN, E.ORIG_REQ, E.P_IBM_ACT_ANALYSIS, E.P_IBM_EST_DESIGN_HOURS, E.P_IBM_EST_SYS_HOURS, E.P_IBM_EST_FUNC_DOC_HOURS, E.P_IBM_EST_FUNC_PEER, E.P_IBM_EST_FA_OTH_HOURS, E.P_IBM_EST_DEV_HOURS, E.P_IBM_EST_TECH_DOC_HOURS, E.P_IBM_EST_TECH_PEER_HOURS, E.P_IBM_EST_IMPL_HOURS, E.REQUEST_STATUS
FROM EXTRACT AS E LEFT JOIN ABREQ AS R ON R.REQUEST_ID = E.REQUEST_ID
WHERE (((R.REQUEST_ID) Is Null));

How can I add subquery here.
Was This Post Helpful? 0
  • +
  • -

#6 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 23 March 2012 - 08:55 PM

Subquery for what? That sql statement already looks complete. It has a subquery to select the records.
Was This Post Helpful? 0
  • +
  • -

#7 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 27 March 2012 - 09:54 AM

hello June7

I have macro which has 5 queries .
delete (current data), insert(new data),delete (current data), insert(new data).

now my problem is insert i am able to do it as previous post .its working fine.My problem is update and its taking long time and i think its not the best practice how can i check for any change then update the record. for more than 25 columsn in my table
thanks for you r help
Maya 29
Was This Post Helpful? 0
  • +
  • -

#8 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 27 March 2012 - 11:12 AM

Not sure what you mean about checking for any change. Maybe a find unmatched query. Do you want to provide sample data and show the UPDATE query?
Was This Post Helpful? 0
  • +
  • -

#9 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 27 March 2012 - 12:22 PM

Table A exists record Z and need to to see any column has data different in regards to tableB.
(table req and table extract has exact same columns.)

25 columns need to be update if any data mismatch is there when we compare with table req and table extract row by row.

UPDATE tbl_req
SET tbl_req.REQ_TYPE = (select REQ_TYPE from EXTRACT), 
tbl_req.DESCRIPTION = (select DESCRIPTION from EXTRACT), 
tbl_req.APPLICATION = (select APPLICATION from EXTRACT), 
tbl_requests.REQUEST_TYPE_NAME = (select REQUEST_TYPE_NAME from EXTRACT), 
tbl_requests.BILLABLE = (select BILLABLE from EXTRACT), 
tbl_requests.REQ_GROUP = (select REQ_GROUP from EXTRACT), 
tbl_requests.EST_DESIGN = (select EST_DESIGN from EXTRACT), 
tbl_requests.EST_DEV = (select EST_DEV from EXTRACT), 
tbl_requests.ST_SYS_TEST = (select EST_SYS_TEST from EXTRACT), 
WHERE ((([EXTRACT].[REQUEST_ID])=[tbl_req].[REQUEST_ID]));


Was This Post Helpful? 0
  • +
  • -

#10 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 29 March 2012 - 06:18 PM

I have never seen UPDATE SQL statement with that syntax. Does it work (I doubt it)? Every UPDATE of data between tables I have ever run or seen uses a table join, not a SELECT subquery for each field.

Do you really need to compare or just go ahead and run UPDATE? If data is different it will be changed, if not then the replacement won't matter, it is same data.

What the UPDATE won't identify is if there are IDs in one table that are not in the other, if that is possible. That is where the find unmatched query is needed.

This post has been edited by June7: 29 March 2012 - 06:20 PM

Was This Post Helpful? 0
  • +
  • -

#11 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 31 March 2012 - 12:18 PM

UPDATE tbl_req
SET tbl_req.REQ_TYPE = EXTRACT.REQ_TYPE,
	tbl_req.DESCRIPTION = EXTRACT.DESCRIPTION,
	tbl_req.APPLICATION = EXTRACT.APPLICATION,
	tbl_req.REQUEST_TYPE_NAME = EXTRACT.REQUEST_TYPE_NAME,
	tbl_req.BILLABLE = EXTRACT.BILLABLE,
	tbl_req.REQ_GROUP = EXTRACT.REQ_GROUP,
	tbl_req.EST_DESIGN = EXTRACT.EST_DESIGN,
	tbl_req.EST_DEV = EXTRACT.EST_DEV,
	tbl_req.ST_SYS_TEST = EXTRACT.EST_SYS_TEST,
	WHERE (((tbl_req.REQUEST_ID) Is  NOT Null));


If my extract table has null value how will i update to tbl_req.
thank you
maya29

This post has been edited by maya29: 31 March 2012 - 12:19 PM

Was This Post Helpful? 0
  • +
  • -

#12 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 31 March 2012 - 12:53 PM

Query syntax still not correct. Need to join the two tables on the common ID fields. Access Help has guidelines on building query with the query designer.

The Null should pass through in the UPDATE.

Test with a copy of the destination table.
Was This Post Helpful? 0
  • +
  • -

#13 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 02 April 2012 - 11:06 AM

UPDATE tbl_req AS R 
      INNER JOIN EXTRACT AS E ON R.REQUEST_ID = E.REQUEST_ID 
  SET R.REQUEST_ID = E.REQUEST_ID, 
      R.REQ_TYPE = E.REQ_TYPE, 
      R.DESCRIPTION = E.DESCRIPTION, 
      R.APPLICATION = E.APPLICATION, 
      R.REQUEST_TYPE_NAME = E.REQUEST_TYPE_NAME, 
      R.BILLABLE = E.BILLABLE, 
      R.REQ_GROUP = E.REQ_GROUP, 
      R.EST_DESIGN = E.EST_DESIGN, 
      R.EST_DEV = E.EST_DEV
WHERE (((R.REQUEST_ID) Is Not Null));   



I have rewritten like this is this right syntax?
Thanks !
Maya29

This post has been edited by maya29: 02 April 2012 - 11:06 AM

Was This Post Helpful? 0
  • +
  • -

#14 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: How can I insert data without deleting the existing record in MS Acces

Posted 02 April 2012 - 06:06 PM

Wait, are those ID fields autonumber datatype? If not, and those IDs are representing same record then should work. Although since the IDs are already equivalent, does nothing to set one equal to another. Also, the INNER join should assure that no record has ID that is null so that WHERE is probably extraneous.

Test it on copy of table and see what happens.

This post has been edited by June7: 02 April 2012 - 06:07 PM

Was This Post Helpful? 0
  • +
  • -

#15 maya29  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 22
  • Joined: 30-January 12

Re: How can I insert data without deleting the existing record in MS Acces

Posted 09 April 2012 - 01:32 PM

hello June

I am getting Error 2950...
message is Operation Must use an updateable query
IDs are representing same record

Any clue?
Maya29
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2