Edit Query

[M. Byford]

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

 

[M. Byford]

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

 

[M. Byford]

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.

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:

 

[M. Byford]

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

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:

 

[M. Byford]

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

 

[M. Byford]

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?

 

[M. Byford]

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.

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.

VBA Code:

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

'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"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:

 

[M. Byford]

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