MSQuery query changed in Excel

Question

I have a query that runs in an Excel Macro that has been changed and I can't figure out how to change the code in the macro. The original query is:

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ "ODBC;DSN=MS Access Database;DBQ=" & strpath & ";DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout" _ ), Array("=5;")), Destination:=range("A1")) .CommandText = Array( _ "SELECT 8MR Final.Plan, 8MR Final.Area, 8MR Final.Track, 8MR Final.1 MR Report.Segment, 8MR Final.Seg Begin, 8MR Final.Seg End, 8MR Final.Track Category, 8MR Final.Component, 8" _ , _ "MR Final.ID, 8MR Final.Defect Desc, 8MR Final.Meas, 8MR Final.Def Begin Sta, 8MR Final.Def End Sta, 8MR Final.Rail, 8MR Final.Standard, 8MR Final.Work Action, 8MR Final.Quanti" _ , _ "ty, 8MR Final.UM, 8MR Final.Unit Cost, 8MR Final.Ext Cost, 8MR Final.Comments" & Chr(13) & "" & Chr(10) & "FROM " & strpath & ".8MR Final 8MR Final" _ )

The new query has joins in it and a parameter. Can it be run in a Macro? How do I change it to run in the macro. New query from access is:

SELECT [1 MR Report].Plan AS PlanDesc, [1 MR Report].Area, [1 MR Report].Track, [1 MR Report].Segment, [1 MR Report].[Seg Begin], [1 MR Report].[Seg End], [1 MR Report].[Track Category], [1 MR Report].Component, [7Meas].ID, [1 MR Report].[Defect Desc], [7Meas].Meas, [1 MR Report].[Def Begin Sta], [1 MR Report].[Def End Sta], [1 MR Report].Rail, [1 MR Report].Standard, [1 MR Report].[Work Action], [1 MR Report].Quantity, [1 MR Report].UM, [1 MR Report].[Unit Cost], [1 MR Report].[Ext Cost], [7Meas].Comments FROM [1 MR Report] LEFT JOIN 7Meas ON ([1 MR Report].Segment=[7Meas].Segment) AND ([1 MR Report].Defect=[7Meas].Defect) AND ([1 MR Report].[Def Begin Sta]=[7Meas].Loc) WHERE ((([1 MR Report].Key)=[userplankey])) ORDER BY [1 MR Report].Plan, [1 MR Report].Area, [1 MR Report].Track, [1 MR Report].Segment, [1 MR Report].[Seg Begin], [1 MR Report].Component, [7Meas].ID, [1 MR Report].[Defect Desc], [1 MR Report].[Def Begin Sta], [1 MR Report].[Work Action];

I have figured out how to get the parameter from the spreadsheet and place it in a global variable, but am having trouble converting the query. I need it to run the same way if possible. Thanks in advance for any help and suggestions.

Lora

Answer 1

Try to do it in pieces.

Start with one field in the SELECT clause and the FROM clause. See if you can get that to work. If not, reduce the joins in the FROM clause until you can get it to work and then add each join back in individually.

Then add each field back in to the SELECT clause until you have all of them, then add conditions in the WHERE clause, and leave the ORDER BY clause for last.

Also, consider aliasing the tables in the FROM clause so it's easier to read.