String Building for Where Clause in Query

[danperfect]

Hi,

I'm hoping someone can help with a very simple problem. I have a
column in excel that contains numbers of 9 digits each. I have an
access table containing these numbers and a lot of additional
information. I am trying to execute a query through code, that will
return this additional data. I have made the query work by going down
each line in excel and opening a new query and pulling back the
information WHERE the article number = a variable containing the
number from excel. This works fine but is slow. What I would like to
do is build a string containing all the article numbers, to act as a
multiple criteria WHERE query. I have had this work when I have used
the numbers directly, but when I try it through code, I get a "Type
mismatch" error as soon as the macro reaches the query. Below is the
string building and query code. Can anyone point out why this is not
working?


saparticle = ActiveCell
criteriastring = "(`Final Output`.Article='" & saparticle & "') "
While ActiveCell.Offset(1, 0) <> ""
ActiveCell.Offset(1, 0).Select
saparticle = ActiveCell
criteriastring = criteriastring & "OR (`Final Output`.Article='" &
saparticle & "') "
Wend
Range("R4").Name = "onedctarget"


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=H:\*******.mdb;DefaultDir=H:
\*****;DriverId=281;FIL=MS Access;MaxBuffe" _
), Array("rSize=2048;PageTimeout=5;")),
Destination:=Range("onedctarget"))
.CommandText = Array( _
"SELECT `Final Output`.Article,`Final Output`.Site,`Final
Output`.Listed, `Final Output`.OneDC" & Chr(13) & "" & Chr(10) &
"FROM
`H:\****`.`Final Output` `Final Output`" & Chr(13)
& "" & Chr(10) & "WHERE " & criteriastring)
.Name = "Query from MS Access Database"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With


Any help would be much appreciated.


Thanks,


Dan