running plain SQL trough VBA?

[bensmeets]

I was wondering if and if so, how it is possile to make VBA execute an SQL command directly. I am used to php, where you can just say:

sql = "SELECT bla FROM bla WHERE bla" etc
and then execute that SQL.

Since i am running in to an problem which i do not have the access knowledge of fixing the access-way, i was wondering if i could do it directly in sql. Tnx in advance.

 

[Mile-O]

DoCmd.RunSQL sql

 

[ray705]

You can run SQL with limitations.

MYdb.Execute "UPDATE T SET T.SAFETY = 02 WHERE (((T.SAFETY)=""3 Point Belt""));"

From Microsoft help:
“The Execute method is valid only for action queries. If you use Execute with another type of query, an error occurs. Because an action query doesn't return any records, Execute doesn't return a Recordset.”

For a select query, you have to loop thru the recordset yourself.
Here is some code I have used.

Set MyDB = CurrentDb
' Create a temporary QueryDef object to retrieve data
Set qdfCycle = MyDB.CreateQueryDef("")
With qdfCycle
    .SQL = "SELECT * FROM Clients WHERE …;" 
    Set MyTable2 = .OpenRecordset()
    MyTable2.MoveFirst
    While Not MyTable2.EOF
       Me![ubClient] = MyTable2("ClientName")
       Me![ubClientNr] = MyTable2("Number")
       Me![ubFullAddress] = MyTable2("ClientAddr")
       MyTable2.MoveNext
       Wend
    MyTable2.Close

 

[Shadez]

You dont have to use DAO for sql u can use ADO (My Favorite)

to execute Sql use

Currentproject.connection.execute sql[,Rows]

ie,

currentproject.connection.execute "delete * from MyTable"


select queries can be done like this


Set Rs = new adodb.recordset

rs.open sql = "Select * from sometable",Currentproject.connection

Do while not rs.eof
debug.? rs!SomeFieldName
'OR
debug.? rs("SomeOtherFieldName")
Rs.movenext
loop

'remember to close all ur connections it eats up Access Memory
rs.close

ShadeZ