New record problem

  • Thread starter Thread starter Walter Levine
  • Start date Start date
W

Walter Levine

In an Access project (SQL Server) I am adding a new record to the table
using storedprocedures;

Application.CurrentProject.AccessConnection.Insert_Client_Record <parm
list>

Now I need the new record id: (only the last one works, but no triggers on
the table)
' strSQL = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] "
'strSQL = "select max(clientid) from tbl_Clients"
strSQL = "SELECT @@IDENTITY"
Set rs1 = New ADODB.Recordset
rs1.Open strSQL, Application.CurrentProject.Connection, adOpenKeyset,
adLockReadOnly
NewClientRecId = rs1.Fields(0).Value
rs1.Close
Set rs1 = Nothing
now add three more table inserts
Application.CurrentProject.AccessConnection.Insert_Status_Record
NewClientRecId, TempClient.Status (TempClient is a UDF)
Application.CurrentProject.AccessConnection.Insert_SW_Record
NewClientRecId, CLng(TempClient.sw)
Application.CurrentProject.AccessConnection.Insert_CheckList_Record
NewClientRecId

At this point, everything is ok, data is correctly added.
My problem is that now I need to sync up the form to display the new record
with the related table records

This is code from the combobox wizard, but it doesn't work. (no surprise,
it's DAO)
Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.Find "[CLIENTID] = " & str(NewClientRecId)
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
How can I sync up the form with the new recrord?
When I was using DoCmd.GoRecord,,,acNewRecord,it worked automatically
Thanks for any input
Walter
 
Hi Walter,

your code is not DAO but ADO, when using ADP projects the underlying
linking is via ADO, in MDB's it is DAO.

is it not posible to after the inserts to

Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.MoveLast
Me.Bookmark = rst.Bookmark
rst.Close

or build a sql statement only selecting the latest arrival making that
that recordsource for the form, with a refresh after.


Hope it helps

Regards

Alex
 
The SCOPE_IDENTITY() won't work here because it's not part of the batch that
has been called by your stored procedure. Contrary to the @@Identity
command, which is SQL-Server wide, the SCOPE_IDENTITY is closely related to
the current running context on the SQL-Server.

However, even if it seems to work, the "SELECT @@IDENTITY" is also an error
here because some other guys may do an insert on the SQL-Server between the
call to the Insert_Client_Record procedure and the time the SELECT
@@IDENTITY will execute on the SQL-Server. This will work 99.9% of the time
but may return you an invalid identity value for the 0.1% remaining.

The best way to do this is to learn how to use parameters with ADP, forget
about calling directly yours SP from the AccessConnection and ask your
stored procedure to make the SELECT @@IDENTITY or the SCOPE_IDENTITY() and
return it's value to you.

You can also try to use a open recordset to make the insert but contrary to
DAO and JET, you must make the update after the AddNew before trying to read
the identity value. It is also possible that you will have to resync your
recordset to the last inserted record after the update with something like:
rs.bookmark = rs.LastModified

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Walter Levine said:
In an Access project (SQL Server) I am adding a new record to the table
using storedprocedures;

Application.CurrentProject.AccessConnection.Insert_Client_Record <parm
list>

Now I need the new record id: (only the last one works, but no triggers on
the table)
' strSQL = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] "
'strSQL = "select max(clientid) from tbl_Clients"
strSQL = "SELECT @@IDENTITY"
Set rs1 = New ADODB.Recordset
rs1.Open strSQL, Application.CurrentProject.Connection,
adOpenKeyset,
adLockReadOnly
NewClientRecId = rs1.Fields(0).Value
rs1.Close
Set rs1 = Nothing
now add three more table inserts
Application.CurrentProject.AccessConnection.Insert_Status_Record
NewClientRecId, TempClient.Status (TempClient is a UDF)
Application.CurrentProject.AccessConnection.Insert_SW_Record
NewClientRecId, CLng(TempClient.sw)
Application.CurrentProject.AccessConnection.Insert_CheckList_Record
NewClientRecId

At this point, everything is ok, data is correctly added.
My problem is that now I need to sync up the form to display the new
record
with the related table records

This is code from the combobox wizard, but it doesn't work. (no surprise,
it's DAO)
Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.Find "[CLIENTID] = " & str(NewClientRecId)
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
How can I sync up the form with the new recrord?
When I was using DoCmd.GoRecord,,,acNewRecord,it worked automatically
Thanks for any input
Walter
Click to expand...
 
Thanks for your reply
I was trying to do what i think you suggested, that is, to grab the new id
value in the sp that adds the client record, and pass it back to the Access
caller as an out parameter. But I couldn't figure out how to do that (I'm
new to sql server)
I can get the value to print (SELECT SCOPE_IDENTITY()), what is the syntax
to assign it to a parameter? Like:
@OutVal = SELECT SCOPE_IDENTITY())
I know this seems pretty basic, but I can't seem to find it
Thanks for your help
Walter


Sylvain Lafontaine said:
The SCOPE_IDENTITY() won't work here because it's not part of the batch that
has been called by your stored procedure. Contrary to the @@Identity
command, which is SQL-Server wide, the SCOPE_IDENTITY is closely related to
the current running context on the SQL-Server.

However, even if it seems to work, the "SELECT @@IDENTITY" is also an error
here because some other guys may do an insert on the SQL-Server between the
call to the Insert_Client_Record procedure and the time the SELECT
@@IDENTITY will execute on the SQL-Server. This will work 99.9% of the time
but may return you an invalid identity value for the 0.1% remaining.

The best way to do this is to learn how to use parameters with ADP, forget
about calling directly yours SP from the AccessConnection and ask your
stored procedure to make the SELECT @@IDENTITY or the SCOPE_IDENTITY() and
return it's value to you.

You can also try to use a open recordset to make the insert but contrary to
DAO and JET, you must make the update after the AddNew before trying to read
the identity value. It is also possible that you will have to resync your
recordset to the last inserted record after the update with something like:
rs.bookmark = rs.LastModified

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Walter Levine said:
In an Access project (SQL Server) I am adding a new record to the table
using storedprocedures;

Application.CurrentProject.AccessConnection.Insert_Client_Record <parm
list>

Now I need the new record id: (only the last one works, but no triggers on
the table)
' strSQL = "SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY] "
'strSQL = "select max(clientid) from tbl_Clients"
strSQL = "SELECT @@IDENTITY"
Set rs1 = New ADODB.Recordset
rs1.Open strSQL, Application.CurrentProject.Connection,
adOpenKeyset,
adLockReadOnly
NewClientRecId = rs1.Fields(0).Value
rs1.Close
Set rs1 = Nothing
now add three more table inserts
Application.CurrentProject.AccessConnection.Insert_Status_Record
NewClientRecId, TempClient.Status (TempClient is a UDF)
Application.CurrentProject.AccessConnection.Insert_SW_Record
NewClientRecId, CLng(TempClient.sw)
Application.CurrentProject.AccessConnection.Insert_CheckList_Record
NewClientRecId

At this point, everything is ok, data is correctly added.
My problem is that now I need to sync up the form to display the new
record
with the related table records

This is code from the combobox wizard, but it doesn't work. (no surprise,
it's DAO)
Dim rst As ADODB.Recordset
Set rst = Me.RecordsetClone
rst.Find "[CLIENTID] = " & str(NewClientRecId)
If Not rst.EOF Then
Me.Bookmark = rst.Bookmark
End If
rst.Close
Set rst = Nothing
How can I sync up the form with the new recrord?
When I was using DoCmd.GoRecord,,,acNewRecord,it worked automatically
Thanks for any input
Walter
Click to expand...
Click to expand...
 
Hi Walter

Try

set @OutValue = (Select Scope_Identity())

to put the value into a variable/parameter

also try

select Scope_Identity()

as the last line in the stored procedure

hope it helps


Regards

Alex
 
Hello Alex,
Sorry to report, I still can't get it to work
This is my sp (simplified test)
Create PROCEDURE [dbo].[footest]
@char_data varchar(50),
@int_data int,
@new_id int out
as
insert into foo(char_data,int_data) values(@char_data,@int_data)
set @new_id=(select scope_identity()) <or> select Scope_Identity()
go
Here is the call:
Dim s As String
Dim i As Integer
Dim n As Long
Application.CurrentProject.AccessConnection.footest s, i, n
MsgBox n

The data fields are written correctly, but n comes back as zero.
What am i doing wrong?
Thanks for the help
Walter
 
I never call a stored procedure directly on the
CurrentProject.AccessConnection; I always use ADO objects, so I cannot help
you any further on this line if you want to use the AccessConnection
property.

But if you choose to go with ADO, then the ADO objects have full support for
IN and OUT parameters as well as for a Return Value. Both an OUT parameter
or a Return Value can be used to return the identity value out of the stored
procedure. You can also choose to use a recordset to return this value.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


Walter Levine said:
Hello Alex,
Sorry to report, I still can't get it to work
This is my sp (simplified test)
Create PROCEDURE [dbo].[footest]
@char_data varchar(50),
@int_data int,
@new_id int out
as
insert into foo(char_data,int_data) values(@char_data,@int_data)
set @new_id=(select scope_identity()) <or> select Scope_Identity()
go
Here is the call:
Dim s As String
Dim i As Integer
Dim n As Long
Application.CurrentProject.AccessConnection.footest s, i, n
MsgBox n

The data fields are written correctly, but n comes back as zero.
What am i doing wrong?
Thanks for the help
Walter


Alex said:
Hi Walter

Try

set @OutValue = (Select Scope_Identity())

to put the value into a variable/parameter

also try

select Scope_Identity()

as the last line in the stored procedure

hope it helps


Regards

Alex
Click to expand...
Click to expand...
 
Back
Top