Solved

Hit the RecordSource string limit in an Access 2000 Project Form

Posted on 2003-11-19
10
374 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 75 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 75 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 100 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 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