Create SQL statement in VBA Access 2007

20 pts.
Tags:
Access VBA
Microsoft Access 2007
SQL
SQL statements
VBA
How can I create a select Group BY SQL statement inside a VBA module? I've ran actions queries and they seem to take an effect in my tables but I'm having a difficult time creating a Select statement. Please display a couple of samples

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DoneThat
    If I understand your question, by "action queries" you mean you successfully run SQL updates and deletes. These "action statements" do not require interaction with the application. An SQL Select statement must receive output FROM your database INTO a recordset. You then must unwind the data FROM the recordset INTO your interface (very often Excel), This is all done in VBA. I do not have a simple working example. The following is cut from a fairly large app but hopefully will get you going in the right direction.
                
        Dim colIdx As Long
        plngRecordSetColCount = 0
    
    '  Execute the SQL query
    '  Any select statement at all can be in pstrSQL
        cmdAS400Command.CommandText = pstrSQL
        On Error GoTo 0
        Set rsAS400RecordSet = cmdAS400Command.Execute()
    
    '  Clear all previous contents on the output sheet from the starting cell to the very end.
        Workbooks(pstrClientWB).Worksheets(pstrCLDataSheetName).Range(pstrCLStartingCell, "iv65536").Cells.ClearContents
      
    '  At this point we activate the "starting cell" and populate sheet
    '  with the SQL Record Set.
        Range(pstrCLStartingCell).Select
        
    '  get the all rows from the recordset
        Do While Not rsAS400RecordSet.EOF
            plngRecordSetColCount = rsAS400RecordSet.Fields.Count
            
            For colIdx = 0 To plngRecordSetColCount - 1
                ActiveCell.Value = rsAS400RecordSet.Fields(colIdx).Value
                ActiveCell.Offset(0, 1).Activate
            Next colIdx
            ' Move back to the beginning
            ActiveCell.Offset(1, -plngRecordSetColCount).Activate
            
            rsAS400RecordSet.MoveNext
    
        Loop
    
    
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following