Solved

Insert to Table Then Update A Different One

Posted on 2010-08-26
17
581 Views
Last Modified: 2013-11-28
I have a peice of code that works on SQL Server. But now I need the same functionality in Access. I need to insert  or update values in child table, capture the new record, then update a value in the parent table. I can't make the sql work as is so can anyone tell me how to modify the statements to work in MS Access?
INSERT INTO [dbo].[RecordedLesson]
           ([RegID]
           ,[Attended]
           ,[Duration]
	   ,[Comments])
     VALUES
           (@RegID, @Attended, @Duration, @Comments)
	DECLARE @idn int
	SET @idn=scope_identity()


UPDATE pr
   SET [PlayerID] = l.PlayerID  
     , [ModuleId] = l.ModuleId
     , [SignupDate] = l.[SignupDate]
     , [LessonTypeID] = l.[LessonTypeID]
     , [LocationID] = l.LocationID 
     , [Lessons] = l.Lessons 
     , [Paid] = l.Paid 
     , [Used] = ((SELECT rl.[Duration] 
                   FROM [dbo].[RecordedLesson] rl
                 WHERE rl.LesID = @idn) + l.Used
                )
  FROM [dbo].[LessonRegistration] pr
  JOIN [dbo].[LessonRegistration] l  
    ON pr.RegID = l.RegID
 WHERE pr.RegID = @RegID

Open in new window

0
Comment
Question by:wdarnellg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +2
17 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 33536274
Click the "Request Attention" button and ask the the S+QL syntax zone be added to this Q.

A lot of Experts there get questions like this, and may be able to help.

;=-)

JeffCoachman
0
 

Author Comment

by:wdarnellg
ID: 33538959
Thanks for your response Jeff. I have followed your suggestion.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 33541408
Here are two key techniques that will get you on your way.

To insert new record in table1(id autonumber, c char)

    Dim rs As Recordset
    Set rs = CurrentDb.TableDefs("table1").OpenRecordset
    rs.AddNew
    rs!c = "abc"
    newid = rs!id
    rs.update

Note that the autonumbered field id from just-created record is available even before the new record is commited to the database.

The syntax to update table2 from a join with table1:

UPDATE table2 INNER JOIN Table1 ON table2.id = Table1.id SET table2.cc = [table1].[c];

You can assist yourself by creating Query in Access and then either copying its sql, or saving the query and running it by name from the code.

Going even further however, evaluate whether you need the code to begin with. Because Access keeps the data in the same place as presentation layer, maybe you can simply bind the controls on the forms to the respective fields in the tables, so the data will be created and updated automatically without any code at all.





0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33541411
In MS Access, I believe @@identity works as there is NOT a scope_identity() function.

If you have difficulties with translating anything else in the statements, please advise, but should be pretty standard SQL and for the parameters, I would suspect you are doing this in a VBA procedure|function, so can just use parameters|variables there.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 33541421
Good points, vadimrapp1!
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 33542102
@vadimrapp1,
If one uses your approach:
  • Is it possible that another user might also generate the same Autonumber value before you commit the record to the database (thus creating issues for one or the other of the users)?
  • What happens if you do not actually commit the record to the database?  I.e. is that Autonumber value lost to the world and a gap will exist in the sequence of UIDs?
The former question may answer the latter but it is also probably of more significance.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 33542381
> Is it possible that another user might also generate the same Autonumber value before you commit the record to the database (thus creating issues for one or the other of the users)?

No, if another user issues addnew after the 1st one issued addnew but before the 1st one has commited the record, 2nd user's new autonumber will be more than the 1st one. However, as you probably know, when there are more than one user, it's highly advisable to use SQL Server. You can try it out by opening 2 instances of Access, opening the same database, and running the same code in debug mode.

>  I.e. is that Autonumber value lost to the world and a gap will exist in the sequence of UIDs?

Maybe not to the world, but to this particular table it is lost. Yes, there will be a gap. Same as if you delete a record from the middle.

0
 

Author Comment

by:wdarnellg
ID: 33546533
Thank you everyone. I will get back working tonight as soon as I am back at my machine and get back to you.
0
 

Author Comment

by:wdarnellg
ID: 33548245
I feel rather silly as I need further explanation. I am using Access 2010 and my tables are linked to a SQL Server db via an ODBC connection on a website host. I am seeking to use Access functionality on my desktop to update tables on the web. The previous code snippet is from a stored proc that I wish I could simply fire from Access.


I would love to automate this through the controls, but it seems I need at least some Macro assistance.
There are some Macro commands that I can't seem to quite peice together, but it seems they are only available to me if I have local tables, therefore to get started, I converted the needed tables to 'Local'.  I saved this one Macro in notepad / xml showing my attempt to update the second table in the first table's 'After Insert' event. I get an error message that says: "EditRecord failed because the default alias represents a record which is read only" I don't know how to make the alias editable.

I am thinking that the VBA code suggestion belongs with a button click event? I'll start on that now. I am not familiar with VBA syntax, but here goes...

2010-Macro.txt
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 33548321
If you already have working stored procedure in sql server, the indeed, why converting. Launch it from your Access using pass-through query.

Or, you can create an ADP instead of MDB, which will give direct access to sql server.  http://accessjunkie.com/faq_23.aspx
0
 

Author Comment

by:wdarnellg
ID: 33561229
I like the ADP I think. I just can't seem to figure out how to pass the required parameters to the stored procedure. I am trying to use:
DoCmd.RunSQL "EXEC InsertRecordedLesson"

Can you guide me through how to give parameters to the Stored Proc?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 33561452
currentproject.connection.execute "mystoredprocedure 'string-parm',number-parm "

usually you will simply concatenate:

currentproject.connection.execute "mystoredprocedure '" & forms!form1!field1 & "'," & forms!form1!numericfield




0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 33561551
...though, speaking of the particular stored procedure in the initial post, I think it would be the best to set up a trigger on the table in sql server, so whenever new record is inserted in the table RecordedLesson, the trigger will automatically update the totals in the table LessonRegistration .

Even better solution would be to eliminate the calculated totals in the table LessonRegistration alltogether, and instead create a view with aggregate functions (unless the table RecordedLesson is so large that calculating the totals takes significant time), so the totals are calculated on-the-fly when they are needed. No need to keep redundant data, unless this is is data warehouse.
0
 

Author Comment

by:wdarnellg
ID: 33564210
A trigger seems like a good idea. I have never created one. Can I create one through Management Studio?
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
ID: 33564305
yes, you can.

create trigger RecordedLesson_U on RecordedLesson for insert as
set nocount on
update LessonRegistration set [used] = [used] + Inserted.duration
from LessonRegistration join Inserted on Inserted.RegId=LessonRegistration.regid

But as I said, even better to drop column [used] from LessonRegistration, rename LessonRegistration to LessonRegistration0, and  and

create view LessonRegistration as
select LessonRegistration0.*, isnull(sum([RecordedLesson.used]),0) as Used from
LessonRegistration0 left join RecordedLesson on LessonRegistration0.regid=RecordedLesson.regid
group by <all columns of LessonRegistration0 >
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

752 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