Access 2007 can lose the CurrentDb: see http://support.microsoft.com/kb/167173, so in the event of getting "Object Invalid or no longer set" with the examples, use:
Dim db as Database
Dim rs As DAO.Recordset
Set db = CurrentDB
Set rs = db.OpenRecordset("SELECT * FROM myTable")
Here is another way to use SQL SELECT
statement in VBA:
sSQL = "SELECT Variable FROM GroupTable WHERE VariableCode = '" & Me.comboBox & "'"
Set rs = CurrentDb.OpenRecordset(sSQL)
On Error GoTo resultsetError
dbValue = rs!Variable
MsgBox dbValue, vbOKOnly, "RS VALUE"
resultsetError:
MsgBox "Error Retrieving value from database",VbOkOnly,"Database Error"
Source: Stackoverflow.com