Using a Design View Query in ADO?

[danny2000]

Hi

Is it possibly to use a query created in Design View in Access in ADO?
I'm beginning to wonder if it's possible.

I know there are several alternatives though I'm specifically trying to use a design view query in ADO.

By using the command object I have placed a reference to the query in the .commandText property though this gives me an error: 'Invalid SQL Statetement; expected Delete, Insert, Procedure, Select or Update.

Is there a way of execting a Design View query using ADO. I've placed the very simple code below along with the project attached. It's attached to an event handling procedure.

Private Sub Combo2_AfterUpdate()

Dim rst As Recordset
Dim cmd As ADODB.Command

Dim qrtest As String

qrtest = "MyQuery"


Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command

With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = qrtest
    .CommandType = adCmdText
End With

Set rst = cmd.Execute

Me.Text0 = rst.Fields(1)


End Sub

[pkstormy]

Is this what you're trying to do?

https://www.accessforums.net/code-re...mple-7571.html

If you want to show the results of a query within a subform on a mainform, basically:

1. Create a subform in your main form.
2. Set the sourceobject of the subform to: Query.MyQueryName (ie. instead of to another form name).

But maybe I've misinterpreted your question.

[danny2000]

Thanks for your response. I'm more interested in learning how to randomly reference a Query designed In Design View in ADO code. I know there are many alternatives and I also realise you can link a control to a query though there are occasions where I just want to utilize a query on some occasions.

There must be a way.

Thanks
Danny2000

[pkstormy]

Here's what I do to put a query into a recordset using ADO...

dim rs as adodb.recordset
set rs = new adodb.recordset
dim strSQL as string
strSQL = "Select * from MyQueryName"
rs.open strSQL, currentproject.connection, adopendynamic, adlockoptimistic 'updateable recordset
or
rs.open strSQL, currentproject.connection, adopenkeyset, adlockreadonly 'non-updateable recordset but can use rs.recordcount
if rs.eof and rs.bof then 'No records returned
msgbox "No Records returned"
rs.close
set rs = nothing
else
'to save rs!SomeField to a field on the form
me!MyFieldOnTheForm = rs!SomeField
'to update rs!SomeField2
rs!SomeField2 = "SomeValue"
rs.update
rs.close
set rs = nothing
end if

I'm guessing your code might work if you changed it to this

qrytest = "Select * from MyQuery"

I'm not sure what you mean though by 'randomly reference a query'.

[danny2000]

Thanks again for coming back. All I meant for randomly accessing a query was that if I needed to reused on in my code I could add it in. Either way, I shouldn't have put that part in as it's immaterial to the problem at hand.

Using strSQL = "Select * from MyQueryName" where the Query name is used instead of a table name doesn't work? Or if it does, I'm doing something wrong. I've reproduced the case as per your example and actually have to access the table directly. I thought a source couldn't utilize a query name though I may be wrong on that. Nothing wrong with that but I really want to know if it's possible to use a Design View Query in ADO code as I haven't seen an example yet.

The project you've provided the link to above doesn't once use a query name in the code using the method above though I don't doubt that it can be done.

Thanks again for your help on this.