How to display a SELECT query using VBA?

[izharmel]

Hi,

I'm trying to display data from a table using a query.
My code:

    Dim db As Database
    Dim rs As Recordset
    Dim qdf As QueryDef
    Dim strSql As String
    Set db = CurrentDb
 
    strSql = "SELECT T_MAIN.Name FROM T_MAIN;"
    Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
 
    With db
    Set qdf = .CreateQueryDef("tmpQry", strSql)
    DoCmd.OpenQuery "tmpQry", , acReadOnly
    .QueryDefs.Delete "tmpQry"
    End With
    db.Close
    qdf.Close
 

The code gets to "set qdf = ..." working just fine
but in the following row I get a message:
"Run-Time Error '7874':
Microsoft Office can't find the object 'tmpQry'."

Does anybody know what I'm doing wrong?

Thanks in advance
Izhar

[patjones]

Hi Izhar,

Everything that you're doing in the With...End With part of your code is not necessary. When line 8 is executed, the results of the SQL query get dumped into your recordset rs. From that point forward you can use the recordset to access the results of the SQL query.

Pat

[izharmel]

Hi Pat,
What I'm trying to do is display the query, the same way you would get it if you had done it manually (using a SELECT query with Access).

Thanks,
Izhar

[patjones]

I'm not really sure I know what you mean by that. Are you saying you want to display the results in a report? Or do you want the results to open up in table view?

Also, how are you calling this code...for instance is it being run after clicking a button in a form?

Pat

[izharmel]

yes, there's a button in a form. When the user presses the button, I want a table to be displayed (the result of the SELECT query).
Thanks,
Izhar

[izharmel]

Hi,
In the mean time I copied the data to a new table (using "SELECT ... INTO") and then
DoCmd.OpenTable "TableName", acViewNormal
is this the best way to do it?

Thanks in advance,
Izhar

[ahmedtharwat19]

@izharmel

    DoCmd.OpenQuery "tmpQry", , acReadOnly 
    .QueryDefs.Delete "tmpQry" 

You are using one of two things against one another for quite some.

how can use one command to open and second command to delete in same vba command

you should do the first and when done from it use the second in another vba command.

If I mistake any one correct me...

with my regards,
ahmed tharwat

[patjones]

@izharmel
If this is what you want and that code accomplishes it, then I don't see a problem with it.

Pat

[ahmedtharwat19]

zepphead80 @izharmel If this is what you want and that code accomplishes it, then I don't see a problem with it.

Pat

what i mean , we can open it and when close the query we cannot use the query again in another vba code.

i`m sorry for Lack of understanding of the last post of mine.

Best Regards ,

Ahmed Tharwat