Solved

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

Posted on 2014-12-21
14
133 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 35

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 35

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 35

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

860 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