Get primary key value from append query

[Seth Schrock]

My database is linked through an ODBC driver to a SQL Server backend. I know that when using the recordset.addne w method, to get the auto numbered primary key value for the record just entered you have to set the bookmark to the last modified record and then go to that record and get the value from the primary key field. Due to having to encrypt a few fields, I have to use an Append query to insert the data (the query will encrypt it). Can I still use the method of bookmarking the last modified record to get the primary key value?

[Rabbit]

Since it's SQL Server, you can try a pass through query using this SQL:

SELECT @@IDENTITY

I don't know if it will work in Access but in SSMS it returns the most recently assigned primary key.

[zmbd]

Make sure it's a "pass thru" query as you've done with the others.

[Seth Schrock]

My apologies for getting back so late. I was gone over the weekend.

I will give that a try tomorrow. It does sound promising.

[Seth Schrock]

Well, I tried it and nothing is returned. I read the following on the MSDN website:

"The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server."

Does this mean that running it as a pass-through query won't work? I would have thought that since both my INSERT query and your @@IDENTITY query were pass-through queries that they both would have happened on the same server (not a remote server) and would thus work.

[TheSmileyCoder]

Are you maintaining and re-using the same connection between the append query and the query for the identity?

[Seth Schrock]

I'm running two separate querydefs. Is that two separate connections? Is it possible to combine the two queries into one querydef so that it would be the same connection (separating the queries using the word GO)?

[Seth Schrock]

Would it just be better to do a Recordset.AddNe w for all of the fields that aren't encrypted, get the PK field, and then run an update query to add the encrypted fields to the record I just created? This just seems like a bad idea and a waste of system resources, but I can't figure anything else out.

[Seth Schrock]

Well, I went ahead and just used the Recordset.AddNe w idea to create the record, get the value of the primary key field and then I do an update query to encrypt the last two fields. This works, but I would still like to be able to do this all in one step. At least I can move on for now and continue testing.

[Rabbit]

If you want to try it in one step, you could try using a stored procedure to do your insert and have it return the @@identity.

[Seth Schrock]

I'll have to look into how to do that. If I need help I'll post in the MS SQL Server forum. Thanks Rabbit.