By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,007 Members | 1,232 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,007 IT Pros & Developers. It's quick & easy.

How to display a SELECT query using VBA?

P: 27
Hi,

I'm trying to display data from a table using a query.
My code:
Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rs As Recordset
  3.     Dim qdf As QueryDef
  4.     Dim strSql As String
  5.     Set db = CurrentDb
  6.  
  7.     strSql = "SELECT T_MAIN.Name FROM T_MAIN;"
  8.     Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
  9.  
  10.     With db
  11.     Set qdf = .CreateQueryDef("tmpQry", strSql)
  12.     DoCmd.OpenQuery "tmpQry", , acReadOnly
  13.     .QueryDefs.Delete "tmpQry"
  14.     End With
  15.     db.Close
  16.     qdf.Close
  17.  
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
Jun 3 '10 #1
Share this Question
Share on Google+
8 Replies


patjones
Expert 100+
P: 931
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
Jun 3 '10 #2

P: 27
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
Jun 3 '10 #3

patjones
Expert 100+
P: 931
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
Jun 3 '10 #4

P: 27
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
Jun 6 '10 #5

P: 27
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
Jun 6 '10 #6

ahmedtharwat19
P: 55
@izharmel
Expand|Select|Wrap|Line Numbers
  1.     DoCmd.OpenQuery "tmpQry", , acReadOnly 
  2.     .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
Jun 6 '10 #7

patjones
Expert 100+
P: 931
@izharmel
If this is what you want and that code accomplishes it, then I don't see a problem with it.

Pat
Jun 6 '10 #8

ahmedtharwat19
P: 55
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
Jun 7 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.