DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Query Question

Hybrid View

  1. #1
    Join Date
    Jul 2008
    Posts
    1

    Query Question

    I have a function which runs an SQL query and returns a recordset. This function takes two recordsets as arguments for the SQL statement.

    So far I have this:

    Code:
    Private Function Additions(existingData As ADODB.Recordset, additionalData As ADODB.Recordset) As ADODB.Recordset
        Dim adoRs As ADODB.Recordset
        Dim SQL As String
    
        Set adoRs = New ADODB.Recordset
    
        SQL = "SELECT Additional.*, Existing.* FROM Additional, Existing;"
    
        adoRs.Open SQL
        adoRs.Sort = "PartNo"
    
        Set Additions = adoRs
    
        Set adoRs = Nothing
    End Function
    Clearly this is incorrect; the problem I am having is having is how do you change this code so that the SQL terms additional and existing refer to the two recordsets passed into the function? What I am trying to do is to run the query on the two recordsets passed into the function as if they were tables.

    If this will never work then what is the best alternative to approach this?

    Any help is greatly appreciated.

    Thanks
    All help is greatly appreciated
    Kind regards
    Ota

  2. #2
    Join Date
    Aug 2004
    Location
    Orange, California
    Posts
    1,263
    You are absolutly correct. This is not going to work.
    A Query is always performed against a database object, which a recordset is not.
    A recordset is an object, ADO, DAO, etc., that is used to hold the results of a query. Think of it as a data structure, like a big array, or a collection. Just like you can't run an SQL statement against a variable in your program code because the sql is sent to the database and the database has no access to your variable in your code.
    So your options become to combine the two original SQL statments into one that retreives all the records that you need. Basically using one large SQL Statement.
    Or to save each of the queries as a view, or if you are using MS Access, you can save them as queries. At this point these SQL Statements become database objects that you can query against. You use the names you saved them with, just like they were tables names.

Similar Threads

  1. Simple sql query for recordset
    By awyeah in forum Database
    Replies: 1
    Last Post: 07-13-2008, 11:35 AM
  2. Union query
    By peljo in forum Database
    Replies: 4
    Last Post: 12-17-2007, 12:38 PM
  3. Multi-table Select Query using Joins
    By knightsg in forum Database
    Replies: 0
    Last Post: 02-16-2005, 09:07 AM
  4. Replies: 8
    Last Post: 08-09-2002, 08:00 AM
  5. Query Question
    By Shawn M in forum Database
    Replies: 4
    Last Post: 11-03-2000, 12:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center