SpookiePower said:
As I have found out, it is not possible to run a SELECT
statement in vba using DoCmd.RunSql or DoCmd.RunQuery.
But how can I then run a SELECT statement in vba ?
If you simply want the query to open for the user to do something with, then
use:
DoCmd.OpenQuery "MyQuery"
If however you want to loop through all the records, then you would use a
recordset object like this:
Private Sub MySub()
On Error GoTo Err_Handler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT TOP 5 * FROM MyTable ORDER BY MyField"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)
While Not rst.EOF
MsgBox Nz(rst.Fields(0).Value,"")
rst.MoveNext
Wend
MsgBox "Done", vbInformation
Exit_Handler:
If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
If Not dbs Is Nothing Then
Set dbs = Nothing
End If
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Select
End Sub
