joel said: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:
joel said: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:
joel said: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:
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.