SQL Query in VBA

[Sue]

I'm trying to run a query in VBA. Code as follows:

SQL = "SELECT Temp.Polisnommer " & _
"FROM (Temp LEFT JOIN LMS ON " & _
"Temp.Polisnommer = LMS.Polisnommer) " & _
"WHERE (((LMS.Polisnommer) Is Null));"


DoCmd.RunSQL SQL

When I step through, I get the following error message:


Error Message 2342

'A runSQL action requires an argument consisting of an SQL statement. '

The above SQL was tested in the Query SQL area and does work. But as soon as
I try to run it in the VBA shell, it doesn't. WHY?

Thanks

 

[Yvonne Anderson]

What it is this group, and why is it appearing on my computer?

Yvonne Michele Anderson
(e-mail address removed)

 

[Douglas J. Steele]

As the message says, DoCmd.RunSQL only works with Action queries (INSERT
INTO, UPDATE, DELETE).

What are you hoping to have happen? You could save the SQL as a query and
then use DoCmd.OpenQuery "NameOfQuery", but to be honest, that doesn't
strike me as a particularly friendly user interface.

 

[Alex Dybenko]

Hi,
using DoCmd.RunSQL you can run action queries, like insert, delete, update.
For select queries - you can save SQL as select query and open it using
docmd.openquery, or set it to recordsource of a form and open form

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Sue]

Hey

I have linked tables called Temp and LMS. The user imports data via a
button. If their is data missing in the primary table LMS that is in Temp, I
want to run the query to test. This concludes then with a subform that shows
the user which data is not in the primary detail and needs to be added. What
this query does in fact is to do a left join, and showing all data in Test
that is Null in LMS.

Does this make sense?

 

[Sue]

Sorry, forgot to add. Have run a previous select query and it works.

 

[Douglas J. Steele]

My point was showing the results of a query as a query (as opposed to as a
form, where you can control what's done with the data) isn't a very friendly
interface.

If that's what you really want, I told you how you can do it. My advice,
though, is to create a form and use that query as its recordsource.

 

[Sue]

Thank you

Hi,
using DoCmd.RunSQL you can run action queries, like insert, delete, update.
For select queries - you can save SQL as select query and open it using
docmd.openquery, or set it to recordsource of a form and open form

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Sue]

How do I not let the query show. The help function have different views that
you can see the data. I don't want my user to see the query.

 

[Sue]

Thank you. I've sorted it out by running a requery and making the subform not
visible until when I want the user to see the data.

Once again, thanks.