[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

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.
0
Natchiket
Asked:
Natchiket
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

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

Featured Post

Independent Software Vendors: 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!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now