Access 97 SQL Character Limit

[Joe]

I have run into a problem when I add an additional Query
with its associated field to the Record Source of a form.
I obtain an error message "The string returned by the
builder was too long. The result will be truncated."

The SQL statement is then truncated to exactly 2048
characters. This seems odd in that Access Query
specifications says that the number of characters in an
SQL statement is approximately 64K.

Short of renaming the Queries (and trying to find all the
places it is referenced without breaking
anything ......sure)is there some other technique for
increasing the SQL statement size?? Or am I doing
something wrong??

Help is always appreciated.

Joe

 

[MacDermott]

In my years of programming, I've never needed a query remotely near that
long -
perhaps there's another way to accomplish your aim?
More detail would be welcome.

Should you, however, find yourself truly needing to find and replace all
references to a query name, I'd recommend the third-party product Speed
Ferret.

HTH
- Turtle

 

[Joe]

Thanks for the input. It appears as if Speed Ferret will
accomplish what I need to do in a less time consuming
manner, however at $190 it is too cost prohibitive.

As far as "detail" is concerned, let me first state that I
have named my Queries in a descriptive manner so that I
can go back to them later without a lot of head scratching
later on. The problamatic subform Record Source currently
contains three tables and six queries based on
computations from data contained in other subforms
(hopefully with more to come). With each Query name being
up to 40 characters in length, it quickly adds up.

My "aim" for the database is to track my children's school
grades based on the progress reports that they get
periodically. The reports that they get from their
teachers contain grade information on their individual
class assignments summed up to an overall class grade but
lack the "weighting" information of each assignment.

Perhaps I still have the "Excel" number crunching
mentality with all the queries.

The database that I have created is based on the "Student
and Classes" database provided with Access 97. Lacking the
programming efficiency that will come in time, I used the
logic of the sample database and built upon that.

The "limited" SQL stement is as follows;

SELECT Assignments.*, [SubAssignments MaxPoint
Query].AssignmentMaximumPoints, [SubAssignments MaxPoint
Query].StudentID, [SubAssignments Missing
Query].MissingFlag, [SubAssignments Score
Query].AssignmentScore, [Assignment Grade Calculation
Query].AssignmentGrade, [Assignments % of Class Grade
Earned Query].[%ofClassGradeEarned], [Assignments Max
Class Grade Query].AssignmentMaxClassGrade FROM
(((([Students And Classes] INNER JOIN ((Assignments INNER
JOIN [Assignment Grade Calculation Query] ON
Assignments.AssignmentID = [Assignment Grade Calculation
Query].AssignmentID) INNER JOIN (Classes INNER JOIN
[SubAssignments MaxPoint Query] ON Classes.ClassID =
[SubAssignments MaxPoint Query].ClassID) ON
(Classes.ClassID = Assignments.ClassID) AND
(Assignments.AssignmentID = [SubAssignments MaxPoint
Query].AssignmentID)) ON (Classes.ClassID = [Students And
Classes].ClassID) AND ([Students And
Classes].StudentClassID = [SubAssignments MaxPoint
Query].StudentClassID)) INNER JOIN [SubAssignments Score
Query] ON ([Assignment Grade Calculation Query].StudentID
= [SubAssignments Score Query].StudentID) AND
([SubAssignments MaxPoint Query].StudentID =
[SubAssignments Score Query].StudentID) AND
(Assignments.AssignmentID = [SubAssignments Score
Query].AssignmentID) AND ([Students And
Classes].StudentClassID = [SubAssignments Score
Query].StudentClassID)) INNER JOIN [Assignments % of Class
Grade Earned Query] ON (Assignments.AssignmentID =
[Assignments % of Class Grade Earned Query].AssignmentID)
AND ([SubAssignments Score Query].StudentID = [Assignments
% of Class Grade Earned Query].StudentID)) INNER JOIN
[Assignments Max Class Grade Query] ON ([Assignments % of
Class Grade Earned Query].StudentID = [Assignments Max
Class Grade Query].StudentID) AND
(Assignments.AssignmentID = [Assignments Max Class Grade
Query].AssignmentID)) INNER JOIN [SubAssignments Missing
Query] ON (Assignments.AssignmentID = [SubAssignments
Missing Query].AssignmentID) AND ([Assignments Max Class
Grade Query].StudentID = [SubAssignments Missing Query].S

Pretty long isnt it?

So the question remains.......Is the SQL statement limited
to 2048 characters as opposed to the 64K advertised?

Joe

 

[Douglas J. Steele]

Use the AS keyword to create short aliases for your tables in the SQL, then
use the aliases rather than the table names in the SELECT statement.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the input. It appears as if Speed Ferret will
accomplish what I need to do in a less time consuming
manner, however at $190 it is too cost prohibitive.

As far as "detail" is concerned, let me first state that I
have named my Queries in a descriptive manner so that I
can go back to them later without a lot of head scratching
later on. The problamatic subform Record Source currently
contains three tables and six queries based on
computations from data contained in other subforms
(hopefully with more to come). With each Query name being
up to 40 characters in length, it quickly adds up.

My "aim" for the database is to track my children's school
grades based on the progress reports that they get
periodically. The reports that they get from their
teachers contain grade information on their individual
class assignments summed up to an overall class grade but
lack the "weighting" information of each assignment.

Perhaps I still have the "Excel" number crunching
mentality with all the queries.

The database that I have created is based on the "Student
and Classes" database provided with Access 97. Lacking the
programming efficiency that will come in time, I used the
logic of the sample database and built upon that.

The "limited" SQL stement is as follows;

SELECT Assignments.*, [SubAssignments MaxPoint
Query].AssignmentMaximumPoints, [SubAssignments MaxPoint
Query].StudentID, [SubAssignments Missing
Query].MissingFlag, [SubAssignments Score
Query].AssignmentScore, [Assignment Grade Calculation
Query].AssignmentGrade, [Assignments % of Class Grade
Earned Query].[%ofClassGradeEarned], [Assignments Max
Class Grade Query].AssignmentMaxClassGrade FROM
(((([Students And Classes] INNER JOIN ((Assignments INNER
JOIN [Assignment Grade Calculation Query] ON
Assignments.AssignmentID = [Assignment Grade Calculation
Query].AssignmentID) INNER JOIN (Classes INNER JOIN
[SubAssignments MaxPoint Query] ON Classes.ClassID =
[SubAssignments MaxPoint Query].ClassID) ON
(Classes.ClassID = Assignments.ClassID) AND
(Assignments.AssignmentID = [SubAssignments MaxPoint
Query].AssignmentID)) ON (Classes.ClassID = [Students And
Classes].ClassID) AND ([Students And
Classes].StudentClassID = [SubAssignments MaxPoint
Query].StudentClassID)) INNER JOIN [SubAssignments Score
Query] ON ([Assignment Grade Calculation Query].StudentID
= [SubAssignments Score Query].StudentID) AND
([SubAssignments MaxPoint Query].StudentID =
[SubAssignments Score Query].StudentID) AND
(Assignments.AssignmentID = [SubAssignments Score
Query].AssignmentID) AND ([Students And
Classes].StudentClassID = [SubAssignments Score
Query].StudentClassID)) INNER JOIN [Assignments % of Class
Grade Earned Query] ON (Assignments.AssignmentID =
[Assignments % of Class Grade Earned Query].AssignmentID)
AND ([SubAssignments Score Query].StudentID = [Assignments
% of Class Grade Earned Query].StudentID)) INNER JOIN
[Assignments Max Class Grade Query] ON ([Assignments % of
Class Grade Earned Query].StudentID = [Assignments Max
Class Grade Query].StudentID) AND
(Assignments.AssignmentID = [Assignments Max Class Grade
Query].AssignmentID)) INNER JOIN [SubAssignments Missing
Query] ON (Assignments.AssignmentID = [SubAssignments
Missing Query].AssignmentID) AND ([Assignments Max Class
Grade Query].StudentID = [SubAssignments Missing Query].S

Pretty long isnt it?

So the question remains.......Is the SQL statement limited
to 2048 characters as opposed to the 64K advertised?

Joe

-----Original Message-----
In my years of programming, I've never needed a query remotely near that
long -
perhaps there's another way to accomplish your aim?
More detail would be welcome.

Should you, however, find yourself truly needing to find and replace all
references to a query name, I'd recommend the third-party product Speed
Ferret.

HTH
- Turtle




.

 

[Joe]

Thanks,

I finally found the path to the Query/Table Alias
name.......talk about being buried. Alas I read your
response too late and had already embarked on the renaming
campaign. Albeit a methodical approach it worked after
some tweaking.

But the question Still remains..........Is the SQL
statement limited to 2 to the 11th power characters?

-----Original Message-----
Use the AS keyword to create short aliases for your tables in the SQL, then
use the aliases rather than the table names in the SELECT statement.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the input. It appears as if Speed Ferret will
accomplish what I need to do in a less time consuming
manner, however at $190 it is too cost prohibitive.

As far as "detail" is concerned, let me first state that I
have named my Queries in a descriptive manner so that I
can go back to them later without a lot of head scratching
later on. The problamatic subform Record Source currently
contains three tables and six queries based on
computations from data contained in other subforms
(hopefully with more to come). With each Query name being
up to 40 characters in length, it quickly adds up.

My "aim" for the database is to track my children's school
grades based on the progress reports that they get
periodically. The reports that they get from their
teachers contain grade information on their individual
class assignments summed up to an overall class grade but
lack the "weighting" information of each assignment.

Perhaps I still have the "Excel" number crunching
mentality with all the queries.

The database that I have created is based on the "Student
and Classes" database provided with Access 97. Lacking the
programming efficiency that will come in time, I used the
logic of the sample database and built upon that.

The "limited" SQL stement is as follows;

SELECT Assignments.*, [SubAssignments MaxPoint
Query].AssignmentMaximumPoints, [SubAssignments MaxPoint
Query].StudentID, [SubAssignments Missing
Query].MissingFlag, [SubAssignments Score
Query].AssignmentScore, [Assignment Grade Calculation
Query].AssignmentGrade, [Assignments % of Class Grade
Earned Query].[%ofClassGradeEarned], [Assignments Max
Class Grade Query].AssignmentMaxClassGrade FROM
(((([Students And Classes] INNER JOIN ((Assignments INNER
JOIN [Assignment Grade Calculation Query] ON
Assignments.AssignmentID = [Assignment Grade Calculation
Query].AssignmentID) INNER JOIN (Classes INNER JOIN
[SubAssignments MaxPoint Query] ON Classes.ClassID =
[SubAssignments MaxPoint Query].ClassID) ON
(Classes.ClassID = Assignments.ClassID) AND
(Assignments.AssignmentID = [SubAssignments MaxPoint
Query].AssignmentID)) ON (Classes.ClassID = [Students And
Classes].ClassID) AND ([Students And
Classes].StudentClassID = [SubAssignments MaxPoint
Query].StudentClassID)) INNER JOIN [SubAssignments Score
Query] ON ([Assignment Grade Calculation Query].StudentID
= [SubAssignments Score Query].StudentID) AND
([SubAssignments MaxPoint Query].StudentID =
[SubAssignments Score Query].StudentID) AND
(Assignments.AssignmentID = [SubAssignments Score
Query].AssignmentID) AND ([Students And
Classes].StudentClassID = [SubAssignments Score
Query].StudentClassID)) INNER JOIN [Assignments % of Class
Grade Earned Query] ON (Assignments.AssignmentID =
[Assignments % of Class Grade Earned Query].AssignmentID)
AND ([SubAssignments Score Query].StudentID = [Assignments
% of Class Grade Earned Query].StudentID)) INNER JOIN
[Assignments Max Class Grade Query] ON ([Assignments % of
Class Grade Earned Query].StudentID = [Assignments Max
Class Grade Query].StudentID) AND
(Assignments.AssignmentID = [Assignments Max Class Grade
Query].AssignmentID)) INNER JOIN [SubAssignments Missing
Query] ON (Assignments.AssignmentID = [SubAssignments
Missing Query].AssignmentID) AND ([Assignments Max Class
Grade Query].StudentID = [SubAssignments Missing Query].S

Pretty long isnt it?

So the question remains.......Is the SQL statement limited
to 2048 characters as opposed to the 64K advertised?

Joe

-----Original Message-----
In my years of programming, I've never needed a query remotely near that
long -
perhaps there's another way to accomplish your aim?
More detail would be welcome.

Should you, however, find yourself truly needing to

find
and replace all

references to a query name, I'd recommend the third-

party
product Speed

Ferret.

HTH
- Turtle

I have run into a problem when I add an additional Query
with its associated field to the Record Source of a form.
I obtain an error message "The string returned by the
builder was too long. The result will be truncated."

The SQL statement is then truncated to exactly 2048
characters. This seems odd in that Access Query
specifications says that the number of characters in an
SQL statement is approximately 64K.

Short of renaming the Queries (and trying to find all the
places it is referenced without breaking
anything ......sure)is there some other technique for
increasing the SQL statement size?? Or am I doing
something wrong??

Help is always appreciated.

Joe


.

.

 

[Douglas J. Steele]

I've never heard that the documentation is in error with regards to this
particular issue.

How exactly are you using the SQL? Is it the SQL for a saved query, or are
you building the SQL string in a module and trying to use it?

 

[Joe]

Mea Culpa for not responding sooner. Just trying to figure
out your question so as to provide an intelligent response.

I am building an SQL string in a subform (and subreport)
using the Query builder user interface. For right now the
user interface seems easier than actually writing SQL
statements. (Keep in mind the post to UserGroup Category
of NewUsers. Can't wait for the VBA coding, will get there
some day) This subform query is (was) based on many "sub"
queries which performed various calculations.

As I was attempting to learn the "mechanics" of the
Queries, I would get one working then decide that I wanted
to add another calculation based on similiar tables and
fields. Thus I would copy the working Query, rename it and
modify it until it worked.

On one hand it helped in the learning process......on the
other hand it led to enormous SQL statements as I combined
them in joining them together. Live and learn as I say. So
after some examination of the various calculation queries,
I was able to reduce them to about half of what I
originally had.

The effect was to eliminate redundancy and redistribute
the amount of characters amongst the various SQL
statements with no one being greater than approxmately
1.5k. Doing this also had the benefit of opening the
subform (and subreport) significantly quicker.

Relative to the documentation, the 64K advertised number
came for Access 97 help (Query Specifications). However
naive I may be in the convoluted world of Access, it is
obvious that the approach that I have taken has
limitations.

Comments???

Thanks again
Joe

P.S. You stay up late.......similiar to another
roving "MVP" aka Myrna Larsen (what a mind that Lady has)

 

[Joe]

As a philosophical afterthought, I can understand the need
for databases, but Access as it stands in 97 (perhaps
better in later versions) I believe just wont cut it in
the long run. I guess you get what you pay for.

Ah......Realization may be upon us. Bill has a wonderful
business sense doesnt he.

Please dont let this critique influence the response to
the previous reply. I would love to learn and conquer.

Thanks for all your unending help to us poor souls who
dont have a clue.

Thanks Joe

Great minds (when in tune) think alike

 

[Douglas J. Steele]

Sorry, but I have to disagree with you. Access is an extremely powerful
database, and it certainly "cuts it" for most uses.

 

[Douglas J. Steele]

I believe the limitation you're running into is the fact that the
RecordSource property of a form can only a fixed length string, not a
problem with queries.

Try building the query through the Query tab, and then referring to the
query you saved. (Alternatively, you can continue getting to the query
builder through the ellipsis, but save the query you built, rather than
simply returning to the form.)

 

[Joe]

Thanks Doug,

That appears to be the issue. I have gone back and saved
the form and report queries and set the respective Record
Sources back to the new saved query. Other than having to
reset the sort criteria, no detrimental effects are seen.

I don't think that I will try to see if the limit can now
be reached in the queries.

Thanks again
Joe