I am new to macros and am trying to write one which enables me to access
MSQuery file for editing from my worksheet.
The worksheet has to be updated each month so I only need to edit this
criteria of the query.
Any advice would be greatly appreciated

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

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

.

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

I had some time. This is how I setup complicated Queries. You need to
place Carriage Returns and Linefeed every 256 characters (I used
vbcrlf).



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


Sub Macro1()
'

Set Sht = Sheets("sheet1")
AccountNumber = "Customer NBR"
CompanyCode = "950"
LedgerSection = "Business Area"
MonthEndPeriod = "201003"



SelectSQL = "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""," & vbCrLf & _
"A_Customer_Month_End_View_UK.""OverDUe"" 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""," & vbCrLf & _
"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'"

FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK A_Customer_Month_End_View_UK"

WhereSQL1 = "A_Customer_Month_End_View_UK.""Account Number"" ='" & AccountNumber & "'"
WhereSQL2 = "A_Customer_Month_End_View_UK.""Company Code"" ='" & CompanyCode & "'"
WhereSQL3 = "A_Customer_Month_End_View_UK.""Ledger Section"" ='" & LedgerSection & "'"
WhereSQL4 = "A_Customer_Month_End_View_UK.""Month End Period"" ='" & MonthEndPeriod & "'"
WhereSQL = "Where " & WhereSQL1 & " AND " & WhereSQL2 & " AND " & WhereSQL3 & " AND " & WhereSQL4

GroupBy1 = "A_Customer_Month_End_View_UK.""Ledger Section"","
GroupBy2 = "A_Customer_Month_End_View_UK.""Account Number"","
GroupBy3 = "A_Customer_Month_End_View_UK.""Customer Name"","
GroupBy4 = "A_Customer_Month_End_View_UK.""Credit Limit"","
GroupBy5 = "A_Customer_Month_End_View_UK.""Balance

GroupbySQL = "GROUP BY " & GroupBy1 & GroupBy2 & GroupBy3 & _
GroupBy4 & GroupBy5

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

.CommandText = Array(, SelectSQL & vbCrLf & _
FromSQL & vbCrLf & WhereSQL & vbCrLf & GroupbySQL)

.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

Plse ignore my previous post as I sent it before I saw your reply below. I
can see that your code is much tidier than what I had and therefore is much
clearer to follow, somthing I've learnt for the future.

I'm still getting a runtime error 13, mismatch against the following bit of
code;

..CommandText = Array(, SelectSQL & vbCrLf & _
FromSQL & vbCrLf & WhereSQL & vbCrLf & GroupbySQL)

Thanks for all of your help on this



"joel" wrote:


I had some time. This is how I setup complicated Queries. You need to
place Carriage Returns and Linefeed every 256 characters (I used
vbcrlf).




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



Sub Macro1()
'

Set Sht = Sheets("sheet1")
AccountNumber = "Customer NBR"
CompanyCode = "950"
LedgerSection = "Business Area"
MonthEndPeriod = "201003"



SelectSQL = "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""," & vbCrLf & _
"A_Customer_Month_End_View_UK.""OverDUe"" 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""," & vbCrLf & _
"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'"

FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK A_Customer_Month_End_View_UK"

WhereSQL1 = "A_Customer_Month_End_View_UK.""Account Number"" ='" & AccountNumber & "'"
WhereSQL2 = "A_Customer_Month_End_View_UK.""Company Code"" ='" & CompanyCode & "'"
WhereSQL3 = "A_Customer_Month_End_View_UK.""Ledger Section"" ='" & LedgerSection & "'"
WhereSQL4 = "A_Customer_Month_End_View_UK.""Month End Period"" ='" & MonthEndPeriod & "'"
WhereSQL = "Where " & WhereSQL1 & " AND " & WhereSQL2 & " AND " & WhereSQL3 & " AND " & WhereSQL4

GroupBy1 = "A_Customer_Month_End_View_UK.""Ledger Section"","
GroupBy2 = "A_Customer_Month_End_View_UK.""Account Number"","
GroupBy3 = "A_Customer_Month_End_View_UK.""Customer Name"","
GroupBy4 = "A_Customer_Month_End_View_UK.""Credit Limit"","
GroupBy5 = "A_Customer_Month_End_View_UK.""Balance

GroupbySQL = "GROUP BY " & GroupBy1 & GroupBy2 & GroupBy3 & _
GroupBy4 & GroupBy5

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

.CommandText = Array(, SelectSQL & vbCrLf & _
FromSQL & vbCrLf & WhereSQL & vbCrLf & GroupbySQL)

.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

.

When I copy the code into my macro the following part highlights as Compile
Error:Syntax Error.
.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 & """)

If I remove one of the " before '& AccountNumber &' it eliminates the error
message. Not sure if this is correct but it seemed logical due to the odd
number of " in the sequence.
Unfortunately when the Macro runs it stops at the same point showing Type
mismatch error 13.

"joel" wrote:


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

.