More elegant solution for pulling value from DB using recordset

Question

Is there a better solution (using less code) for the following code snippet. It is something that I find myself doing a lot of in VB6 and was hoping to trim it down.

As I understand it Connection.Execute will not work

    SQL = "SELECT SomeID FROM TableA"
    RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
    SomeID = RecordSet.Fields(0).Value
    RecordSet.Close

    SQL = "SELECT AnotherID FROM TableB"
    RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
    AnotherID = RecordSet.Fields(0).Value
    RecordSet.Close

This isn't a functional issue just looking to see if there is a tidier way.

Answer 1

The default parameters for both RecordSet.Open() and Connection.Execute() are:

• adOpenForwardOnly
• adLockReadOnly

You use different settings, but there is no apparent reason not to go with the defaults in your case.

I don't know why you think that Connection.Execute() will not work, especially since you seem to have static SQL:

Function FetchOneField(Connection, SQL)
  With Connection.Execute(SQL)
    FetchOneField = .Fields(0).Value
    .Close
  End With
End Function

SomeID    = FetchOneField(Connection, "SELECT SomeID FROM TableA")
AnotherID = FetchOneField(Connection, "SELECT AnotherID FROM TableB")

' or, not expressed as a function (not less lines, but "tidier" nevertheless)'
With Connection.Execute("SELECT SomeID FROM TableA")
  SomeID = .Fields(0).Value
  .Close
End With

Answer 2

Assuming that you have 1 record per query you could do it this way

SQL = "SELECT TableA.SomeID, TableB.AnotherID FROM TableA, TableB"
RecordSet.Open SQL, Connection, adOpenStatic, adLockOptimistic, adCmdText
SomeID = RecordSet.Fields(0).Value
AnotherID = RecordSet.Fields(1).Value
RecordSet.Close

Answer 3

Connection.Execute should work. This works for me:

  Const Sql1 As String = "SELECT SomeID FROM TableA"
  SomeID = Connection.Execute(Sql1)(0).Value

  Const Sql2 As String = "SELECT AnotherID FROM TableB"
  AnotherID = Connection.Execute(Sql2)(0).Value

...though personally I'd encapsulate those Connection.Execute lines where I could be explicit about releasing connections from the connection pool etc.