easy way to execute SELECT sql? (via VBA)

[homer2002]

Hi all
This is probably very easy to do.

I need to be able to view the results of an SQL SELECT statement
like a built access query


i.e

sub MySub()
dim TempSQL as string
TempSQL = "SELECT * FROM tblTable WHERE name = 'ME'"
docmd.runSQL tempSQL
end sub

problem is, you cant use .runSQL with SELECT statements

does anyone have any solution for this

 

[Mile-O]

You need to build a QueryDef.

This is covered in Access Help files with an example.

If you still have problems, post back.

 

[AngelicGuardian]

try this:

Dim nameToFind As String
Dim db As Database
Dim rs As Recordset
Dim vSql As String

nameToFind = "ME"

Set db = CurrentDb
vSql="SELECT * FROM tblTable WHERE name = '"& nameToFind &"'"
Set rs = db.OpenRecordset(vsql, dbOpenDynaset, dbSeeChanges)
If rs.BOF And rs.EOF Then
rs.Close
MsgBox "name '"& nameToFind &"' not found", vbInformation, "Name not found"
Exit Sub
' rs![name] will equal nameToFind without quotes
' so do your stuff here
rs.Close
db.Close
End Sub


I modified the above to allow you to specify the name to find

 

[Mile-O]

I need to be able to view the results of an SQL SELECT statement like a built access query

Angelic Guardian's solution is no good in this instance as homer2000 wants to "view" the results of the SELECT query in much the same way as a stored querydef.

That is why you must create a querydef in order for this to be possible.

 

[AngelicGuardian]

Sorry I thought it would work since we use a very similar vbs to "view" items here at work

 

[Pat Hartman]

You really shouldn't be presenting users with naked queries. Use forms instead. If you want something that looks like a query, use a form in datasheet view.