wildcard search a Access DB

[bobaol]

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

 

[NateO]

Hello,

Add your table then, pick-up here:

http://www.mrexcel.com/board2/viewtopic.php?p=385966#385966

Where you're right-clicking on your query...

Once your query is set up as you like, just refresh it, don't keep adding new QueryTable Objects to the Worksheet.

 

[bobaol]

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.

 

[NateO]

Sure,

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.