Solved

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

Posted on 2014-12-21
14
120 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
  • 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 3

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 33

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
 
LVL 33

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 34

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

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 34

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 3

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 34

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 3

Author Closing Comment

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

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now