Query Question

[Otacustes]

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:

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

 

[SkipVought]

How about

SQL = "SELECT Additional.*, Existing.* FROM (Additional.activecommand.commandtext), (Existing.activecommand.commandtext);"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Otacustes]

Thanks SkipVought but I plugged in your suggestion and now get an error:

Run-time error '3709':
The connection cannot be used to performthis operation. It is either closed or invalid in this context.

and stops at

adoRs.Open SQL

Any ideas?

All help is greatly appreciated
Kind regards
Ota

 

[SkipVought]

BTW, How many rows in each recordset? What you have is a cartesian join (every row in A is joined to every row in B).

What are you trying to accomplish here?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Golom]

You haven't given the system a connection object to use when opening the recordset. You need something like

adoRs.Open SQL[red], ADOConn[/red]

Where ADOConn is some ADODB connection object.

 

[Otacustes]

There will be approx 100 rows in each recordset. As the code develops the query will become more complicated - I thought i'd start off simple!!

I am not so sure, anymore, that this was the proper way to achieve this...

The initial idea is that I could pass two record sets to a function, perform an SQL query on that recordset (in this function to find the difference between the recordsets) and pass a recordset back containing the results.

The recordsets passed into the function are generated by retrieving values from cells in an excel worksheet.

As I say I am not so sure whether this is the right way to go about this but I am open to suggestions.

Thanks

All help is greatly appreciated
Kind regards
Ota

 

[Otacustes]

Galom,

How do you connect to the excel workbook that you are currently workng in?

As I understand it ADOConn is a connection but what do I connect to if the code is being run from where the data is?

Hopefully that makes sense?

All help is greatly appreciated
Kind regards
Ota

 

[SkipVought]

    sPath = ActiveWorkbook.Path
    sDB = Split(ActiveWorkbook.Name, ".")(0)
    
    Set cnn = New ADODB.Connection
    
    sConn = "Provider=MSDASQL.1;"
    sConn = sConn & "Persist Security Info=False;"
    sConn = sConn & "Extended Properties=""DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"""
    
    cnn.Open sConn
    
    Set adoRs = New ADODB.Recordset

    SQL = "SELECT * From [Sheet1$]"
    adoRs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
'...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Otacustes]

I tried that Skip and now have this error:

[Microsoft][ODBC Excel Driver] Cannot update. Database or object is read-only.

and stops here:

adoRs.Open SQL, cnn, adOpenStatic, adLockReadOnly, adCmdText

I cannot believe it is so hard to run an SQL query on a recordset in excel...surely other people have done this before???

Thanks


All help is greatly appreciated
Kind regards
Ota

 

[xlbo]

yes - many many times - and it is (usually) very straightforward - has the file been saved? Is there any security on th efolder where the file is saved?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Otacustes]

Hi Geoff

Yep, the file has been saved on my desktop so there is no security on the folder.

As an experiment, I added readonly=false to the connection string but it thengave me another error:

Could not find installable IASM

Thanks for the reply.

All help is greatly appreciated
Kind regards
Ota

 

[SkipVought]

Have you read the HELP on recordset OPEN? Notice that my code had adLockReadOnly. No wonder you could not UPDATE!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Otacustes]

I took out adLockReadOnly but it still complains!!

I am only trying a simple SELECT SQL statement nothing that would require adding data...that comes later.

I am beginning to think that I should abandon this and go for plan two!!

Thanks guys.

All help is greatly appreciated
Kind regards
Ota

 

[SkipVought]

Please post all your code. Your posts have not been consistent! First you talk about UPDATE then you say simple SELECT.

Show us what you're working with.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Anyway, you can't reference a recordset inside SQL code.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886