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

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

.

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

.

I copied this from the code you posted (recorded Macro). I just tried
similar code on one of my databases and got the same error. You somehow
got any extra comma in your recorded macro

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

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

Here are some hints in writing a query
1) The query is simply a string. the command text portion of the string
is the SQL statements.
2) A SQL can be very long (I think 32,000 characters), but need to be
have a carriage return every 256 characters or less.
3) Make sure you have a delimiter (space, comma, return) between in part
of the SQL. for example

Don't do this
Select *From (I left out the space between the * and FROM).

4) The line continuation character (the underscore at the end of each
line) isn't seen by the SQL server. The SQL server see 2 lines in my
code as one long string. So don't leave out the Delimiters or returns
thinking the line continuation character is seperating the fileds of the
SQL.

5) Develope the SQL in small pieces to make it easier to to debug.


--
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 can see where that came from my original code, it was because of a line
break. I checked the rest of the code to see if there were any additional
issues and picked up just one in a text field (OverDUe should be Over DUe).

I've removed the comma and the space in the Array details but the same error
message is being returned and I am stuck again (but it is stretching my
knowledge of macros).

"joel" wrote:


I copied this from the code you posted (recorded Macro). I just tried
similar code on one of my databases and got the same error. You somehow
got any extra comma in your recorded macro

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

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

Here are some hints in writing a query
1) The query is simply a string. the command text portion of the string
is the SQL statements.
2) A SQL can be very long (I think 32,000 characters), but need to be
have a carriage return every 256 characters or less.
3) Make sure you have a delimiter (space, comma, return) between in part
of the SQL. for example

Don't do this
Select *From (I left out the space between the * and FROM).

4) The line continuation character (the underscore at the end of each
line) isn't seen by the SQL server. The SQL server see 2 lines in my
code as one long string. So don't leave out the Delimiters or returns
thinking the line continuation character is seperating the fileds of the
SQL.

5) Develope the SQL in small pieces to make it easier to to debug.


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

.

Start with a small SQL and then make it larger.You only need a Select
and From.

.CommandText = Array("SELECT *" & vbCrLf & FromSQL)

Get above working first working first. Then add to the SQL little by
litle until ou get the full SQL string.


The SELECT : Are the columns that get returned
The From: The table in the database
The Whe The filtering which returns theonly rows specified
The Groupby : the Sort order of the returned tables.


I assume you are still getting the Error 13. The query doesn't get
process until the Refresh line (last line of the query). If you get an
error in the Refresh line it indicats soemthing is wrong with teh SQL
statements.

The Error message are very vague. Sometimes it is better to use the ADO
method to connect to a database where the error messages have more
detail.

I tired your code yesterday morning and got past the error 13 by
removing the comma. I only used a SELECT and From portion of the 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

I tried .CommandText = Array("SELECT *" & vbCrLf & FromSQL) and got past the
Error 13 but it stopped with an ODBC error on the Refresh line.

I changed to .CommandText = Array(SelectSQL & vbCrLf & FromSQL) and got as
far as
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"","
With no Error 13 (but still the ODBC error on the refresh line)

When I added the next line so the code was
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"","
The Error 13 returned

By the way, I notice the 'From' statement only mentions the Customer month
end view. Do I need to add the open item month end view as well?


"joel" wrote:


Start with a small SQL and then make it larger.You only need a Select
and From.

.CommandText = Array("SELECT *" & vbCrLf & FromSQL)

Get above working first working first. Then add to the SQL little by
litle until ou get the full SQL string.


The SELECT : Are the columns that get returned
The From: The table in the database
The Whe The filtering which returns theonly rows specified
The Groupby : the Sort order of the returned tables.


I assume you are still getting the Error 13. The query doesn't get
process until the Refresh line (last line of the query). If you get an
error in the Refresh line it indicats soemthing is wrong with teh SQL
statements.

The Error message are very vague. Sometimes it is better to use the ADO
method to connect to a database where the error messages have more
detail.

I tired your code yesterday morning and got past the error 13 by
removing the comma. I only used a SELECT and From portion of the 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

.

I've included a lot of debug steps below. Read my instruction
carefully. I included my plan in getting this query working and want
you to attempt to find and fix the problems yourself. Otherwise, it may
take a week before we get everything working. I also want you to learn
by yourself your own techniques for debugging macros. third, I want to
build up your confidince in troubleshooting macro problems. I won't
always be there to help. Ask questions as you move along.

I think you are right about the 2nd table in the From. I think we need
to make this change

from:
FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK
A_Customer_Month_End_View_UK"

To:
FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK, " & _
"dms_reporting.dms_uk.A_Open_Items_Month_End_View_ UK"


But hold off on this for a little bit. Notice on the old SQL
A_Customer_Month_End_View_UK is shown twice. This is becaue the macro
created an Alias saying instead of using the the Database name
(dms_reporting.dms_uk) then a period, and then the table name
(A_Customer_Month_End_View_UK), the alias say you can use just the table
name. An alias is simply a shortcut.

Somethins like this : Database.Table Table where the second table is
the alias name. So further in the SQL you could just specify the short
alias name. I eliminated the alais to make the code easy to understand.
When you start adding formulas to the SQL like the SUM and COUNT your
may need to use the Alias. You also probably need the alias if you are
refering to more than one table. I don't often get an SQL this
complicated and it usually takes me a number of tries before I get it
right. I find some slight differences when using SQL with a microsoft
database and a SQL server. And some SQL servers will except certain
statements and others won't. SQL statements aren't 100% the same
between different SQL Servers or Database programs.


---------------------------------------------------------------------------
1)
We need to get past the error at the Refresh line before we proceeed any
further. I want you to record a new macro since you did some editing on
the last recorded macro that may be causing problems. When you record
the new macro on the 1st menu open one of the tables in the left side
window by opening up the plus sign. Then select one column of the table
(under the plus sigh you opened up) and use the right arrow to slide
this column to the right side window. Then press next until you get to
the last menu and then press Finish. Then stop recording. Delete any
portion of the macro after the REFRESH statement.
2) Your data should be on one worksheet of the workbook. I want you to
select a 2nd sheet of the workbook. Then run the recorded macro to make
sure it works.
3) Copy the working macro so in case there are problems we canm always
go back to something that works.
4) You should also when running the macro while debugging the code in my
instructions below either create a new worksheet everytime you run the
macro, or delete the data retured from the macro before running the
macro a 2nd time.

-----------------------------------------------------------------------
Here is the macro I recorded doing the same process. I used a databae
on my PC. You are using a SQL server where the code is a little bit
different.



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


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/29/2010 by Joel
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\TEMP\submission.mdb;DefaultDir=C:\ TEMP;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Submissions.Task_ID" & Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\submission`.Submissions Submissions" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

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





---------------------------------------------------------------------
I want you to make the same changes I made below (but use your table and
database from the recorded macro
From
.CommandText = Array( _
"SELECT Submissions.Task_ID" & Chr(13) & "" & Chr(10) & "FROM
`C:\TEMP\submission`.Submissions Submissions" _
)
to
.CommandText = Array("SELECT *" & vbCrLf & FromSQL)


I also want you to put the following at the beginning of your macro
FromSQL = "FROM `C:\TEMP\submission`.Submissions"

Notice I took out the second Submission from the recorded macro which is
the alias. I'm trying to make the code as simple as possible. the
filename should be from your recorded macro (not what I have posted).


This should now work without any errors.

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

Next I want you to build the Select portion of the SQL one item at a
time

So 1st change the Command text

from
.CommandText = Array("SELECT *" & vbCrLf & FromSQL)
to
.CommandText = Array(SelectSQL & vbCrLf & FromSQL)

And place the following at the beginning of the macro

SelectSQL = "SELECT A_Customer_Month_End_View_UK.""Ledger Section"""

If this works then add a 2nd item to the Select


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


SelectSQL = "SELECT A_Customer_Month_End_View_UK.""Ledger Section""," & _
"A_Customer_Month_End_View_UK.""Account Number"""

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




then a 3rd item


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


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

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




Notice each time I remove the last comma, underline and the amphersand
until you get the entire Select portion.

If you get an error during any of the additions. I want you to record a
new macro and select only the table and column where the error occured.
the compare the new recorded macro with the SQL to see if there are any
differences. Remember you ned to add a VBCRLF at least once for every
256 characters in a line.

If you are brave you can add a few more select items at one time rather
than add one at a time. Make sure you completed run the macro past the
refresh line each tiome you add more items to the SQL.

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

You can do the same with the WHERE and Groupby portion of the SQL. let
me know where you start having problems. I don't have access to your
SQL server so I can't do this myself.


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

Major breakthrough today. I have actually got the macro to run (on limited
data) so can now build it up bit by bit as you suggest.
I will let you know how it develops over the next couple of days.

"joel" wrote:


I've included a lot of debug steps below. Read my instruction
carefully. I included my plan in getting this query working and want
you to attempt to find and fix the problems yourself. Otherwise, it may
take a week before we get everything working. I also want you to learn
by yourself your own techniques for debugging macros. third, I want to
build up your confidince in troubleshooting macro problems. I won't
always be there to help. Ask questions as you move along.

I think you are right about the 2nd table in the From. I think we need
to make this change

from:
FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK
A_Customer_Month_End_View_UK"

To:
FromSQL = "From dms_reporting.dms_uk.A_Customer_Month_End_View_UK, " & _
"dms_reporting.dms_uk.A_Open_Items_Month_End_View_ UK"


But hold off on this for a little bit. Notice on the old SQL
A_Customer_Month_End_View_UK is shown twice. This is becaue the macro
created an Alias saying instead of using the the Database name
(dms_reporting.dms_uk) then a period, and then the table name
(A_Customer_Month_End_View_UK), the alias say you can use just the table
name. An alias is simply a shortcut.

Somethins like this : Database.Table Table where the second table is
the alias name. So further in the SQL you could just specify the short
alias name. I eliminated the alais to make the code easy to understand.
When you start adding formulas to the SQL like the SUM and COUNT your
may need to use the Alias. You also probably need the alias if you are
refering to more than one table. I don't often get an SQL this
complicated and it usually takes me a number of tries before I get it
right. I find some slight differences when using SQL with a microsoft
database and a SQL server. And some SQL servers will except certain
statements and others won't. SQL statements aren't 100% the same
between different SQL Servers or Database programs.


---------------------------------------------------------------------------
1)
We need to get past the error at the Refresh line before we proceeed any
further. I want you to record a new macro since you did some editing on
the last recorded macro that may be causing problems. When you record
the new macro on the 1st menu open one of the tables in the left side
window by opening up the plus sign. Then select one column of the table
(under the plus sigh you opened up) and use the right arrow to slide
this column to the right side window. Then press next until you get to
the last menu and then press Finish. Then stop recording. Delete any
portion of the macro after the REFRESH statement.
2) Your data should be on one worksheet of the workbook. I want you to
select a 2nd sheet of the workbook. Then run the recorded macro to make
sure it works.
3) Copy the working macro so in case there are problems we canm always
go back to something that works.
4) You should also when running the macro while debugging the code in my
instructions below either create a new worksheet everytime you run the
macro, or delete the data retured from the macro before running the
macro a 2nd time.

-----------------------------------------------------------------------
Here is the macro I recorded doing the same process. I used a databae
on my PC. You are using a SQL server where the code is a little bit
different.




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



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/29/2010 by Joel
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DSN=MS Access Database;DBQ=C:\TEMP\submission.mdb;DefaultDir=C:\ TEMP;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Submissions.Task_ID" & Chr(13) & "" & Chr(10) & "FROM `C:\TEMP\submission`.Submissions Submissions" _
)
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub


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





---------------------------------------------------------------------
I want you to make the same changes I made below (but use your table and
database from the recorded macro
From
.CommandText = Array( _
"SELECT Submissions.Task_ID" & Chr(13) & "" & Chr(10) & "FROM
`C:\TEMP\submission`.Submissions Submissions" _
)
to
.CommandText = Array("SELECT *" & vbCrLf & FromSQL)


I also want you to put the following at the beginning of your macro
FromSQL = "FROM `C:\TEMP\submission`.Submissions"

Notice I took out the second Submission from the recorded macro which is
the alias. I'm trying to make the code as simple as possible. the
filename should be from your recorded macro (not what I have posted).


This should now work without any errors.

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

Next I want you to build the Select portion of the SQL one item at a
time

So 1st change the Command text

from
.CommandText = Array("SELECT *" & vbCrLf & FromSQL)
to
.CommandText = Array(SelectSQL & vbCrLf & FromSQL)

And place the following at the beginning of the macro

SelectSQL = "SELECT A_Customer_Month_End_View_UK.""Ledger Section"""

If this works then add a 2nd item to the Select



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



SelectSQL = "SELECT A_Customer_Month_End_View_UK.""Ledger Section""," & _
"A_Customer_Month_End_View_UK.""Account Number"""


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




then a 3rd item



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



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


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




Notice each time I remove the last comma, underline and the amphersand
until you get the entire Select portion.

If you get an error during any of the additions. I want you to record a
new macro and select only the table and column where the error occured.
the compare the new recorded macro with the SQL to see if there are any
differences. Remember you ned to add a VBCRLF at least once for every
256 characters in a line.

If you are brave you can add a few more select items at one time rather
than add one at a time. Make sure you completed run the macro past the
refresh line each tiome you add more items to the SQL.

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

You can do the same with the WHERE and Groupby portion of the SQL. let
me know where you start having problems. I don't have access to your
SQL server so I can't do this myself.


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

.

Great News. I hate doing SQL with Queries because the error messagge
are so vague. I either use the ADO to connect to a database (error
messages are a little better, only a little). the other trick I use
which may not be very easy in your SQL is to use the Query Editor. Once
You get your simple query you can use the Query Editor by selecting any
cell where data has been returned. Then go to the Data menu and you
will see the edit query option enabled. In the query editor there is a
SQL button that opens a new window. Yo can acutall type in any query
you want and the eidtor will tell you if your SQL is valid and return
data into the window.


--
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 have finally got the macro working. I had to break it back out into the
lengthier code as I was having trouble identifying errors using the compacted
SelectSQL & GroupSQL.
I am in the process of adding an InputBox so that the month end period can
be updated when required by the user.
It has taken hours of work to get here but it does feel good.
I couldn't have done it without your help (I probably would have just given
up on it as a bad idea) so thank you for you time & patience

"joel" wrote:


Great News. I hate doing SQL with Queries because the error messagge
are so vague. I either use the ADO to connect to a database (error
messages are a little better, only a little). the other trick I use
which may not be very easy in your SQL is to use the Query Editor. Once
You get your simple query you can use the Query Editor by selecting any
cell where data has been returned. Then go to the Data menu and you
will see the edit query option enabled. In the query editor there is a
SQL button that opens a new window. Yo can acutall type in any query
you want and the eidtor will tell you if your SQL is valid and return
data into the window.


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

.