Execute Select statement without creating query in VBA

[Ronald_yoh]

Hi There,

anyone knows how to execute the SQL (Select statement) directly from VBA without creating the query.

sample case:
dim sSQL as string

sSQL = "Select * from tblCustomer"


I need to execute the above recordset without creating a new query.

any thoughts anyone?

thanks
R

[Ronald_yoh]

sorry guys.. the "currentdb.openrecordset" will do the trick.
I thought it only worked for query that has been created in the database

**fool me** for posting the thread without testing it first.

thanks guys

[CreganTur]

If you are using ADO, then you can create very specific recordsets based off of SELECT statements that you supply as a part of the recordset's Source parameter.

In other words, you could create an ADO recordset with something like this:
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = CurrentProject.Connection
Set rs As New ADODB.Recordset

rs.Open "SELECT * FROM tblEmployees WHERE Country <> 'Spain'", conn

[Ronald_yoh]

i would've thought it wouldn't be effecient to access the local table by using ADO.

I've solved the issue anyway... thanks for your answer...

Ta

[CreganTur]

Why do you think ADO would be inefficient when working with the current database?

[Ronald_yoh]

ooo.. i was just thinking with ADO you need to specify the connection and build the connection first whereas I can simplify my code using "currentdb"..

[CreganTur]

ooo.. i was just thinking with ADO you need to specify the connection and build the connection first whereas I can simplify my code using "currentdb"..

Connecting to the current DB via ADO is very easy, as you've discovered.