Basics of VBA and SQL

  • Thread starter Thread starter Jonas
  • Start date Start date
J

Jonas

I am trying to use VBA and SQL but I can't seem to get it to work.
Below is some code I have found doesn't work. I looked all over the
internet for an answer and in a couple of books that I have been using.
The book presents the subject as though I should just be able to put
the SQL statements with some modifications to the right of DoCMD.RunSQL
and run the query from the VBA editor. Any suggestions that you could
give would be much appreciated. I'm going nuts.

Sub testme()
Dim strQry As String
strQry = "SELECT Contacts.First, Contacts.Last, Contacts.Title,
Contacts.Company FROM Contacts"
DoCmd.OpenQuery strQry

End Sub
 
Jonas said:
I am trying to use VBA and SQL but I can't seem to get it to work.
Below is some code I have found doesn't work. I looked all over the
internet for an answer and in a couple of books that I have been using.
The book presents the subject as though I should just be able to put
the SQL statements with some modifications to the right of DoCMD.RunSQL
and run the query from the VBA editor. Any suggestions that you could
give would be much appreciated. I'm going nuts.

Sub testme()
Dim strQry As String
strQry = "SELECT Contacts.First, Contacts.Last, Contacts.Title,
Contacts.Company FROM Contacts"
DoCmd.OpenQuery strQry
Click to expand...

DoCmd.OpenQuery is used to opened a saved query (one visible in the db window).
What you have is a string and OpenQuery does not work on a string (even one with
SQL in it).

There is no way to display a query datasheet without creating an actual Query
object. You can open a Recordset in code based on SQL strings and manipulate
the data in the Recordset, but the only way to SEE the data in a query is to
create a query first.
 
I am trying to use VBA and SQL but I can't seem to get it to work.
Below is some code I have found doesn't work. I looked all over the
internet for an answer and in a couple of books that I have been using.
The book presents the subject as though I should just be able to put
the SQL statements with some modifications to the right of DoCMD.RunSQL
and run the query from the VBA editor. Any suggestions that you could
give would be much appreciated. I'm going nuts.

Sub testme()
Dim strQry As String
strQry = "SELECT Contacts.First, Contacts.Last, Contacts.Title,
Contacts.Company FROM Contacts"
DoCmd.OpenQuery strQry

End Sub
Click to expand...

1) The DoCmd.OpenQuery method requires the NAME of a saved query, not
a SQL Statement.

2) Using a SQL statement in VBA would require the DoCmd.RunSQL strQry
method (or CurrentDb.Execute strQry, dbFailOnError).

3) In VBA, RunSQL and CurrentDb.Execute can only run an Action query,
i.e. Delete, Update, etc., not a Select query or SQL.

4) If you wish to run a Select SQL, create a query, name and save it,
then use the DoCmd.OpenQuery "QueryName" to run it.

5) or use createquerydef using VBA, and run it that way:

Dim qdf As DAO.QueryDef
qdf = CurrentDb.CreateQueryDef("NameOfQuery",strQRY)

Look it up in VBA help for more information.
 
Thanks for responding Fred. I looked at a different portion of the
book I was reading and found the answer.
 
Back
Top