SQL Query and Access

[shane01]

Hi Guys,

I want to use VBA to execute a query and return the results just like a normal Access query (ie: in a datasheet type view). How can I do this?

Shane

[Shuja Ali]

Same way as you would do it in VB.

Create a connection to the database using ADODB and execute the query.

[sweet_dreams]

Search in google about ado. There are loads of code samples abd tutorials.

or

This should be helpful:
- http://msdn.microsoft.com/library/de...tlibraries.asp

hope this help,

regards,
sweet_dreams

[salvelinus]

DAO (fastest for Access only operations):
DoCmd.RunSql "SELECT * FROM Foo"

or if it's a saved query in Access

DoCmd.OpenQuery "qryFoo"

ADO is different, uses .Execute, etc.

[shane01]

DoCmd.RunSQL "SELECT * FROM mwo2005 WHERE Method='LIQUID'"

Gives error:

A RunSQL action requires an argument consisting of an SQL statement.

[shane01]

I have discovered that DoCmd.RunSQL cannot be select statements but havent found a way to extract information and display it in the same way as a saved query. Also, how do I reference a field value on a form?

Ie: frmMain.txtType.Text? for SELECT * FROM mwo2005 WHERE Method='" & ... & "'"

[RobDog888]

You can access control values like so.

SELECT * FROM mwo2005 WHERE Method='" & Forms![Form1].txtType.Value & "'"

Only these types of queries can be run with RunSQL...

Query type     SQL statement 
Action  
---------------------------
Append         INSERT INTO 
Delete         DELETE 
Make-table     SELECT...INTO 
Update         UPDATE 

Data-definition (SQL-specific)  
Create a table    CREATE TABLE 
Alter a table     ALTER TABLE 
Delete a table    DROP TABLE 
Create an index   CREATE INDEX 
Delete an index   DROP INDEX

To run a basic SELECT SQL statement you will need to use the .OpenQuery function of a saved query.

[salvelinus]

Duh! I actually knew that and just wasn't thinking. Sorry for the misinfo, shane.