DoCmd.RunSQL gives 2342 error

[Torch]

Dear all

This is probably a very simple question, but I do not seem to find a awnser
that suits me on the net.

I want to make a buttonclick perform a select qeury.
The code is something like thisPrivate Sub Command4_Click()
Dim SQL As String

SQL = "SELECT *" & _
"FROM dataset" & _
"WHERE dataset.machines = machineA"

DoCmd.RunSQL SQL

End Sub
I know this is wrong because the DoCmd.RunSQL method is only for action
queries. But how do I make an (ASCII) overviewof the selection I need from
the table dataset.
I need this data for further analysis.

 

[Stefan Hoffmann]

hi Torch,

PLEASE, don't use the greater sign '>' as a separator for code or
similar things. It makes your posting looking ugly and - in some way -
unreadable.
We're here in a newsgroup and the greater sign '>' is reserved to mark
quotes. These quotes are normally managed by your newsreader, not
manually.

http://en.wikipedia.org/wiki/Usenet
http://www.xs4all.nl/~hanb/documents/quotingguide.html
http://www.river.com/users/share/etiquette/edit.html

Thanks.

SQL = "SELECT *" & _
"FROM dataset" & _
"WHERE dataset.machines = machineA"
DoCmd.RunSQL SQL

You have to add spaces at the end of the partial strings, e.g.

SQL = "SELECT * " & _
"FROM dataset " & _
"WHERE dataset.machines = machineA"

But how do I make an (ASCII) overviewof the selection I need from
the table dataset.

Huh? What do you like to do?

Save the SQL as query and assign it as record source to a form or report.
Otherwise save it as query and display the query:

DoCmd.OpenQuery "queryName"

Create your query by code:

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Set db = CurrentDb
db.CreateQueryDef "queryName", SQL


mfG
--> stefan <--

 

[fredg]

Dear all

This is probably a very simple question, but I do not seem to find a awnser
that suits me on the net.

I want to make a buttonclick perform a select qeury.
The code is something like this
Private Sub Command4_Click()
Dim SQL As String

SQL = "SELECT *" & _
"FROM dataset" & _
"WHERE dataset.machines = machineA"

DoCmd.RunSQL SQL

End Sub

I know this is wrong because the DoCmd.RunSQL method is only for action
queries. But how do I make an (ASCII) overviewof the selection I need from
the table dataset.

Note: You need to include a space between words. As written your SQL
will look like this:
SQL = "SELECT *FROM datasetWHERE dataset.machines = machineA"

If we assume you have a valid SQL.....
If the SQL always is the same, simply create a query and then use:
Docmd.OpenQuery "YourQueryName"
to view the results.

If the SQL changes, you can do it this way.

You'll need to create a querydef the first time this is run.
Then change the query sql each time you run this.


Private Sub Command4_Click()
On Error GoTo Err_Handler
Dim Db As DAO.Database
Dim qdf As DAO.QueryDef
Set Db = CurrentDb
Dim strSQL As String

strSQL = "SELECT * FROM dataset WHERE dataset.machines = machineA"

Set qdf = Db.CreateQueryDef("YourQueryName", strSQL)
DoCmd.OpenQuery "YourQueryName"

Exit_Sub:
Set Db = Nothing
Exit Sub
Err_Handler:
If Err = 3012 Then
Set qdf = CurrentDb.QueryDefs("YourQueryName")
qdf.SQL = strSQL
Resume Next
Else
MsgBox "Error " & Err.Number & " " & Err.Description
End If
Resume Exit_Sub

End Sub

A good method to change the SQL is through a text control on a form.
Enter the SQL in an unbound text control. Then use:

strSQL = Me.ControlName

in the above code.

 

[Klatuu]

The proper way to do it is to create a form to display the data and use the
query as the form's row source.

Also, this line:
"WHERE dataset.machines = machineA"
is probably not correct. If your table is named dataset and your field
machines is a text field and machineA is a literal value, it needs to be:
"WHERE dataset.machines = 'machineA'"

If machineA is a variable then is needs to be:
"WHERE dataset.machines = '" & machineA & "'"

You SQL should be a stored query rather than one dynamically built. Stored
queries are faster because they have already been optimized and compiled by
Rushmore.