how to get input into an Jet OLE DB Query

Question

Background:

I am using Excel 2003

I have 2 Excel files (Source, list), one is essentially my source data. The second is where I am using the excel "Import External data" function to get the data in teh second sheet. I am then using the modifiy query to allow me to use SQL to query my data and limit the data I am displaying.

My SQL query:

SELECT * 
  FROM ['Master List$'] 
 WHERE [Name] Like "Brent%";

My question is this, I would like to have the cell A1 to be an input into my query. So in A1 I would like to have "Brent" to replace the first WHERE condition.

I am hoping to find something to that I will be able to change one cell and be able to refresh the data for whatever is in Cell A1.

Anyone have an idea?

Thanks.

My Connection String is this:

Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;
Data Source=C:\My Data\Master List\Master List.xls;
Mode=Share Deny Write;Extended Properties="HDR=YES;";
Jet OLEDB:System database="";Jet OLEDB:Registry Path="";
Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;
Jet OLEDB:Global Partial Bulk Ops=2;
Jet OLEDB:Global Bulk Transactions=1;
Jet OLEDB:New Database Password="";
Jet OLEDB:Create System Database=False;
Jet OLEDB:Encrypt Database=False;
Jet OLEDB:Don't Copy Locale on Compact=False;
Jet OLEDB:Compact Without Replica Repair=False;
Jet OLEDB:SFP=False

Answer 1

You have erroneously omitted the FROM keyword in your query.

Because you are querying an Excel workbook, it is best to ensure it is closed; if using OLE DB you should be aware of the dreaded memory leak bug. Also be aware of how Excel determines data types, which can get a bit weird with NULLs appearing unexpectedly; more details at Dick's Blog.

You have not specified the worksheet, so I've assumed it is named Sheet1.

Much of your connection string is made up of default values and can be deleted.

Because this is a programming site and you are asking about Excel, how about some VBA:

Sub ExcelExample()

  Const CONN_STRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;" & _
      "Data Source=C:\My Data\Master List\Master List.xls;" & _
      "Mode=Share Deny Write;Extended Properties=""HDR=YES;"";" & _
      "Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";" & _
      "Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;" & _
      "Jet OLEDB:Global Partial Bulk Ops=2;" & _
      "Jet OLEDB:Global Bulk Transactions=1;" & _
      "Jet OLEDB:New Database Password="""";" & _
      "Jet OLEDB:Create System Database=False;" & _
      "Jet OLEDB:Encrypt Database=False;" & _
      "Jet OLEDB:Don't Copy Locale on Compact=False;" & _
      "Jet OLEDB:Compact Without Replica Repair=False;" & _
      "Jet OLEDB:SFP=False"""

  Const SQL_TEXT As String = _
      "SELECT * FROM ['Master List$'] WHERE [Name] Like ?;"

  Dim cmd
  Set cmd = CreateObject("ADODB.Command")

  With cmd
    .ActiveConnection = CONN_STRING
    .CommandText = SQL_TEXT

    Dim param
    Set param = CreateObject("ADODB.Parameter")

    With param
      .Direction = 1  ' adParamInput
      .Type = 202  ' adVarWChar
      .Size = 255
      .Attributes = 64  ' adParamNullable
      .Value = ThisWorkbook.Worksheets("Sheet1").Range("A1").Value & "%"
    End With

    .Parameters.Append param
  End With

  Dim rs
  Set rs = CreateObject("ADODB.Recordset")

  With rs

    Set .Source = cmd
    .Open

    ' Do stuff with recordset...

  End With

End Sub

UPDATE

Which parts of the connection string are useless? Most of it! For example, the default user is Admin but the Jet workgroup file is empty string because Excel doesn't use Jet security. I think the connection string can be reduced as follows without changing behaviour:

  Const CONN_STRING As String = _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\My Data\Master List\Master List.xls;" & _
      "Extended Properties=""HDR=YES;"";"

Answer 2

oops.................