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:\SUPPLY\STEVE\SteveH\Dan`.`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

 

[Candyman]

Build the SQL as Query String & Criteria
ie
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(1,2,3,4,5)

The (12,3,4,5) is actually your Criteria field , which can be built by this
funtion:



Function BuildListForAccessString(StartAddress)
Dim ListString As String
Dim xTemp
Dim x
x = 0
Range(StartAddress).Activate
xTemp = LTrim(RTrim(ActiveCell.Value))
Do Until xTemp = ""
If x > 0 Then ListString = ListString & ", " & xTemp Else ListString =
xTemp
ActiveCell.Offset(1, 0).Activate
x = x + 1
xTemp = LTrim(RTrim(ActiveCell.Value))
Loop
BuildListForAccessString = (ListString)
End Function

Try it. put 1,2,3 in rows 1-3 and run:
Sub get1()
MsgBox (BuildListForAccessString("A1"))
End Sub


So your final query will be :
"SELECT Article,Site,Listed, OneDC FROM
`H:\SUPPLY\STEVE\SteveH\Dan`.`Final Output` `Final Output` WHERE Article
in(" & Function BuildListForAccessString(StartAddress) & ")"
HTH
Carl

 

[Candyman]

By the way, you don't need all that extra baggage in default settings:
just list the important ones
.. . .

..RefreshStyle = xlOverwriteCells
..Refresh BackgroundQuery:=False
End With