?
Solved

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

Posted on 2014-12-21
14
Medium Priority
?
136 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 46

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 400 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 400 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 46

Expert Comment

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

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 46

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 38

Assisted Solution

by:PatHartman
PatHartman earned 400 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 46

Accepted Solution

by:
aikimark earned 1200 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 38

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 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