Learn how to a build a cloud-first strategyRegister Now

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

@@IDENTITY not working for one linked view but works for another linked view in Access 2010 with SQL Server 2008 backend

Hi.  I am baffled by why the @@Identity logic is not working for one of my tables.  It is actually a linked view to SQL Server.  So I created a small table and a view for that table, and when I test in a linked view for that table, it works fine.  So it seems there must be something wrong with the table that is causing @@Identity to not work.

Here's the test table that works:

CREATE TABLE [dbo].[TestTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FieldOne] [varchar](50) NULL,
	[FieldTwo] [smallint] NULL,
	[FieldThree] [float] NULL,
	[added] [datetime] NULL CONSTRAINT [DF_TestTable_added]  DEFAULT (getdate()),
 CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


Here's the real table that doesn't work:
CREATE TABLE [dbo].[Orders](
	[OrderID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerID] [int] NULL,
	[CustomerBillToID] [int] NULL CONSTRAINT [DF_Orders_CustomerBillToID]  DEFAULT ((0)),
	[CustomerShipToID] [int] NULL CONSTRAINT [DF_Orders_CustomerShipToID]  DEFAULT ((0)),
	[CustomerContactID] [int] NULL CONSTRAINT [DF_Orders_CustomerContactID]  DEFAULT ((0)),
	[OrderType] [varchar](15) NULL,
	[OrderPayType] [varchar](12) NULL,
	[PONumber] [varchar](40) NULL,
	[QuoteNumber] [varchar](25) NULL,
	[RFQNumber] [varchar](4) NULL,
	[RFQNumber2] [varchar](4) NULL,
	[RFQNumber3] [varchar](4) NULL,
	[RFQNumber4] [varchar](4) NULL,
	[RFQNumber5] [varchar](4) NULL,
	[SalesOrderDate] [datetime] NULL,
	[ShipmentDate] [datetime] NULL,
	[ShippingMethod] [varchar](100) NULL,
	[ShippingTerms] [varchar](100) NULL,
	[PaymentTerms] [varchar](100) NULL,
	[SalesRep] [varchar](50) NULL,
	[Legend] [varchar](750) NULL,
	[OrderSpecificBottlingInstructions] [varchar](1000) NULL,
	[OrderSpecificPackagingInstructions] [varchar](1000) NULL,
	[OrderSpecificDocumentationInstructions] [varchar](1000) NULL,
	[OrderSpecificShippingInstructions] [varchar](1000) NULL,
	[OIRules] [varchar](2500) NULL,
	[Comments] [varchar](2500) NULL,
	[InternalComments] [varchar](500) NULL,
	[DateAdded] [datetime] NULL CONSTRAINT [DF_Orders_DateAdded]  DEFAULT (getdate()),
	[AddedBy] [varchar](20) NULL CONSTRAINT [DF_Orders_AddedBy]  DEFAULT (ltrim(rtrim(substring(suser_sname(),(5),len(suser_sname()))))),
	[LastUpdated] [datetime] NULL CONSTRAINT [DF_Orders_LastUpdated]  DEFAULT (getdate()),
	[LastUpdatedBy] [varchar](20) NULL CONSTRAINT [DF_Orders_LastUpdatedBy]  DEFAULT (ltrim(rtrim(substring(suser_sname(),(5),len(suser_sname()))))),
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[OrderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window


I believe I created both views of these tables in the same way and gave myself the same permissions.

Here's my code to get the @@Identity:

Dim db As DAO.Database
Dim rs As DAO.Recordset
 
Set db = CurrentDb

sql = "INSERT INTO dbo_vwTestTable (FieldOne) VALUES ('Hello')"
db.Execute (sql)
MsgBox "vwTesttable: RecordsAffected: " & db.RecordsAffected
Set rs = db.OpenRecordset("SELECT @@identity AS NewID FROM dbo_vwTestTable")
MsgBox "vwTesttable: NewID: " & rs.Fields("NewID")

sql = "INSERT INTO dbo_vwOrders (customerid) VALUES (1)"
db.Execute (sql)
MsgBox "vwOrders: RecordsAffected: " & db.RecordsAffected
Set rs = db.OpenRecordset("SELECT @@identity AS NewID FROM dbo_vwOrders")
MsgBox "vwOrders: NewID: " & rs.Fields("NewID")

The program above gives me data in both cases, but the one for TestTable gives me the true @@identity value while it gives me the wrong number for Orders.  The new record for Orders is being created properly and, for example, used the OrderID 761 but the line @@identity gives me 528.  When I tested it before this record, it gave me 527 instead of 760.

Can anyone see what I did wrong?

Thanks,
Alexis
0
alexisbr
Asked:
alexisbr
  • 4
  • 2
1 Solution
 
alexisbrAuthor Commented:
I think I figured out what the problem is.  The Test table does not have any triggers on it but the Orders table does have triggers on it for audit trail purposes.  I'm trying SCOPE_IDENTITY() but I don't think it works with Access.  

If anyone has any ideas, please let me know.

Thanks,
Alexis
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
SCOPE_IDENTITY isn't supported in Access. You could create a Stored Procedure and move your logic to there, and then call the SP from Access like this:

Currentproject.Connection.Execute "EXEC YourStoredProcedure @Value1='Somevalue'"

Assuming YourStoredProcedure is expecting a Text value named "@Value1", this would then execute that procedure.
0
 
alexisbrAuthor Commented:
Thanks, LSMConsulting,
I am using a form to test the logic in a test table.  I created a stored procedure to return the scope_identity but it isn't working.  I am able to pass back another value from that table using the same method, but the scope_identity is giving a null error.  I've included the code and also the error message image.
Thanks,
Alexis
null error
Private Sub btnTestID_Click()

 Dim db As DAO.Database
 Dim rs As DAO.Recordset
 
 Set db = CurrentDb

 sql = "INSERT INTO dbo_vwTestTable (FieldOne) VALUES ('Hello')"
 db.Execute (sql)

 Call SP_Value
 Call spGetLastInsertedOrderID
End Sub

Public Function SP_Value()
   Dim mydb As Database
   Dim myq As QueryDef
   Dim myrs As Recordset

   Set mydb = CurrentDb()
   Set myq = mydb.CreateQueryDef("")

   myq2.Connect = "ODBC;Driver={SQL SERVER};Server=xxxx;Database=xxxx;Trusted_Connection=Yes"
   myq.sql = "TEST"
   Set myrs = myq.OpenRecordset()
   MsgBox myrs!x   'this works
End Function

Public Function spGetLastInsertedOrderID()
   Dim mydb2 As Database
   Dim myq2 As QueryDef
   Dim myrs2 As Recordset

   Set mydb2 = CurrentDb()
   Set myq2 = mydb2.CreateQueryDef("")

   myq2.Connect = "ODBC;Driver={SQL SERVER};Server=xxxx;Database=xxxx;Trusted_Connection=Yes"
   myq2.sql = "spGetLastInsertedOrderID"
   Set myrs2 = myq2.OpenRecordset()
   MsgBox myrs2!NewID  'this gives error about null value – see error image 
  End Function

SQL Server SPs:
ALTER PROCEDURE [dbo].[TEST]
   AS
BEGIN
 select x=FieldOne from TestTable where ID = 1
END

ALTER PROCEDURE [dbo].[spGetLastInsertedOrderID]
  AS
   BEGIN
	SELECT NewID=scope_identity() FROM TestTable
   END

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't believe you can do it like this - that is, insert the data in Access and then try to get the ID value in SQL. I've never done it that way, anyway, and I think you'd be better off moving the INSERT code to the Stored Procedure:

ALTER PROCEDURE [dbo].[spGetLastInsertedOrderID]
    @Val1 varchar(25),
    @ID    INT    OUTPUT
  AS
   BEGIN
      INSERT INTO SomeTable(Col1) VALUES(@Val1)
        SELECT @ID =scope_identity()
   END

Then run this, and capture the Output param "@ID"
0
 
alexisbrAuthor Commented:
Thanks for your help.  I am getting back to this today.  I understand what you are saying but I have to rework my code.  I am going to use stored procedures for the inserts instead of linked tables.  
Alexis
0
 
alexisbrAuthor Commented:
Thanks, LSMConsulting.  I haven't had time to complete these changes yet but I know this will resolve the problem.  

Alexis
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now