Simple Query

[manoj.mcans]

I want to run simple
Select * from Tablename using vba code not going in query design mode.

usually i use
first create simple query in query design mode with name abc
then in vba code

I have made the odbc connection in Ms-Acccess

Dim MyDB As DAO.Database
Dim qdef As DAO.QueryDef
Dim stDocName As String
Set MyDB = CurrentDb()
MyDB.QueryDefs.Delete "abc"
Set qdef = MyDB.CreateQueryDef("abc", "Select * from tablename")
stDocName = "abc"
DoCmd.OpenQuery stDocName, acNormal, acEdit

This code work fine but for this we have to create the query abc..but i didnot want to create

Please suggest me.

 

[namliam]

I dont understand your problem?? If you want to show the results of a query you have to have a 'container' to show it in. This is either using the query as recordsource in a form or having a query.

 

[manoj.mcans]

If we have action query
then we can directly run that query without going inot query design mode
like docmd.runSQL queryname

can we run simple select clause statement purely using vba instead of going into query design mode

 

[namliam]

You mean just open the query in memory... yes we can...
Dim rs as DAO.Recordset
Set rs = Currentdb.openrecordset("Select ....")

Now the rs contains the recordset and you can loop thru it....
i.e.

Do while not rs.eof
    msgbox "Do Something"
    rs.movenext
loop

Dont forget to close and cleanup these variables tho...
rs.close
set rs = nothing