Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
VB Databases Basics Beginning-level VB coding questions specific to using VB with databases. Issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the VB Databases Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 14th, 2008, 08:55 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default Query Question

Hello all,

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.

Any help is greatly appreciated.

Thanks


Any help is greatly appreciated

Kind regards
Ota
Reply With Quote
  #2 (permalink)  
Old July 14th, 2008, 12:14 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

Tip: When you add code [code...] to a post, it is rendered literally. You should edit your post to make the 1st line at least 2 lines...
Code:
Private Function Additions(existingData   As ADODB.Recordset, _
                           additionalData As ADODB.Recordset _
                          ) As ADODB.Recordset
                          I don't believe you can do this. You need to take the SQL the creates the recordset(s), and use that to open a new recordset. I just know of no way to treat an ADODB RecordSet as a table. It is much more akin to a specialized array.
Reply With Quote
  #3 (permalink)  
Old July 14th, 2008, 01:13 PM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by BrianWren
 Tip: When you add code [code...] to a post, it is rendered literally. You should edit your post to make the 1st line at least 2 lines...
Code:
Private Function Additions(existingData   As ADODB.Recordset, _
Code:
                           additionalData As ADODB.Recordset _
                          ) As ADODB.Recordset
                        I don't believe you can do this. You need to take the SQL the creates the recordset(s), and use that to open a new recordset. I just know of no way to treat an ADODB RecordSet as a table. It is much more akin to a specialized array.
Damn!! :( The recordsets are created from an excel spreadsheet...what would you suggest is a good way to create a table that I can run an SQL script on?

Thanks

Any help is greatly appreciated

Kind regards
Ota
Reply With Quote
  #4 (permalink)  
Old July 14th, 2008, 05:37 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

 If you want to do a JOIN, there is no reason you can't do the join *FROM* the Excel spreadsheet(s).

That is, do *NOT* create two separate recordsets. Just modify the SQL you used to get the data form Excel so *IT* returns a single recordset.

On the other hand, the code you show there doesn't look like it wants to do a JOIN. Looks like you just want the UNION of the two results, no? Is the structure of the two recordsets identical or nearly so??? Same number of fields with same types or at least compatible types??

If so, you *COULD* disassociate each RS from its connection if you use a client side cursor and then set the data source of each to NOTHING. Then you could indeed choose one RS as the "master" and use AddNew on it as you added records from the other Recordset. It would be slow and clumsy compared to other ways, but it should work.

You could also do this in VB.NET (well, actually ADO.NET) by creating a DataSet and then using the data from the two queries to create DataTables and then (to a limited degree) you can relate the two DataTables and do filtering on the result. Or, again if this is actually a UNION that is needed, it's trivial to copy DataRows from one DataTable to another. But clearly *NOT* using ADODB.Recordset objects. You'd have to switch to ADO.NET.

But lay some more details on us. Start by discussing whether you need a JOIN or a UNION. And start by discussing why you can't just use a single query against Excel (if, indeed, you can't).
Reply With Quote
  #5 (permalink)  
Old July 15th, 2008, 01:13 AM
Registered User
 
Join Date: Jan 2007
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The SQL statement was something just to get me started and indeed in the future will change so that it compares existing data to the additional data and return a recordset with what has been added - that was the plan anyway!!

I am not sure how you'd alter the SQL statement to get the information from the excel sheet; the information is contained in different positions on the sheet.

Hopefully I didn't misunderstand you. Thanks very much.

Any help is greatly appreciated

Kind regards
Ota
Reply With Quote
  #6 (permalink)  
Old July 15th, 2008, 12:27 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

[u]Please</u> edit your original post so that this internet form is not wider
than my screen!

Loop through the recordset, adding each record in turn to a table in Access, then
open that table in a recordset. (You can create a connection, through that connection
create a table, then use SQL INSERT statements with the connection to populate the
table.)
Reply With Quote
  #7 (permalink)  
Old July 15th, 2008, 01:24 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

You just give us nothing to go on.

SHOW the two queries you are using to get the two recordsets.

SHOW the list of fields and types in each recordset.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query question ldp101068 SQL Server 2000 6 December 3rd, 2007 03:41 PM
Update Query Question arholly Access 2 January 19th, 2007 01:28 PM
query performance question kBusby Oracle 3 February 14th, 2005 04:42 PM
Database Query Question kbuska BOOK: Beginning ASP 3.0 4 September 23rd, 2004 11:04 PM
Query Question tingtuang SQL Language 3 June 19th, 2003 11:07 AM



All times are GMT -4. The time now is 06:47 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.