Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit Query
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Query Wizard Cannot Be Used To Edit This Query | Excel Discussion (Misc queries) | |||
why does edit query button not open query | Excel Discussion (Misc queries) | |||
MS Query - unable to edit query | Excel Discussion (Misc queries) | |||
Problem with MS Query - can't edit query | Excel Discussion (Misc queries) | |||
Edit Query from Excel will not open query in MSQuery | Excel Programming |