Solved

Hit the RecordSource string limit in an Access 2000 Project Form

Posted on 2003-11-19
10
370 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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm thinking of dumping the SQL string into a view on the server.  But would this have problematic security implications ?
0
 
LVL 3

Expert Comment

by:mccredb
Comment Utility
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
Comment Utility
Is there any way to make a form show multiple records and populate it manually from a recordset ?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 17

Author Comment

by:Natchiket
Comment Utility
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 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 75 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now