UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Retrieve Last Insert Id Vbscript/access Db.    
 
   
slacka
post Mar 19 2018, 06:19 PM
Post#1



Posts: 10
Joined: 2-December 17



Pulling my hair out trying to find the documentation for this.

This is how i do it in VBA/DAO within access:
CODE
dim dbs as dao.database: set dbs = currentdb
dbs.execute ("Insert ...")
id = dbs.OpenRecordset("SELECT @@IDENTITY")(0)


I'm using VBScript with ADODB but can't find out how to properly get the id.

ADODB's site says to use insert_id(). All the other things i can find are talking about ASP.

Here's how i add things in VBScript.
CODE
dim dbs
set dbs = CreateObject("ADODB.Connection")
dbs.open "connection string here"
dbs.execute ("INSERT ...")
id = ???

'i attempted this to no avail. Maybe it's an asp thing.
'set arr = dbs.execute("Insert ...; select @@identity;").nextrecordset
'id = arr(0)
Go to the top of the page
 
cheekybuddha
post Mar 19 2018, 06:32 PM
Post#2


UtterAccess VIP
Posts: 9,668
Joined: 6-December 03
From: Telegraph Hill


Hi,

It's not too different from the DAO code:
CODE
dim dbsm, id
set dbs = CreateObject("ADODB.Connection")
dbs.open "connection string here"
dbs.execute ("INSERT ...")
With dbs.Execute("SELECT @@IDENTITY;")
  id = .Fields(0)
  .Close
End With


hth,

d

[EDIT: Missed a comma in variable declaration]

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 19 2018, 06:35 PM
Post#3


UtterAccess VIP
Posts: 9,668
Joined: 6-December 03
From: Telegraph Hill


More explanantion:

In ADO the connection object method .Execute can both carry out action statements (INSERT, UPDATE, DELETE) and also open recordsets (SELECT ...) - but the recordsets open using that method will be read-only and forward-only (ie you can't use .MovePrevious)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
slacka
post Mar 20 2018, 12:03 PM
Post#4



Posts: 10
Joined: 2-December 17



beauty. Thanks for solution and follow up explanation.
Go to the top of the page
 
cheekybuddha
post Mar 20 2018, 01:18 PM
Post#5


UtterAccess VIP
Posts: 9,668
Joined: 6-December 03
From: Telegraph Hill


yw.gif

Further extra info:

You could have done it almost identically to your DAO code too:
CODE
dim dbsm, id
set dbs = CreateObject("ADODB.Connection")
dbs.open "connection string here"
dbs.execute ("INSERT ...")
id = dbs.Execute("SELECT @@IDENTITY;")(0)


However, with this method/syntax (ie doing it all in one line) you lose the ability to close the opened recordset.

This *might* lead to problems with 'Out of memory' errors or 'Too many tables opened' errors if you perform lots of INSERTs this way.

The VBA/VBS gaarbage collector should deal with removing the objects from memory when they go out of scope, but you have no control.

It often happened in older versions of Access that DAO recordsets which were not explicitly closed could leave orphan pointers to the recordset in memory (memory leak).

I *think* ADO recordsets were better at closing their internal references when they went out of scope (but I could be making that up!)

The situation may have been improved over the years, but I don't know for sure.

So I would advise also changing your VBA/DAO code within Access to:
CODE
dim dbs as dao.database, id As long
set dbs = currentdb
With dbs
  .execute ("Insert ...")
  With dbs.OpenRecordset("SELECT @@IDENTITY")
    id = .Fields(0)
    .Close
  End With
End With
Set dbs = Nothing


A little more code, but slightly safer!

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
slacka
post Mar 20 2018, 03:46 PM
Post#6



Posts: 10
Joined: 2-December 17



That might actually explain some things in other parts of my code. Thanks. I have been learning VBA/vbscript as i need it, so i'm missing some finer points that are pretty important. haha.
Go to the top of the page
 
cheekybuddha
post Mar 20 2018, 04:26 PM
Post#7


UtterAccess VIP
Posts: 9,668
Joined: 6-December 03
From: Telegraph Hill


>> so i'm missing some finer points that are pretty important. haha. <<

That's the great thing about this forum!

I don't think anyone here knows all the finer points - but there is always someone around who knows some of the one's you don't, and someone else who knows the ones that they don't!

Altogether we've got them all pretty much covered! uarulez2.gif

Good luck with your project! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd April 2018 - 06:23 AM