?
Solved

Hit the RecordSource string limit in an Access 2000 Project Form

Posted on 2003-11-19
10
Medium Priority
?
382 Views
Last Modified: 2012-06-22
I've written some code which compiles an SQL statement depending on user parameters etc, only problem is that in some stiutations when I apply the SQL string to a form's RecordSource property to display the results I get the error, 'Setting for this property is too long'.

What's the best option(s) for getting around this ?
Back end is SQL server 2000.
0
Comment
Question by:Natchiket
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9778859
you can only replace the STRING with a Query that produces the same result set.  There is no way to get around the limitation that the RowSource text is limited to 2048 characters.

AW
0
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 9778866
alternative might be to have the basic SQL in a SQL Server Stored procedure, and pass the constraints as Input parameters to the Stored Proc.  This would of course depend on what you are attemtping to allow the user to do.

AW
0
 
LVL 17

Author Comment

by:Natchiket
ID: 9779005
I'm thinking of dumping the SQL string into a view on the server.  But would this have problematic security implications ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:mccredb
ID: 9779201
Had a similar problem myself.  Got round it by substituting a long table name with a short alias.

eg
Select Longtablename.field1, Longtablename.field2, ...
From LongTableName
Group By Longtablename.field1, Longtablename.field2, ...
Order By Longtablename.field1;

can be replaced with...

Select L.field1, L.field2, ...
From LongTableName as L
Group By L.field1, L.field2, ...
Order By L.field1;

which obviously uses fewer characters.

Regards,

David.
0
 
LVL 17

Author Comment

by:Natchiket
ID: 9779206
Is there any way to make a form show multiple records and populate it manually from a recordset ?
0
 
LVL 17

Author Comment

by:Natchiket
ID: 9779845
Unfortunately I don't have the luxury of shortening the SQL very much, due to the fact that ii'm using loads of UNIONS to assess hit rates on various criteria options e.g.

SELECT TOP 100 PERCENT tblCentralAdressBook.ContactID,
    tblCentralAdressBook.LastName + ', ' + tblCentralAdressBook.FirstName
     Consultant, InHouse, Principle, Recommended, Ok, Other,
    Gender, Local, RESI.Country AS COR,
    NATI.Country AS Nationality, CONQUANT.HITS AS HITS
FROM dbo.tblCentralAdressBook INNER JOIN
        (SELECT tblConsultants.ContactID, COUNT(STUFF.CVID)
           AS HITS
      FROM (SELECT CVID
            FROM tblLanguageExp
            WHERE Language = 'AR'
            UNION ALL
            SELECT CVID
            FROM tblLanguageExp
            WHERE Language = 'BAS'
            UNION ALL
            SELECT CVID
            FROM tblLanguageExp
            WHERE Language = 'BR'
            UNION ALL
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 7
            UNION ALL
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 41
            UNION ALL
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 13
            UNION ALL
            SELECT CVID
            FROM tblCountryExp
            WHERE Country = 1
            UNION ALL
            SELECT CVID
            FROM tblCountryExp
            WHERE Country = 2
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.00'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.01'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.02'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.04'
            UNION ALL
            SELECT CVID
            FROM tblConsultants
            WHERE InHouse = 1) STUFF INNER JOIN
           dbo.tblConsultants ON
           dbo.tblConsultants.CVID = STUFF.CVID
      GROUP BY dbo.tblConsultants.ContactID) CONQUANT ON
    dbo.tblCentralAdressBook.ContactID = CONQUANT.ContactID INNER
     JOIN
    tblConsultants ON
    tblConsultants.ContactID = tblCentralAdressBook.ContactID LEFT
     OUTER JOIN
    tblCountryLkp RESI ON
    tblConsultants.ResidenceC = RESI.CountryID LEFT OUTER JOIN
    tblCountryLkp NATI ON
    tblConsultants.BirthCountry = NATI.CountryID
WHERE tblCentralAdressBook.ContactID IN
        (SELECT ContactID
      FROM tblConsultants
      WHERE CVID IN
               (SELECT CVID
             FROM tblConsultants
             WHERE InHouse = 1) AND (CVID IN
               (SELECT DISTINCT tblKeywordExp.CVID
             FROM tblKeywordExp
             WHERE Keyword IN ('01.00', '01.01', '01.02',
                  '01.04')) OR
           CVID IN
               (SELECT DISTINCT tblCountryExp.CVID
             FROM tblCountryExp
             WHERE Country IN (1, 2)) OR
           CVID IN
               (SELECT DISTINCT tblClientExp.CVID
             FROM tblClientExp
             WHERE Client IN (7, 41, 13)) OR
           CVID IN
               (SELECT DISTINCT tblLanguageExp.CVID
             FROM tblLanguageExp
             WHERE Language IN ('AR', 'BAS', 'BR'))))
ORDER BY CONQUANT.HITS DESC

If anyone can think of a way of shortening this I'd be grateful!
0
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 300 total points
ID: 9780172
Try this in OnOpen event of your form:

Me.RecordSource=fnSqlStr()
----------
And, in a standard module, under module tab have:

Public Function fnSqlStr() As String

fnSqlStr= "SELECT TOP 100 PERCENT tblCentralAdressBook.ContactID, " & _
    "tblCentralAdressBook.LastName + "', "' + tblCentralAdressBook.FirstName " & _
     .
     .
            "WHERE Country = 2  " & _
            "UNION ALL  " & _
            "SELECT CVID  " & _
            "FROM tblKeywordExp  " & _
            "WHERE Keyword = "'01.00"'  " & _
            "UNION ALL  " & _
            .
            .
"ORDER BY CONQUANT.HITS DESC"

End Function
0
 
LVL 3

Assisted Solution

by:mccredb
mccredb earned 300 total points
ID: 9780282
Your right there isn't much you can do with aliasing within union queries.  

However I did notice with the union parts, that you are accessing the same tables/fields multiple times.  Why not use the IN operator as you have towards the bottom of your SQL,  or doesn't that work with union queries either?
0
 
LVL 44

Accepted Solution

by:
Arthur_Wood earned 400 total points
ID: 9780670
this block:

FROM tblLanguageExp
            WHERE Language = 'AR'
            UNION ALL
            SELECT CVID
            FROM tblLanguageExp
            WHERE Language = 'BAS'
            UNION ALL
            SELECT CVID
            FROM tblLanguageExp
            WHERE Language = 'BR'
can be shortened to:

FROM tblLanguageExp
            WHERE Language in ( 'AR', 'BAS','BR')

similarly:
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 7
            UNION ALL
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 41
            UNION ALL
            SELECT CVID
            FROM tblClientExp
            WHERE Client = 13
 becomes

            SELECT CVID
            FROM tblClientExp
            WHERE Client IN ( 7, 41, 13)
 and:

           SELECT CVID
            FROM tblCountryExp
            WHERE Country = 1
            UNION ALL
            SELECT CVID
            FROM tblCountryExp
            WHERE Country = 2
becomes
           SELECT CVID
            FROM tblCountryExp
            WHERE Country IN (1,2)
and finally:

           SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.00'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.01'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.02'
            UNION ALL
            SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword = '01.04'

shortens to:

           SELECT CVID
            FROM tblKeywordExp
            WHERE Keyword IN ( '01.00','01.01','01.02', '01.04')

 and that cuts a siginificant part of the string down to a more manageable size.


it means that you must do a bit more coding to build, in your code, the IN ('....') phrases, rahter than simply adding additional UNION sections.  But if that is what you require, then the extra code will be worth the effort.

AW


 
0
 
LVL 17

Author Comment

by:Natchiket
ID: 9837025
Thanks for the advice folks.  In the end I cheated and converted it into an INSERT query, dumping the data into a table which the report was much happier with (along with a field for the user's name so different users wouldn't get each others data mixed up)
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question