SQL in Excel

Hi I am trying to run SQL in EXcel on data stored in Sheet2 of same workbook.
I am tryting to fill a combo box using SQL queries ..The list populating is dependent
on selection made in another drop down.eg

A japan
B india
B UK
A germany
A italy
B France

so if A is selected I want the other combo box to be populated with japan,germany and Italy...
please help wth the code for running the SQL

Hi Nishant,

It's recommend that not to query using ADO on an open excel workbook. More details can be found here;

BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO)


You may also interested this link: http://www.excelfox.com/forum/f12/de...-formulas-111/

I read the article about the memory leak - below is the code I use to read my Access table(s) - so I guess I am going to run into the memory problem. Can you suggest another method of connecting to Access or SQL for that matter.

Is there a way I can monitor the amount of free memory - in the olden days the 'Free' command fullfilled that purpose - But I am not sure what replaces that command.

Code:

---

Public Sub FindFirstLastDates(strDBPath As String, DB_PW As String)
    Dim adoConn    As Object
    Dim rstRec      As Object
    Set adoConn = CreateObject("ADODB.Connection")
    Set rstRec = CreateObject("ADODB.Recordset")
    strTable = "SampleHeader"
    On Error Resume Next
    With adoConn
        Err.Clear
        .provider = "Microsoft.Jet.Oledb.4.0"
        .Properties("Jet OLEDB:Database Password") = DB_PW
        .Open "Data Source=" & strDBPath
        If Err.Number <> 0 Then
            On Error GoTo 0
            Exit Sub
        End If
    End With
    rstRec.Open "Select * from " & strTable & " ORDER BY SampleDateTime", adoConn, 3, 3
    rstRec.movefirst
    TxtStartDate.Text = Format(rstRec.fields("SampleDateTime"), "YYYY/MM/DD")
    rstRec.movelast
    TxtEndDate.Text = Format(rstRec.fields("SampleDateTime"), "YYYY/MM/DD")
    adoConn.Close
    On Error GoTo 0
End Sub

---