Hit the RecordSource string limit in an Access 2000 Project Form

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.
LVL 17
NatchiketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Arthur_WoodCommented:
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
Arthur_WoodCommented:
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
NatchiketAuthor Commented:
I'm thinking of dumping the SQL string into a view on the server.  But would this have problematic security implications ?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mccredbCommented:
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
NatchiketAuthor Commented:
Is there any way to make a form show multiple records and populate it manually from a recordset ?
0
NatchiketAuthor Commented:
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
Mike EghtebasDatabase and Application DeveloperCommented:
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
mccredbCommented:
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
Arthur_WoodCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NatchiketAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.