Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

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

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
bfuchs
Asked:
bfuchs
  • 5
  • 4
  • 3
  • +1
4 Solutions
 
aikimarkCommented:
An error will be raised if it doesn't work.
0
 
bfuchsAuthor Commented:
Hi,
How can I get the error msg returned by SQL within my Access App?
Thanks,
Ben
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
aikimarkCommented:
Look at both the Err.Number and the Errors collection.
0
 
PatHartmanCommented:
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
 
aikimarkCommented:
Is Employeestbl an attached table?
0
 
bfuchsAuthor Commented:
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
 
PatHartmanCommented:
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
 
aikimarkCommented:
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
 
bfuchsAuthor Commented:
@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
 
PatHartmanCommented:
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
 
bfuchsAuthor Commented:
Thanks to all experts participating in this thread!
0
 
bfuchsAuthor Commented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now