Solved

how do i know if a record was created and which ID was assigned?

Posted on 2014-12-21
14
134 Views
Last Modified: 2014-12-30
Hi Experts,

I have a table in sql BE named EmployeesTbl

Now I have a function in Access that  inserts records into the Employeestbl, using command below:
strSql = "Insert into Employeestbl(column1,column2..)values(1,2..)"
CurrentProject.Connection.Execute strSql

Open in new window

How can I know,
A-If record was successfully created?
B-which ID was assigned to the Record?
The ID column in sql is set for Identity Increment 1.
0
Comment
Question by:bfuchs
[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
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40512316
An error will be raised if it doesn't work.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40512325
Hi,
How can I get the error msg returned by SQL within my Access App?
Thanks,
Ben
0
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 40512428
also see: http://msdn.microsoft.com/en-us/library/windows/desktop/ms675023%28v=vs.85%29.aspx
Dim lngKt As Long
Dim lngID as long

strSql = "Insert into Employeestbl(column1,column2..)values(1,2..)"
CurrentProject.Connection.Execute strSql, lngKt 

MsgBox "number of records inserted: " & lngKt 

lngID  = Dmax("ID", "Employeestbl")

MsgBox "Last id: " & lngID  

Open in new window


It will be the best to create a stored procedure and call it from Access. You have more options and better error checking.
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 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 40512439
Also try:

cn.Execute ""Insert into Employeestbl(column1,column2..)values(1,2..)", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value
if nz(rs.Fields(0).Value)>0 then
   MsgBox "Insert was successful"
end if

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 40512584
Look at both the Err.Number and the Errors collection.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40513091
Since you are inserting only a single record, use the .AddNew method of DAO.  That way you will be able to retrieve the assigned ID as well as verify the insert.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40513224
Is Employeestbl an attached table?
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40513860
Hi Experts,
First Thanks to all for your input.

@eghtebas,
I am trying your second suggestion (as the employeestbl is heavily used so I don't want rely on max ID), however I got attached error when executing the following:
conn.Execute strSql, , adCmdText + adExecuteNoRecords

Open in new window


@aikimark,
Its an ADP Project linked to SQL 2005 BE.

@PatHartman,
do you have an example how to retrieve it from ADO?

Thanks,
Ben
Untitled.png
0
 
LVL 36

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40514046
I don't use ADO.  I use DAO.  Here's a snippet with the relevant pieces.  It inserts a main record, gets the ID and inserts a child record using the ID from the main record as the foreign key.
    CurDwgNum = StartDwgNum
    Set db = CurrentDb()
    Set td = db.TableDefs!tblDrawings
    Set td2 = db.TableDefs!tblRevisions
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set rs2 = td2.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Do Until CurDwgNum > EndDwgNum
        'add drawing
        rs.AddNew
        rs!JobID = Me.JobID
        rs!DrawingPfx = Me.txtBulkPfx
        rs!DrawingNum = CurDwgNum
        rs!DrawingSfx = Me.cboBulkSfx
        rs!FullDwgName = (Me.txtBulkPfx + "-") & CurDwgNum & ("-" + Me.cboBulkSfx)
        rs!DrawingTypeID = Me.cboBulkDrawingTypeID
        rs!Desc = Me.cboBulkSfx.Column(1)
        'HoldDrawingID = rs!DrawingID    'must be inside .AddNew for ACE otherwise it defaults to first ID in recordset
        rs.Update        
        
        HoldDrawingID = db.OpenRecordset("SELECT @@Identity")(0)    'must be outside .AddNew for SQL Server
        'HoldDrawingID = db.OpenRecordset("SELECT Scope_Identity()")(0) 'doesn't work

        'add revision
        rs2.AddNew
        rs2!DrawingID = HoldDrawingID
        rs2!RevNum = Me.txtBulkRev
        rs2!UpdateDT = Now()
        rs2!UpdateBy = Environ("UserName")
        rs2.Update
        CurDwgNum = CurDwgNum + 1
    Loop

Open in new window

0
 
LVL 45

Accepted Solution

by:
aikimark earned 300 total points
ID: 40514136
Try this:
strSql = "Insert into Employeestbl(column1,column2..)values(1,2..) ; Select @@Identity"
set rs = CurrentProject.Connection.Execute(strSql).NextRecordset
Debug.Print "New Record ID: ", rs(0)

Open in new window

Note: you will need to craft a valid Insert statement.
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40515492
@PatHartman,
I usually use DAO in MDB, just wonder how it works in ADP, assuming certain things are definitely different, for example set db=currentdb, and wondering what else do I have to modify in your example in order to apply to my ADP project?
Additionally I see you are having 2 options to retrieve the ID, one is the item in collection, which is interesting to me that it can retrieve it before the command was actually committed by rs.addnew, however about your second option HoldDrawingID = db.OpenRecordset("SELECT @@Identity")(0), how will a MDB know from which db are we talking about? unless this option was meant to use in ADP? or you modify an existing pass-through query with this sql?

@aikimark,
This latest of yours seems to work, just wonder how that @@Identity really works, is it related to this insert command or it takes the last value inserted at the database, regardless of user, table etc?
and one more thing, re the error msg, by checking err.description will I get the same error which is returned if I would execute the insert in SSMS?

Thanks,
Ben
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40515512
The code runs in an .accdb and the BE is currently .accdb.  I started changing some code to convert the BE to SQL Server.  As you can see, the code to get the ID was originally INSIDE the .AddNew/.Update.  That only works for Jet/ACE because Access has a close tie with it's native database engines.  Notice on a form bound to a Jet/ACE table that as soon as you enter the first character in the form, Access assigns the autonumber.  Same in code.  The .AddNew essentially dirties a record so the ID is available immediately.  Since Access is disconnected from SQL Server et al, the ID isn't available until after Access sends the update to the server-side database and receives a response.

If I had an ADO example, I would have posted it.  aikimark offered some syntax.  I don't know if it is correct but try it.  Just add your update in front of the @@Identity query.
0
 
LVL 4

Author Closing Comment

by:bfuchs
ID: 40517002
Thanks to all experts participating in this thread!
0
 
LVL 4

Author Comment

by:bfuchs
ID: 40524958
Hi Experts,
If you have a chance, please take a look the following
http://www.experts-exchange.com/Database/MS_Access/Q_28588813.html
Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Viewers will learn how the fundamental information of how to create a table.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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