ADODB Recordset Update

[CiberTHuG]

Sorry to cross-post, but after getting no traffic in the other forum, I thought I might get better response here.

I have the following bit of code.

Dim myConn as New ADODB.Connection
  Dim myRS as New ADODB.Recordset
 
  myConn.Open "myDSN"
 
  myRS.Open "SELECT * FROM myTable WHERE 0=1", myConn, adOpenDynamic, adLockBatchOptimistic
 
  myRS.AddNew
  myRS.Fields("Field1").Value = strFoo
  myRS.Fields("Field2").Value = strBar
  myRS.UpdateBatch
 
  myRS.Close
 
  myRS.Open "SELECT IndexField FROM myTable WHERE " & _
            "Field1 = '" & strFoo & "' AND " & _
            "Field2 = '" & strBar & "'", _
            myConn, adOpenDynamic, adLockBatchOptimistic
  intIndex = myRS.Fields("IndexField").Value
  myRS.Close

Is there a safe way (that will work with MS Access, MS SQL Server, and Oracle) that I can get the autonumber field (IndexField) without closing and reopening the recordset? Are there any other places that I can make this code safer, use more accepted conventions?

[iataman]

Basically I belive that recordset are for readinda data, not updating. So I can recomemend you to use code below

Dim strConnStr,strQry as string
Dim conConn as ADODB.Connection
Dim rsRecSet as ADODB.recordset
Dim intIndex as Long
 
' strConnStr = "Provider..." Changes according to your DB
conConn.open strConnStr
 
strQry = "INSERT INTO myTable (Field1, Field2) VALUES ('"&strFoo&"' , '"&strBar&"')"
conConn.Execute (strQry)
 
strQry="SELECT indexField FROM myTable WHERE field1='"&strFoo&"' AND field2 = '"&strBar&"'"
Set rsRecSet = conConn.execute(strQry)
intIndex=rsRecSet.Fields(0)
 
Set rsRecSet=Nothing
Set conConn=Nothing

I personally use this code. This is much more safer I belive because you always have the exact query in hand and sure about what will exactly happen.

Hope it helps.

Regards.

[CiberTHuG]

My main reason for doing the update via the Recordset and not an SQL insert statement is MS SQL Server. The DB will not process a string literal over 4000 characters in an SQL statement. ADO works around this.

Now maybe someone can explain this deal with VB's early and late binding. It is my understanding that if you don't declare certain objects with the "New" operator then you will have to "Set" them after the declare.

As to nitpicky things... I don't like the use of "rsRecSet.Fields(0)". If I change the SQL statement, I now have to change the index. And even though .Value is the default property, call it.

[CiberTHuG]

Anyone? I was hoping to avoid closing and querying.