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) (http://support.microsoft.com/kb/319998)


You may also interested this link: http://www.excelfox.com/forum/f12/dependent-data-validation-using-only-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