.....WHERE `Section 4 list`.ID=" &
Thisworkbook.Worksheet(1).Range("A1').Value
Change the range to match your requirements.

But do you need to use code to create the whole query every time (and delete
the previous one) if you are only changing the value of the one parameter ?
That's why I suggested the previous method.

NickHK

"monkey harry" wrote in message
oups.com...
Thanks, can't get it to work though.

Here is my code. It opens a new access query and will filter it
according to what is used as "ID". This is the part I'm struggling
with as I want it to ask the user to input the ID:

' Open query
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=G:\My
Documents\db3.mdb;DefaultDir=G:\My Documents;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;Page" _
), Array("Timeout=5;")), Destination:=Range("F11"))
.CommandText = Array( _
"SELECT `Section 4 list`.ID, `Section 4 list`.FullName,
`Section 4 list`.FlatRoom, `Section 4 list`.HouseName, `Section 4
list`.HouseNumber, `Section 4 list`.StreetName, `Section 4
list`.TownName, `Sec" _
, _
"tion 4 list`.PostCode" & Chr(13) & "" & Chr(10) & "FROM `G:\My
Documents\db3`.`Section 4 list` `Section 4 list`" & Chr(13) & "" &
Chr(10) & "WHERE (`Section 4 list`.ID=1)" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub