SQL Query and Access

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

Same way as you would do it in VB.

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

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

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.

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

Gives error:

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

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='" & ... & "'"

You can access control values like so.

VB Code:

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

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

Code:

---

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.

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