SQL in Excel

[Nishant Choudhary]

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

[Admin]

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/

[Rasm]

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.

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