More elegant solution for pulling value from DB using recordset

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.

From stackoverflow

• 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
  

• 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
  

• 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.