Corect use of SQL

[AlexanderBB]

Hi,

I want to query some data in my worksheet using SQL. This may not be a good idea as I recall some problem with memory leaks or similar, but regardless I can't get it working and would like to

I'm using ADODB and I have a working connection (to the current workbook) and can get results using "Select * From...". But I don't want "*" instead I want to specify a column (or columns)

A few things found online didn't work here e.g. "Select [Sheetname$.Col1]... or 'select [F1], [F2] etc.
Please, what is the correct working syntax after SELECT ?

Perhaps part of the problem is the extended header? I'm using ='Excel 8.0;HDR=NO'. There were conflicting answers found for HDR, some said use NO even if there are headers. And to use "IMEX=1" but that just seemed to make the connection take longer.

Any advice most welcome, thanks !

 

[mole999]

some current discussions from here > https://www.mrexcel.com/forum/excel-questions/1043777-remove-duplicates-using-sql-distinct.html

not sure what the referencing has to be in excel

 

[AlexanderBB]

He says'All the column in the worksheet Sheet1 have fieldnames in the first row
'which we all use here.
stSQL = "SELECT DISTINCT Data FROM [Sheet1$]"
Suggesting Data is one of his column names.
But With or Without DISTINCT (and HDR= Yes or No) still doesn't work here.Also tried JET OLEDB4/Ace12 and Excel 8.0/12.0 in the Connection string.
It only works if I use "Select *". Without a way to address the columns you can't set any WHERE criteria. Frustrating !

 

[RoryA]

You really haven't given enough information. What code are you using to connect to it? What format is your workbook? Does it have headers? If so, are they in row 1? And also, what exactly do you mean when you say the code you have doesn't work - what happens?

 

[AlexanderBB]

Sorry Rory, I was focussing on just the composition of the SQL as the problem, but you're quite right, it may be something else.
Here's the whole thing (having tried slight changes as per Msg 3).

Sub RsTest()
    Dim myConnection As New ADODB.Connection
    Dim myRecordset As ADODB.Recordset
    With myConnection
        .Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
        "Data Source=Y:\Test3.xlsm;" & _
        "Extended Properties='Excel 12.0;HDR=YES';"
            Set myRecordset = New ADODB.Recordset
            myRecordset.Open "Select * from [EP65$]", myConnection, adOpenStatic
                Do Until myRecordset.EOF
                    Debug.Print myRecordset(0) ', myRecordset(1)
                    myRecordset.MoveNext
                Loop
            myRecordset.Close
            Set myRecordset = Nothing
        .Close
    End With
End Sub

And the problem is Want "Select MyColumn2, MyColum3 From.. rather that everything.

 

[RoryA]

What are the headers in row 1 for the columns you want?

 

[AlexanderBB]

I've just set up two for testing and called them 'ColA' and 'ColB'
I don't necessarily need headers, but can do if it won't work without them.

 

[mole999]

SQL wants to know headers without spaces, when querying you can use '' for a blank column, given that

SELECT
ColA
,ColB
FROM
Data
WHERE

should return the columns

 

[AlexanderBB]

Yes Mole. thank you !! I could have sworn I'd tried every possibility. It all makes sense, with HDR=NO fields are F1, F2 etc
and HDR=YES it's the column names on Row 1. As expected... but I couldn't make it work until now! Weird...

Anyway do you know what the story is on "memory leaks" when doing this (to the current workbook)? I read using a closed workbook is recccomended (presumably a copy)?

Can the memory leaks be plugged or otherwise handled?

 

[mole999]

I have no knowledge on memory leaks, guessing that the connection wasn't being closed in some methods