Opening a Select query w/o storing a named query

[AdamFeerst]

What's the best (or another) way?
When I try below, I get error #3061, Too few parameters. Expected 1, on the line highlighted.

Dim rs As DAO.Recordset
Dim stSQL As String

stSQL = "SELECT qryRevTranBiller.BillerName, qryRevTranBiller.ClientID, qryRevTranBiller.Prod, qryRevTranBiller.AGTSTS, qryRevTranBiller.PRDSTS, qryRevTranBiller.LastUpd, " & _
"qryRevTranBiller.Rev0112 , qryRevTranBiller.Tran0112, qryRevTranBiller.RevYTD, qryRevTranBiller.TranYTD, qryRevTranBiller.Rev1, qryRevTranBiller.Tran1, qryRevTranBiller.Rev2, qryRevTranBiller.Tran2, " & _
"qryRevTranBiller.Rev3, qryRevTranBiller.Tran3, qryRevTranBiller.Rev4, qryRevTranBiller.Tran4, qryRevTranBiller.Rev5, qryRevTranBiller.Tran5, qryRevTranBiller.Rev6, qryRevTranBiller.Tran6, " & _
"qryRevTranBiller.Rev7, qryRevTranBiller.Tran7, qryRevTranBiller.Rev8, qryRevTranBiller.Tran8, qryRevTranBiller.Rev9, qryRevTranBiller.Tran9, qryRevTranBiller.Rev10, qryRevTranBiller.Tran10, " & _
"qryRevTranBiller.Rev11, qryRevTranBiller.Tran11, qryRevTranBiller.Rev12, qryRevTranBiller.Tran12, qryRevTranBiller.Rev1324, qryRevTranBiller.Tran1324, qryRevTranBiller.Rev13, qryRevTranBiller.Tran13, " & _
"qryRevTranBiller.Rev14, qryRevTranBiller.Tran14, qryRevTranBiller.Rev15, qryRevTranBiller.Tran15 , qryRevTranBiller.Rev16, qryRevTranBiller.Tran16, qryRevTranBiller.Rev17, qryRevTranBiller.Tran17, " & _
"qryRevTranBiller.Rev18, qryRevTranBiller.Tran18, qryRevTranBiller.Rev19 , qryRevTranBiller.Tran19, qryRevTranBiller.Rev20, qryRevTranBiller.Tran20, qryRevTranBiller.Rev21, qryRevTranBiller.Tran21, " & _
"qryRevTranBiller.Rev22, qryRevTranBiller.Tran22, qryRevTranBiller.Rev23, qryRevTranBiller.Tran23, qryRevTranBiller.Rev24, qryRevTranBiller.Tran24, qryRevTranBiller.Rev2536, qryRevTranBiller.Tran2536, " & _
"qryRevTranBiller.Rev25, qryRevTranBiller.Tran25, qryRevTranBiller.Rev26, qryRevTranBiller.Tran26, qryRevTranBiller.Rev27, qryRevTranBiller.Tran27, qryRevTranBiller.Rev28, qryRevTranBiller.Tran28, " & _
"qryRevTranBiller.Rev29, qryRevTranBiller.Tran29, qryRevTranBiller.Rev30, qryRevTranBiller.Tran30, qryRevTranBiller.Rev31, qryRevTranBiller.Tran31, qryRevTranBiller.Rev32, qryRevTranBiller.Tran32, " & _
"qryRevTranBiller.Rev33, qryRevTranBiller.Tran33, qryRevTranBiller.Rev34, qryRevTranBiller.Tran34, qryRevTranBiller.Rev35, qyRevTranBiller.Tran35 , qryRevTranBiller.Rev36, qryRevTranBiller.Tran36" & _
" FROM qryRevTranBiller " & _
"WHERE qryRevTranBiller.ClientID='" & Me.ClientID & "'"
Set rs = CurrentDb.OpenRecordset(stSQL)
Set rs = Nothing

 

[MarkK]

Are you aware that you can get all the fields from all the referenced tables if you put a "*" for your field list? Consider this, much simpler, SQL . . .

SELECT * FROM qryRevTranBiller WHERE ClientID = 1234

The error you are getting is because you are using a field name that is not in one of the tables in the query. So if you had this table . . .

tTable
TableID
Data
Created

This SQL would cause the same error . . .

SELECT TableID, Data, Created, FieldNameThatDoesntExist 
FROM tTable

Hope this helps,

 

[AdamFeerst]

I am aware. I'm not including all of the fields.

I haven't found a non-existing, misspelled field. I copied the sql from a working query, and copied it back to a new query to test, and it worked.

Could it be that the string length is too long?

My hope in doing this it to cut down on the number of named queries, especially those that have parameters based on fields on a form.

 

[jdraw]

This one is spelled incorrectly

qyRevTranBiller.Tran35

.

However, because you are dealing with only 1 table/query, I think you can delete the explicit reference to the query name and shorten your sql to:

stSQL = "SELECT BillerName, ClientID, Prod, AGTSTS, PRDSTS, LastUpd, " & _
"Rev0112 , Tran0112, RevYTD, TranYTD, Rev1, Tran1, Rev2, Tran2, " & _
"Rev3, Tran3, Rev4, Tran4, Rev5, Tran5, Rev6, Tran6, " & _
"Rev7, Tran7, Rev8, Tran8, Rev9, Tran9, Rev10, Tran10, " & _
"Rev11, Tran11, Rev12, Tran12, Rev1324, Tran1324, Rev13, Tran13, " & _
"Rev14, Tran14, Rev15, Tran15 , Rev16, Tran16, Rev17, Tran17, " & _
"Rev18, Tran18, Rev19 , Tran19, Rev20, Tran20, Rev21, Tran21, " & _
"Rev22, Tran22, Rev23, Tran23, Rev24, Tran24, Rev2536, Tran2536, " & _
"Rev25, Tran25, Rev26, Tran26, Rev27, Tran27, Rev28, Tran28, " & _
"Rev29, Tran29, Rev30, Tran30, Rev31, Tran31, Rev32, Tran32, " & _
"Rev33, Tran33, Rev34, Tran34, Rev35, Tran35 , Rev36, Tran36" & _
" FROM qryRevTranBiller " & _
"WHERE ClientID='" & Me.ClientID & "'"

 

[AdamFeerst]

I already found and fixed the extra space in Tran35 and still got that error message.

Any other ideas?

 

[jdraw]

What extra space? What I showed was a field name that was missing an "r" as in qryRevTranBiller

Did you try the alternate sql I showed?

In your code you have these adjacent lines

...
Set rs = CurrentDb.OpenRecordset(stSQL)
Set rs = Nothing
..

You aren't doing anything with rs. What was your intent?

 

[plog]

Hey, a fellow american. Time for a real football analogy:

Feel free to throw a hail mary on every down of your first possesion in the 1st quarter. But once it doesn't work, its time try and pick up some first downs and get some yardage.

Knock your select statement down to one field:

stSQL = "SELECT qryRevTranBiller.BillerName 
" FROM qryRevTranBiller " & _
"WHERE qryRevTranBiller.ClientID='" & Me.ClientID & "'"

Just get the offense moving. A touchdown that uses 1 play scores as much as a 15 play drive.

 

[AdamFeerst]

My intent is to open select queries without having to store it as a named query.
docmd.RunSQL only works with action queries.
docmd.OpenQuery only works with named queries.

 

[jdraw]

You were creating a recordset rs, but you have no code to process/read through it.

You can have a recordset based on an sql string. You open it, and move through it and doing some action/reporting/displaying etc as you go.

See this for more info / details/ samples.

Good luck.

 

[AdamFeerst]

Let's go back to my original question. What's the best way to open a select query for viewing without using a stored, named query? Related to that, is this something good to do, or should I just store the queries? My thought is that fewer stored queries means a smaller front end. Is that so? If so, is it a significant amount, or should I not bother?

 

[plog]

You put the query in a Module in VBA, you create a stored query---6 or half dozen of the other. You still have to put the SQL in the front end. What exactly do you think you will gain using one method over the other? What's a smaller front end get you? And how much smaller is one method over the other? Worst case, its a matter of a few kilobytes--which is nothing for a computer in 2015.

Is there a bigger picture objective to this, or are you simply trying to open a query and display data? Sounds like this is being overthought.

 

[AdamFeerst]

Overthinking? Me? I resemble that remark.
I try to learn the underlying reasoning and inner workings when I can so that I can apply concepts to a wide range of things rather than just solving the proximate issue.

I've heard it's best to minimize stored queries. What you're telling me is that it doesn't matter, at least not any more. If it isn't going to significantly reduce FE size or speed up processes, then I'll stick to stored queries. I'm lazy, and it's easier (and less error prone) to use gui than typing sql.

Thanks

 

[MarkK]

The bigger problem is the existence of fields named tran1, tran2, tran3, ..., tran36 AND rev1, rev2, rev3, ..., rev36 in ONE RECORD. I know these are fields in a query, but are there same-named fields in a table somewhere? And if so, that is what we should be talking about here.

Any efficiency loss/gain via storing/not storing queries pales is comparison to the efficiency loss you'll realize with a poorly designed table structure.

Just sayin'

 

[AdamFeerst]

Just sayin' I get it. Sometimes an efficient table structure isn't efficient for processing.

The 1-36 fields represents 3 years of monthly data. I bring the data into an efficient, vertical table (ID, date, rev, tran). Then, I translate this into a horizontal table (LastDate, ID, rev1, tran1, etc.), and a series of grouped summary tables. Putting them into tables allows the forms to load MUCH faster than having to run a series of sum queries. The basic data is only updated monthly, so it's not a big deal to create these tables, and to do the periodic compacting.

Speed kills.

 

[jdraw]

??? 3 yrs worth of data; 36 fields???
What do you intend to do after 4 yrs --now that you have 48 columns of data??

 

[MarkK]

Phew. I thought there was a table design DISASTER unfolding before our very eyes.

I don't care about storing vs. constructing queries from the standpoint of storage space or FE size because memory, RAM and Disk, is cheap cheap cheap. I make that call based on what is most efficient/meaningful/convenient/readable for each particular purpose. Like, sometimes a customer just wants to see data in a particular way, so I'll create and store a named query. But maybe a customer wants a complex search/sort interface to find data based on variable search parameters. In that case I'll construct the query SQL from scratch, sometimes for every keystroke.

I think you should let your purpose dictate your process without regard to memory usage.

 

[MarkK]

Here's an example I'm working on right now, actually. I'm working on a database for a cruise ship company, the problem is to find and compare voyages. They want to be able to select voyages by year, and I only want to show years for which there are voyages in the data, so I need a really specific query that I don't want to forever trip over in the query list, but that has to be there when this form opens. So I do this on that search form, which is the home for this process . . .

Private Const SQL_YEAR As String = _
    "SELECT DISTINCT [Year] FROM " & _
        "( " & _
        "SELECT DISTINCT Year(DepDate) As [Year] FROM tbl_VoyageData " & _
        "UNION ALL " & _
        "SELECT DISTINCT Year(EndDate) FROM tbl_VoyageData " & _
        ") " & _
    "WHERE [Year] Is Not Null;"

. . . and . . .

Private Sub Form_Load()
    Me.cboYear.RowSource = SQL_YEAR
[COLOR="Green"]    ' and so one[/COLOR]
End Sub

And I do this in code so I don't have to go dig it out of the control at design time if it needs service.

 

[AdamFeerst]

Mark: Phew indeed. You should've seen it before I took it over. I did a lot of normalization. Now I'm doing some unnormalizing for efficiency.

JDraw: They want to see 3 years worth of history, so that's what I'm giving them. Anything >36 months falls off.

 

[plog]

unnormalizing for efficiency

I've retyped this post 4 times trying to soften how I say this. This is the best I can come up with:

You don't have much credibility in the knowledge of efficiency department with what you were trying to do in the 1st page of this thread (Query Object vs. VBA Query into a Recordset). So you will have to pardon us if we don't believe you are being efficient with your unnormalization.

To that end--what happens each month? You have a huge process that shifts every columns values to adjacent column (Month1Value->Month2Value->Month3Value...)? That seems wildly inefficent.

 

[AdamFeerst]

First, it's hard to offend me. Be direct.

Second, I know what I know about Access. I know what I don't know. Lack of knowledge in one area doesn't necessarily imply lack of knowledge in another. I've used rs successfully to do actions in other areas. I was trying something new. It didn't work. I asked for help. I learned some things.

Yes the process takes some time, but it's only done once/month, can be done off hours, possibly on the be SQL server.

FWIW, I don't move columns over, I delete all the records, repopulate the ID field (captures new, drops old, and captures changes to prior month), then loop 36x to populate ea column with a RunSQL, changing the sql on ea loop. It works fine, and doesn't take all that long.

Yes we'll do a monthly compacting.

Finally, I don't need to justify myself here.