This is much too complicated to tackle all at once. The code below is
returning all the columns from the table (Select *) and filtering on 1
parameter the AccountNumber. If this works we will add a little bit
more each time. I'm using Sheet1 which you can change as needed and I
made the AccountNumber = "Customer NBR"




VBA Code:
--------------------


Sub Edit_DMS_Query()
'
' Edit_DMS_Query Macro
' Macro recorded 27/04/2010 by markbyfo
'
' Keyboard Shortcut: Ctrl+e
'
Set Sht = sheets("sheet1")
AccountNumber = "Customer NBR"

with Sht
.cells.RemoveSubtotal
with .QueryTables.Add(Connection:= "ODBC;DRIVER=SQLServer;" & _
"SERVER=usoldt-as-056;UID=dms_uk;;" & _
"APP=Microsoft Office 2003;WSID=UKEDG-L41655;" & _
"DATABASE=dms_reporting", _
Destination:=.Range("A5"))

.CommandText = Array(, "SELECT * " & _
"FROM dms_reporting.dms_uk.A_Customer_Month_End_View_UK A_Customer_Month_End_View_UK " & _
"WHERE A_Customer_Month_End_View_UK.""Account Number"" =""" & AccountNumber & """)

.Name = "Query from DMS UK Production Month End"
.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
End With
End Sub

--------------------


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?u=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=198559

http://www.thecodecage.com/forumz