CurrentDb.OpenRecordset Failing

[Moonshine]

Currentdb.OpenRecordset Failing

Can anyone help me, as tell me why this:

CurrentDb.OpenRecordset("SELECT [Manager Ref], Sum([Player Cost]) " & _
" FROM [tbl EnteredTeams] INNER JOIN [tbl Player] ON [tbl EnteredTeams].[Player Code] = [tbl Player].[Player Code] " & _
" GROUP BY [tbl EnteredTeams].[Manager Ref] WHERE [Manager Ref] = " & Me.Team)

Is failing?

Its basically selecting 11 records from [tbl EnteredTeams], all with the same [Manager Ref] (As A number 1, 2, 3 and so on) where the [Manager Ref] = the one you select from the combo box on the form. It Then Groups and Sums the [Player Cost] field, to give you a total team cost... Well its supposed to but when i click the button that runs this code, i get the attached error:

Been doing my head on for an hour now Im sure its something to do with the GROUP BY function, as that's where the error seems to point.

 

[Mile-O]

As the recordset you are trying to open is not dynamic in any way just create the query and open the query with the CurrentDb.OpenRecordset method.

 

[Moonshine]

Thanks for the Idea mile, but in a database with over 200 queries i really didnt want to do any more :/

Finally figured it out:

Set rstTeamCost = CurrentDb.OpenRecordset("SELECT [Manager Ref], Sum([Player Cost]) AS [Total Team Cost] " & _
" FROM [tbl EnteredTeams] " & _
" INNER JOIN [tbl Player] ON [tbl EnteredTeams].[Player Code] = [tbl Player].[Player Code] " & _
" WHERE [Manager Ref] = " & Me.Team & _
" AND [tbl EnteredTeams].Current = True" & _
" GROUP BY [tbl EnteredTeams].[Manager Ref]")

Works like a dream

 

[Mile-O]

in a database with over 200 queries

That's worrying.

 

[Moonshine]

It is indeed.

70meg database too

Im cutting it down tho, most of those queries are ones that can be done in code, which is what im doing now. I'll half that amount by the end of next week.

 

[Mile-O]

70meg database too

Im cutting it down tho, most of those queries are ones that can be done in code, which is what im doing now. I'll half that amount by the end of next week.

Ah, but the more you build in code when they are not dynamic then the more queries that are created at runtime which result in a bigger database.

As you create a query and then save it, its size adds to the overall database size. Once the query defiition is saved though, that's it; it has its space. When you create queries in code then they are used and thrown away by the code each time. This means that your database is going to grow each time a query is created in code.

Make sure you compact your database very often if you need to go down this route.

Is the problem with the volume of queries not that you have set explicit criteria within them?

 

[Moonshine]

Dont think i explianed myself well at all then :/

What im doing, is changing the queries like i had at the start of the message, into code. All of them are either select or Make table queries. So they can be done in code, without having to have a query there too.

Ok, i know the database is going to be large, but that doesnt reall matter. Access can cope with 2gb and im miles away from that.

So all im doing is copying the SQL from the Queries, and formatting it so it works in the VB.

 

[Mile-O]

Is the problem with the volume of queries not that you have set explicit criteria within them?

What about that question?

 

[Moonshine]

Not sure i understand what you mean...

Most of the queries were created long ago, before i knew how to use VB. Now i know what im doing there, im just simpley changing them over to the VB way of doing it.

Some work off date parameter's from forms, some dont...

 

[Mile-O]

By explicit criteria, I mean actually specifying something.

i.e.

one query for each day of the week

52 queries for each week of the year (and then change them manually as the next year starts)

select all records where employee = "John"