wildcard search a Access DB


Board Regular
Jun 3, 2002
Hi, I want to do a wildcard search of a MS Access DB using a value in Excel in cell A1. In the example below, I am trying to find "%4605%". The code below works perfectly for %4605%, but the value will not always be %4605%. Sometimes the value is different.

I want to know how to search based on the value in cell A1. I am using Access11(2003) and Excel11(2003). Thanks in advance.
Sub Macro05_wildcard_search()
Application.Goto Reference:="R1C1"
Application.Goto Reference:="R1C6"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\TEMP\New Folder\2004_01.mdb;DefaultDir=C:\TEMP\New Folder;DriverId=281;FIL=MS Access;MaxBufferSiz" _
), Array("e=2048;PageTimeout=5;")), Destination:=ActiveCell)
.CommandText = Array( _
"SELECT `2004_01`.Field1, `2004_01`.Field2" & Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\New Folder\2004_01`.`2004_01` `2004_01`" & Chr(13) & "" & Chr(10) & "WHERE (`2004_01`.Field2 Like '%4605%')" _
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Application.Goto Reference:="R1C1"
End Sub


Board Regular
Jun 3, 2002
Sorry, I don't understand. most likely my fault. I want to use A1 everytime, without being prompted to input A1 in an Input Box. I was hoping to change this statement "WHERE (`2004_01`.Field2 Like '%4605%')" to something like this "WHERE (`2004_01`.Field2 Like range("a1:a1".value)". I want to be able to do it in VB and not SQL because I don't know SQL and I want to add additional code in VB once I get this one issue figured out. Thanks again.


Feb 17, 2002

That person didn't want a pop-up either. ;)

But just set up the pop intitially, then make the changes in the thread as described, right-clicking on the query and changing the Paramater. Once you do this, the query changes the paramater from the pop-up to the cell you point to.

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...