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