CODEWITHSUNDEEP
vbams-access
Katana24
Katana24

Reputation: 8959

How to run a SQL select statement in VB

I have been looking around but can't seem to find out how to do this.

I'm trying to execute a SELECT sql statement in VB that takes in a parameter from a form. For example the user selects a From and a To date and the SQL should take what they selected.

I know that you cannot use the DoCmd.Execute method for SELECT SQL so I would need to open the record set - but how? This is what I have tried so far

Dim recordSet As DAO.recordSet
Dim SQL As String

SQL = "SELECT * FROM tblWebMeetingData"
Set recordSet = CurrentDb.OpenRecordset(SQL)

'More code in here

recordSet.Close

Set recordSet = Nothing

Because this executes without an error I assume it's actually getting the results - so is there a way to see what it is actually returning?

Thanks

Upvotes: 0

Views: 1658

Answers (2)

www
www

Reputation: 4391

Here you have come sample code about iterating trought RecordSet and using values from it( I hope it helps a bit):

Dim i As Integer
Do While Not rs.EOF
        Sheets("D_" & day).Cells(i, 1) = rs.Fields(0).Value
        Sheets("D_" & day).Cells(i, 2) = rs.Fields(1).Value
        rs.MoveNext
        i = i + 1
Loop

rs.Close

Upvotes: 1

Christoph Jüngling
Christoph Jüngling

Reputation: 1100

First: It's a good advice to rename the recordset to rs, for example, because "recordset" is a reserved name. This is misunderstandable.

This recordset contains the records you queried by your SQL statement. You may access those data by rs.fields("fieldname").value. Move to the next recordset with rs.movenext.

To incorporate the form's control value I use the way to build the full SQL statement prior to opening the recordset. Say the form is named "myform" and the control "mycontrol", you may write some kind of

SQL = "SELECT * FROM tblWebMeetingData WHERE myfield = " & forms!myform.mycontrol.value

Please be sure the form only contains valid values, because any wrong formatted value will directly lead to an SQL execution error.

I hope it was this, what you wanted.

Upvotes: 2

Related Questions