How do I add data to a row in MS Access without using update

Posted on 2008-01-30
Medium Priority
Last Modified: 2013-11-27
I have a temp table (in MS Access) with 7 columns. ID, projName, projInfo, enggFname, enggLname,devFname, devLname.
Using an " insert into select" statement, I insert a row with ID, projName and projInfo from tblProjects into tblTemp.
 tblProjects also contains enggID and devID as foreign keys. Using these foreign keys and other filter criteria, I need to add the enggFName, enggLname to the already existing row in tblTemp. Then I have to repeat the process to add devFname and devLname to the row in tblTemp to complete the insert.
Can I do this in a single query? I have tried using union and append without success. I need to have only a single row with the data inserted in parts in the temp table.
Any suggestions as to how I can build the query, or write multiple queries but execute them after a single event?

Thanks in advance.
Question by:annapolistech
  • 2
  • 2
LVL 44

Accepted Solution

Arthur_Wood earned 1000 total points
ID: 20776418
No, in order to modify data in a table, you must make an update query for each table to be updated.

What do you mean by "but execute them after a single event" ?

you can execute multiple update statements in the Event Handler for a single button, by generating an SQL string, then executing that SQL, then genrate the next query, and executing it:

Dim strSQL as string
Dim cn as Connection

set cn = CurrentProject.Connection
strSQL = "UPDATE Table1 set Field1 = " & Value1
cn.Execute strSQL
strSQL = "UPDATE TABLE2 set Field2 = '" & Value2 & "'"
cn.Execute strSQL

like that.

Is that what you mean?


Assisted Solution

mfsamuel earned 1000 total points
ID: 20777053
Link it to a Macro and execute the macro to run all insert or updates you need.

Macro > New

And add these lines:

1) Action: SetWarnings
  -Warnings On: No
2) Action: OpenQuery
  -Query Name: [your first insert]
3) Action: OpenQuery
  -Query Name: [your second insert]
n) Action: SetWarnings
  -Warnings On: Yes

This macro will run each update/insert in order.

Author Comment

ID: 20785794
Thanks for the replies, I will try the solutions and let you know.
LVL 44

Expert Comment

ID: 20788125
You should be aware that Macros in Access are not a good idea.  Microsoft strongly advises against using them (they are only supported in 2000+ for compatability with earlier versions of Access).  

It is IMPOSSIBLE to debug a Macro.


Expert Comment

ID: 20788368
Not that you are wrong, but can you provide a reference to support that statement?  I have used macros for years, and have found that automating many things is only posible with VBA code.

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Suggested Courses

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question