Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    32

    Unanswered: Querying recordsets

    Hi:

    I have a working search form for my Access db: I am in clean up mode to try to improve it.

    I am new to VB so the approach I have taken may be flawed but it produces the correct results.

    If the user selects "Smith" as the author, "Map" as the publication type and "2002" as the year, via VB I create:

    SQLAuthorString = "Author='Smith'"
    SQLPubTypeString = "Format='Map'"
    SQLYearString = "Year='2000'"

    Set rs = db.OpenRecordset("SELECT Title FROM Publications WHERE" & SQLAuthorString & " AND " & SQLPubTypeString & " AND " & SQLYearString & ";)

    This example is very simple compared with some of the queries I am building whereby BETWEEN is used for a date range, multiple authors are selected, etc so the final SQL string could be ABSOLUTELY MASSIVE but it works and works well.

    My thoughts are that rather than building a single MASSIVE query string, create a series of recursive query strings. Using the example above:

    (1) Query the database based on Author,
    (2) Query the recordset(1) based on Format,
    (3) Query the recordset(2) based on Year.

    The process I have worked out in scratch is to query the database and dump the results of the first query into rsMaster. Then query rsMaster and dump the results into rsTemp1. Then insert all of rsTemp1 into rsMaster so that the nest query queries rsMaster and writes rsTemp1 back into rsMaster again, and so on. Here is the code (ignore the syntax for the moment):

    rsMaster = SELECT * FROM database WHERE Author=criteria
    rsTemp1 = SELECT * FROM rsMaster WHERE Format=criteria
    INSERT INTO rsMaster (*) VALUES (rsTemp1.field1, rsTemp1.field2, ...)

    But in the insert line cannot find rsTemp1. I presume that this is because rsTemp1 is a recordset, not a "real" query.

    Any suggestions?

    TIA,

    Jules
    Last edited by jrickards; 02-23-04 at 16:07.

  2. #2
    Join Date
    Jan 2004
    Posts
    184

    Re: Querying recordsets

    What is the advantage of doing it your way?

    Another way would be to create your initial recordset on rsAuthors then filter according to your criteria (i.e. year etc...). What do you think?
    In abundance of water only the fool is thirsty. Bob Marley.

  3. #3
    Join Date
    Feb 2004
    Posts
    32
    I can't answer the advantage question because I don't know if my proposed methodology is flawed or not. My thinking is that it may be faster to run a series of recordset queries on smaller and smaller data rather than one MASSIVE query on the whole database.

    The other problem I have to deal with is that the user may not wish to search on Author, just on Year and Format, or on Year and Author but no Format. This is why I am trying to be flexible with the code and reusing rsMaster and rsTemp1.

    Isn't a filter the same as a query? If not, how to a create a VB filter?

  4. #4
    Join Date
    Jan 2004
    Posts
    184
    I see what you are saying, then maybe you should have 2 recordset objects, one Year, Format and a second Year, Author.

    No a filter hides for all intents and purposes any records that don't match your criteria. This is much faster then a query. For example you get your initial Author recordset, then you issue the command

    rsAuthor.filter = "Year=1997"

    The recordset just hides all the records that are not year 1997, but they are still in memory, so if you issue another filter

    rsAuthor.filter "Year=2000"

    You get your results instantly

    (To remove the filter type rsAuthors.filter = "")

    Here is some more info for you:

    ==================================

    .Filter

    Specifies a filter for data in a Recordset.

    Sets or returns a Variant value, which can contain one of the following:

    Criteria string — a string made up of one or more individual clauses concatenated with AND or OR operators.

    Array of bookmarks — an array of unique bookmark values that point to records in the Recordset object.


    One of the following FilterGroupEnum values. Constant Description
    adFilterNone Removes the current filter and restores all records to view.
    adFilterPendingRecords Allows you to view only records that have changed, but have not yet been sent to the server. Applicable only for batch update mode.
    adFilterAffectedRecords Allows you to view only records affected by the last Delete, Resync, UpdateBatch, or CancelBatch call.
    adFilterFetchedRecords Allows you to view records in the current cache—that is, the results of the last call to retrieve records from the database.
    adFilterConflictingRecords Allows you to view the records that failed the last batch update attempt.


    Remarks

    Use the Filter property to selectively screen out records in a Recordset object. The filtered Recordset becomes the current cursor. This affects other properties, such as AbsolutePosition, AbsolutePage, RecordCount, and PageCount, that return values based on the current cursor, because setting the Filter property to a specific value will move the current record to the first record that satisfies the new value.

    The criteria string is made up of clauses in the form FieldName-Operator-Value (for example, "LastName = 'Smith'"). You can create compound clauses by concatenating individual clauses with AND (for example, "LastName = 'Smith' AND FirstName = 'John'") or OR (for example, "LastName = 'Smith' OR LastName = 'Jones'"). Use the following guidelines for criteria strings:

    FieldName must be a valid field name from the Recordset. If the field name contains spaces, you must enclose the name in square brackets.


    Operator must be one of the following: <, >, <=, >=, <>, =, or LIKE.


    Value is the value with which you will compare the field values (for example, 'Smith', #8/24/95#, 12.345 or $50.00). Use single quotes with strings and pound signs (#) with dates. For numbers, you can use decimal points, dollar signs, and scientific notation. If Operator is LIKE, Value can use wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last character in the string. Value cannot be Null.


    There is no precedence between AND and OR. Clauses can be grouped within parentheses. However, you cannot group clauses joined by an OR and then join the group to another clause with an AND, like this:
    (LastName = 'Smith' OR LastName = 'Jones') AND FirstName = 'John'

    Instead, you would construct this filter as
    (LastName = 'Smith' AND FirstName = 'John') OR (LastName = 'Jones' AND FirstName = 'John')

    In a LIKE clause, you can use a wildcard at the beginning and end of the pattern (for example, LastName Like '*mit*'), or only at the end of the pattern (for example, LastName Like 'Smit*').
    The filter constants make it easier to resolve individual record conflicts during batch update mode by allowing you to view, for example, only those records that were affected during the last UpdateBatch method call.

    Setting the Filter property itself may fail because of a conflict with the underlying data (for example, a record has already been deleted by another user). In such a case, the provider returns warnings to the Errors collection but does not halt program execution. A run-time error occurs only if there are conflicts on all the requested records. Use the Status property to locate records with conflicts.

    Setting the Filter property to a zero-length string ("") has the same effect as using the adFilterNone constant.

    Whenever the Filter property is set, the current record position moves to the first record in the filtered subset of records in the Recordset. Similarly, when the Filter property is cleared, the current record position moves to the first record in the Recordset.

    See the Bookmark property for an explanation of bookmark values from which you can build an array to use with the Filter property.


    Originally posted by jrickards
    I can't answer the advantage question because I don't know if my proposed methodology is flawed or not. My thinking is that it may be faster to run a series of recordset queries on smaller and smaller data rather than one MASSIVE query on the whole database.

    The other problem I have to deal with is that the user may not wish to search on Author, just on Year and Format, or on Year and Author but no Format. This is why I am trying to be flexible with the code and reusing rsMaster and rsTemp1.

    Isn't a filter the same as a query? If not, how to a create a VB filter?
    In abundance of water only the fool is thirsty. Bob Marley.

  5. #5
    Join Date
    Feb 2004
    Posts
    32
    It turns out that my message was slightly wrong. The error message I am getting occurs at the query against the recordset.

    rsMaster = SELECT * FROM Publications WHERE Author = 'Smith'
    rsTemp = SELECT * FROM rsMaster WHERE Year = '2000'

    It is the second SELECT that gives me the error message, can't find the query or table called rsMaster.

  6. #6
    Join Date
    Jan 2004
    Posts
    184
    Yes to my knowledge, you cannot run a select statement on a recordset.

    Originally posted by jrickards
    It turns out that my message was slightly wrong. The error message I am getting occurs at the query against the recordset.

    rsMaster = SELECT * FROM Publications WHERE Author = 'Smith'
    rsTemp = SELECT * FROM rsMaster WHERE Year = '2000'

    It is the second SELECT that gives me the error message, can't find the query or table called rsMaster.
    In abundance of water only the fool is thirsty. Bob Marley.

  7. #7
    Join Date
    Feb 2004
    Posts
    32
    Thanks very much for your help Rami, I will keep the filter procedure in mind but, as I said, there are more options for the user to select and so a filter may not be sufficient. In some cases, the query crosses tables through joins which would likely not be an option for filters but I can see a filter for other area.

    Hmmm, have to think about this one.

    Thanks again,

    Jules

  8. #8
    Join Date
    Jan 2004
    Posts
    184
    My pleasure Jrickards, I hope that helped.

    In any case check out this control that makes VB database programming so much easier:

    http://www.geocities.com/scirocco_ha/DataControl.htm

  9. #9
    Join Date
    Jan 2004
    Posts
    184
    I forgot to mention that if you absolutly need to run a query on a recordset there is a control that lets you do exactly that

    http://download.com.com/3000-2404-10...ml?tag=lst-0-2

    Execute SQL queries on ADO Recordsets as though they existed as Tables in a Database. With the QueryARecordset dll component you can perform full SQL queries on any ADO Recordsets and return the results to your application. No more complex filter and find routines or writing data to temporary tables. No more expensive round trips to the Server to obtain a subset of data that already exists saving on valuable Connection and Server resources.
    In abundance of water only the fool is thirsty. Bob Marley.

  10. #10
    Join Date
    Feb 2004
    Posts
    32
    Originally posted by rami.haddad
    I see what you are saying, then maybe you should have 2 recordset objects, one Year, Format and a second Year, Author.

    No a filter hides for all intents and purposes any records that don't match your criteria. This is much faster then a query. For example you get your initial Author recordset, then you issue the command

    rsAuthor.filter = "Year=1997"
    HI:

    I am going to try to implement rs.filter. However, I have a question.

    Can I use the SQL INSERT to insert filtered records? As you said, it is not a "permanent" change so I am wondering if I do an insert of the rs if all records, filtered and unfiltered, will be inserted or just the filtered one.

    For some reason, I suspect that all records will be inserted. If so (I am going to run a test momentarily), is there any way to apply a filter then insert the records? For example,

    rs!PUBLICATION_YEAR.filter="PUBLICATION_YEAR=2000"

    Jules

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •