Execute SQL SELECT statement with VBA code

[Randy Wayne]

Below is a method with minimal code


Dim strSQL as String
Dim cnn As ADODB.Connection

Set cnn = CurrentProject.Connection
strSQL = "DELETE Customers.* FROM Customers"

Execute.sql


cnn = Nothing

 

[Guest]

Randy,

If I change your SQL statement to a SELECT statement, and
I change your line #5 to:

cnn.Execute strSQL

and I change your last line to:

Set cnn = nothing

I do not get an error, but there is no display of the
recordset. What I am looking for is a result similar to
DoCmd.OpenQuery "Customers" (assuming "Customers" is a
stored, SELECT query). The difference is, I want to
reference a SELECT SQL statement in code, not a stored
query.

If you discover something that works (Access 2000, 2002,
or 2003), please write back.

Thanks for your input.

 

[Michel Walsh]

Hi,




Dim rst As ADODB.Recordset
Set rst=xnn.Execute("SELECT * FROM somwhere WHERE somethingOccur")




with xnn an open ADO connection. The recordset is read only, forward only,
with that simple syntax, but that should cover 80% of all practical cases.
For the other 20%, try




Dim uvw As ADODB.Recordset ' add With Events, if required
Set uvw=New ADODB.Recordset
uvw.Open "SELECT ...", xnn.Connection, adOpenKeyset, adLockOptimistic,
Options:= adCmdText




Hoping it may help,
Vanderghast, Access MVP