How can I write SQL in VBA?

[neillovell]

I usually use VIEW->Code to go to the script editor to type in VBA, but SQL doesn't have this option. How do people write queries using VBA?

 

[ladybyrd]

The easiest way is to create an Access query in design view, then select View > SQL View. You can then modify the SQL statement, or cut and paste it into the Visual Basic Editor if you want to use it there.

 

[GGleason]

Here is an example of using VBA to write SQL:
[tt]
strTableOrderGTYP = "tblExample"
strSQL = "SELECT q0.* FROM " + strTableOrderGTYP + " AS q0 ORDER BY q0.NDX;"

intMaxGTYP = DCount("[GTYP]", strTableOrderGTYP)

If intMaxGTYP > 0 Then
Set rstGTYP = dbs.OpenRecordset(strSQL)

For i = 1 To intMaxGTYP
strGTYP = rstGTYP.Fields("GTYP&quot
'…do stuff here
'----------
rstGTYP.MoveNext
Next i

rstGTYP.Close
Set rstGTYP = Nothing
End If
[/tt]
hth,
GGleason

 

[neillovell]

Yes but I was assuming that, like a form or whatever, you can do VIEW->Code and go straight to the form's VBA code. With SQL it seems like I have to hunt around for the query's VBA code in the editor.