Re: Viewing the contents of two tables using ASP and MS Access

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 10/14/04

  • Next message: Ray Costanzo [MVP]: "Re: Problem on querying Access link table in ASP page" 
    Date: Thu, 14 Oct 2004 09:06:57 -0400

    Stuart Clark wrote:
    > Nearly had it!
    >
    > I take it then that there is a limit to how long a SELECT statement
    > is?

    Sort of. There are limits to certain features in Jet Queries. Let me check
    online help ...

    Number of enforced relationships 32 per table minus the number of indexes
    that are on the table for fields or combinations of fields that are not
    involved in relationships

    Number of tables in a query 32

    [me] I am not really sure if this refers to the number of tables joined in a
    single FROM clause, or whether it applies to the number of union selects as
    well. I suspect the latter may be the case.

    Number of fields in a recordset 255

    [me] How many fields are you returning? If more than 255, you are going to
    have to forget about using selstart (select *) and provide column lists
    instead (you really should be doing this anyways -
    http://www.aspfaq.com/show.asp?id=2096)

    Recordset size 1 gigabyte
    Sort limit 255 characters in one or more fields
    Number of levels of nested queries 50
    Number of characters in a cell in the query design grid 1,024
    Number of characters for a parameter in a parameter query 255
    Number of ANDs in a WHERE or HAVING clause 99

    Number of characters in an SQL statement approximately 64,000

    [me] Is your sql statement longer than 64000 characters???

    > I nearly had it working. I gave up on the two pages communicating
    > with a cookie idea and put everything back into one page as I
    > originally had it. Used ADOX to get the table names, wrote them into
    > a SELECT statement which was stored as a variable, and then created a
    > recordset using the SELECT statement stored in the variable.
    >
    > I think it may have worked, but there must be over a hundred tables in

    Ridiculous!!!

    > the database, and I think the SELECT statement must have therefore
    > been too long as I had an error:
    >
    > Error Type:
    > Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
    > [Microsoft][ODBC Microsoft Access Driver] Query is too complex.
    > /imail/TMP3o63a5kj6l.asp, line 41
    >
    >
    > Back to the drawing board I suppose!
    >

    I f you response.write the sql statement and paste it into the SQL View of
    the Access Query Builder, does it run without error? If not, what do you
    have to do to it to make it work? That will give you your answer.

    If it is the length or number of tables that is causing the problem, then
    you are going to have to put a counter in your loop, using it to create as
    many union queries as are needed to retrieve the data you need, retrieveing
    the multiple recordsets and combining them when you display them.

    An alternative you may wish to consider:
    If you do not need upt-to-the-minute data to display on this page, you
    should create a scheduled job that combines the data from all your tables
    into a single table. I would not use ASP for this as the operation is likely
    to take longer than a typical ASP operation. Instead, a VB application could
    be created to do this. Or an Access VBA procedure could do it. Use the
    Windows scheduler to run it at the periodic intervals you define. I would
    probably put the combined data into its own database, so reporting
    activities can take place without interfering with data modification
    activities.

    This article
    http://www.aspfaq.com/show.asp?id=2143
    shows how to schedule a script to run periodically. Again, my preference
    would be to use compiled code for this task instead of script, but it can be
    done with script.

    Bob Barrows 

    -- 
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
  • Next message: Ray Costanzo [MVP]: "Re: Problem on querying Access link table in ASP page"

    Relevant Pages

    • Why does my recordset always return .eof=true?!
      ... I'm having an issue with a piece of code designed to populate a text ... Check if less than 3 characters entered and quit if so - not enough ... All appears to work until the recordset is opened. ... I've tried opening the query written with the above sql statement ...
      (comp.lang.basic.visual.misc)
    • Re: Query on text field with multiple spaces within the string
      ... > open a recordset containing just one record and one field - one that you ... > other invisible characters hidden in there. ... >>> you can find out for yourself by creating a SELECT query that returns ... >>> Please respond in the newgroup and not by email. ...
      (microsoft.public.access.queries)
    • Re: Opening a query in MS Access with parameters using VB.net
      ... datasource to a SQL statement. ... using the stored query as the datasource. ... Dim rs As Recordset ...
      (microsoft.public.dotnet.languages.vb)
    • Re: Weird ADO error, any ideas?
      ... Try including that field in your query but not accessing it from code inside ... If that doesn't throw, then try declaring the string as constant length, ... therefore any data in the recordset has come from the SQL ... but include characters form the upper half of the character set. ...
      (microsoft.public.vb.general.discussion)
    • Re: mailmerge and sql
      ... I don't know what limit MS Query imposes but the ... If you're starting from SQL ... I saw that there> was a secondary argument I could use to> get an additional 255 characters - for a whopping total of> 510 characters! ... That would be great except that my sql statement> is approximately 600 characters give or take. ...
      (microsoft.public.word.mailmerge.fields)
    Loading