Open a recordset JUST to add to it

[LeanneGodney]

Hi there,

With the ADO way of opening a recordset is there some way of opening the recordset but not pulling out the existing records? I want to open the recordset in order to add a new record to it, and don't want to waste time with making the call pull out all the underlying records.

I could possibly do SELECT TOP 1 in order to get only ONE record back, but surely there's a way to grab the shell of the object without the records?

Any help would be most appreciated.

:0)

Leanne

 

[RoyVidar]

Why not just do an execute on the connection?

[tt]dim strSql as string
strSql = "insert into mytable (field1, field2) " & _
"values (123, 'just testing')"
cn.execute strSql,,adexecutenorecords+adcmdtext[/tt]

- or use a stored procedure...

Roy-Vidar

 

[LeanneGodney]

Hi Roy,

Yes, I know about that way, but I need to do it with a Recordset.Addnew in order to grab the ID number from the new record created to use elsewhere... Then again, I remember somethign about IDENTITY or something that picks up the ID, doesn't it? How does that work?

Thanks for your response!

Leanne

 

[RoyVidar]

When working with SQL server, it is often recommended to use scope_identity in stead, in case you're having a trigger inserting record in another table. In such case, identity will return the identity of the other table, while scope_identity will return the last identity "in this scope".

[tt]dim rs as adodb.recordset
set rs = cn.execute("select scope_identity()",,adcmdtext)
' set rs = cn.execute("select @@identity",,adcmdtext)
debug.print rs.fields(0).value[/tt]

Roy-Vidar

 

[cmmrfrds]

You could use a query that brings back no records.

dim rs as adodb.recordset
rs.Open "Select * from yourtable where 1<>1",cn, adOpenStatic

rs.Addnew
.....
rs.Update