Return Primary Key Value after insert into

[Colin Coleman]

HI,
I have created an SQL to copy a record in a table to a new record...fine but how can i get to the primary key or new record that has just been created, as i want to return this value from the copy function..


Thanks

Colin

 

[KenHigg]

I'm thinking you can use a dmax()?

 

[Colin Coleman]

ihva a function that does the insert
Function CopyToArchive(SSql) As Integer
On Error GoTo Err_CopyToArchive
Dim StrStoreSQL As String
Dim adoCon As New ADODB.Connection
Dim adoRst As New ADODB.Recordset
Set adoCon = CurrentProject.Connection
StrStoreSQL = SSql
adoRst.Open StrStoreSQL, adoCon
CopyToArchive = 0
Exit_CopyToArchive:
Exit Function
Err_CopyToArchive:
CopyToArchive = -1
GoTo Exit_CopyToArchive
End Function

this works
but as i am worried that 2 or more users are going to post at the same time then the dmax function will be a problem.

in the above code would it be possible for me to use a property of the "adoRst " to ask what data was just created ?


Thanks

Colin

 

[KenHigg]

I seriously doubt a second user is going to squeeze in between two lines of code the first user is executing with a dmax()...

 

[Colin Coleman]

i have seen it before where the delay through the network (some stations are in Germany) and others in the UK, cause all sorts of problems in the real world, I wish i could go back to running on local 100Meg networks.....Drives me nuts !!

So is it possible for me to extract the record details from the Recordset ?

 

[georgedwilkinson]

Any particular reason you need a guaranteed sequential number? As you probably know, VB is not multi-threaded and there is no way to make a generated number thread safe. The best solution is using an autonumber in Access (or SQL Server) but there is absolutely no guarantee that the numbers won't skip (nor is there with any other method, though you'll see it more using Access' autonumbers).

 

[Colin Coleman]

Lost me there, I dont care so much the primary key given, wether it skips jumps or sings, i just wanna know what it used in the insert into sql record.

Please bear with me, im used to programming in Delphi against Firebird and SQL databases, and the recordset in my usual language, returns a pointer to the record created.

I just wondered how you guys do it in Access.


Cheers
Colin

 

[KenHigg]

I'm thinking if you execute a sql statement you have to go back and get the pk. However you could create a record using dao and populate the fields, do a save and the get the pk/autonumber... (I think )

 

[WayneRyan]

Colin,

Where is the table?

If it's in SQL Server, you can use the @@Identity value.

If it's not, then ADO WILL do this:

rst.AddNew
rst.Fields("Field1") = Something
rst.Fields("Field2") = SomethingElse
'
' In All of this region up until the .Update
' the AutoNumber field will have a valid value
'
rst.Update

It's more work than an:

Insert Into
Select *

but it's guaranteed to give you the right value.

Other note: even if it was an "audit trail" type of table, the PK with an
appropriate DateTime range would return the appropriate AutoNumber
for the transaction.

Need more info,
Wayne

 

[KenHigg]

Which is kinda what I was thinking -

 

[Colin Coleman]

Thats Great, gives me an idea what VB in Access can do, so im gonna sleep on it as it 5 mins to midnight here in the UK.

Thanks Very much as well

Colin