Many thanks for your help and I know I'm taking liberties now but I have
recorded the macro as suggested (2) but had to do it in 2 parts as not all of
the data recorded(??)
This is what I came up with but it shows syntax errors. Any ideas as I'm lost?
Sub Edit_DMS_Query()
'
' Edit_DMS_Query Macro
' Macro recorded 27/04/2010 by markbyfo
'
' Keyboard Shortcut: Ctrl+e
'
Selection.RemoveSubtotal
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=usoldt-as-056;UID=dms_uk;;APP=Microsoft Office
2003;WSID=UKEDG-L41655;DATABASE=dms_reporting" _
, Destination:=Range("A5"))
.CommandText = Array( _
, _
"SELECT A_Customer_Month_End_View_UK.""Ledger Section"",
A_Customer_Month_End_View_UK.""Account Number"",
A_Customer_Month_End_View_UK.""Customer Name"",
A_Customer_Month_End_View_UK.""Credit Limit"",
A_Customer_Month_End_View_UK.""Balance"", A_Customer_Month_End_View_UK.""Over
DUe"" AS 'Overdue', A_Customer_Month_End_View_UK.""Not Yet Due"",
A_Customer_Month_End_View_UK.""Falling Due"",
A_Customer_Month_End_View_UK.""Past Due 1"",
A_Customer_Month_End_View_UK.""Past Due 2"",
A_Customer_Month_End_View_UK.""Past Due 3"",
A_Customer_Month_End_View_UK.""Past Due 4"",
A_Customer_Month_End_View_UK.""Past Due 5"",
A_Customer_Month_End_View_UK.""Unallocated"",
A_Customer_Month_End_View_UK.""In Query"",
A_Customer_Month_End_View_UK.""Forward Dated"",
Sum(A_Open_Items_Month_End_View_UK.amount) AS 'Sum of Amount', _
, _
A_Open_Items_Month_End_View_UK.""Report Fiscal Date"",
Count(A_Open_Items_Month_End_View_UK.Query) AS 'Count of Query'" & Chr(13) &
"" & Chr(10)
, _
& "FROM dms_reporting.dms_uk.A_Customer_Month_End_View_UK
A_Customer_Month_End_View_UK,
dms_reporting.dms_uk.A_Open_Items_Month_End_View_U K
A_Open_Items_Month_End_View_UK" & Chr(13) & "" & Chr(10) _
, _
& "WHERE A_Customer_Month_End_View_UK.""Account Number"" =
A_Open_Items_Month_End_View_UK.""Customer NBR"" AND
A_Customer_Month_End_View_UK.""Company Code"" =
A_Open_Items_Month_End_View_UK.""Company Code"" AND
A_Customer_Month_End_View_UK.""Ledger Section"" =
A_Open_Items_Month_End_View_UK.""Business Area"" AND
A_Customer_Month_End_View_UK.""Month End Period"" =
A_Open_Items_Month_End_View_UK.""Month End Period""" & Chr(13) & "" & Chr(10)
_
, _
& "GROUP BY A_Customer_Month_End_View_UK.""Ledger Section"",
A_Customer_Month_End_View_UK.""Account Number"",
A_Customer_Month_End_View_UK.""Customer Name"",
A_Customer_Month_End_View_UK.""Credit Limit"",
A_Customer_Month_End_View_UK.""Balance"", A_Customer_Month_End_View_UK.""Over
DUe"" AS 'Overdue', A_Customer_Month_End_View_UK.""Not Yet Due"",
A_Customer_Month_End_View_UK.""Falling Due"",
A_Customer_Month_End_View_UK.""Past Due 1"",
A_Customer_Month_End_View_UK.""Past Due 2"",
A_Customer_Month_End_View_UK.""Past Due 3"",
A_Customer_Month_End_View_UK.""Past Due 4"",
A_Customer_Month_End_View_UK.""Past Due 5"",
A_Customer_Month_End_View_UK.""Unallocated"",
A_Customer_Month_End_View_UK.""In Query"",
A_Customer_Month_End_View_UK.""Forward Dated"",
Sum(A_Open_Items_Month_End_View_UK.amount) AS 'Sum of Amount', _
, _
A_Open_Items_Month_End_View_UK.Report Fiscal Date"",
Count(A_Open_Items_Month_End_View_UK.Query) AS 'Count of Query'" & Chr(13) &
"" & Chr(10) _
, _
AND ((A_Customer_Month_End_View_UK.""Company Code""='950') AND
(A_Customer_Month_End_View_UK.""Month End Period""='201003'))")

.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 Sub

"joel" wrote:


You have 3 different methods you can use

1) Manually create a query using the Excel menus. This type of query is
fix (can't change parameters) except using the Query editor from the
worksheet menues. Allows only reading of a database or excel file.


2) Record a macro while performing a query (use as a template). Then
modify the macro to give you more flexibility by making parameter
variables. The variable can be entered using inputboxes, listboxes, or
cells in the worksheet. Allows on reading or writing a database or
excel file.


3) Use ADO method to connect to a database (or excel file) and read or
write to the database using SQL.


--
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

.