Multiple LEFT JOIN query fighting SQL Server -> Access port (1 Viewer)

[mdlueck]

Greetings,

I have the following query working well via the SQL Server Management Studio

SELECT [p].[id], [p].[authid], [p].[prodteamid], [pt].[teamname], [p].[subsystypeid], [pss].[title], [p].[buyercodetypeid], [pbc].[title], [p].[stocktypeid], [ps].[title], [p].[mfgmethtypeid], [pmm].[title], [p].[cmdtycodetypeid], [pcc].[title], [p].[quoteid], [p].[partnumber], [p].[title], [p].[rev], [p].[ver], [p].[econumber], [p].[qtyper], [p].[eau], [p].[stopworkflg], [p].[stdcost], [p].[supplierchampion], [p].[comments]
FROM [dbo].[parts] AS [p]
  LEFT JOIN [dbo].[partsbuyercodetype] AS [pbc] ON [p].[buyercodetypeid] = [pbc].[id]
  LEFT JOIN [dbo].[partscmdtycodetype] AS [pcc] ON [p].[cmdtycodetypeid] = [pcc].[id]
  LEFT JOIN [dbo].[partsmfgmethtype] AS [pmm] ON [p].[mfgmethtypeid] = [pmm].[id]
  LEFT JOIN [dbo].[partsstocktype] AS [ps] ON [p].[stocktypeid] = [ps].[id]
  LEFT JOIN [dbo].[partssubsystype] AS [pss] ON [p].[subsystypeid] = [pss].[id]
  LEFT JOIN [dbo].[prodteam] AS [pt] ON [p].[prodteamid] = [pt].[id];

It is putting up a fight porting it to Access.

"Syntax error (missing operator) in query expression 'p.buyercodetypeid = pbc.id
LEFT JOIN dbo_partscmdtycodetype AS pcc ON p.cmdtycodetypeid = pcc.i'."

So I paired it WAY back... to just the parts table. This works:

SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, p.stocktypeid, p.mfgmethtypeid, p.cmdtycodetypeid, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p;

I added one LEFT JOIN, works...

SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, pbc.title AS pbctitle, p.stocktypeid, p.mfgmethtypeid, p.cmdtycodetypeid, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p
  LEFT JOIN dbo_partsbuyercodetype AS pbc ON p.buyercodetypeid = pbc.id;

I add an additional LEFT JOIN, BANG!!!

SELECT p.id, p.authid, p.prodteamid, p.subsystypeid, p.buyercodetypeid, pbc.title AS pbctitle, p.stocktypeid, ps.title, p.mfgmethtypeid, p.cmdtycodetypeid, pcc.title AS pcctitle, p.quoteid, p.partnumber, p.title, p.rev, p.ver, p.econumber, p.qtyper, p.eau, p.stopworkflg, p.stdcost, p.supplierchampion, p.comments
FROM dbo_parts AS p
  LEFT JOIN dbo_partsbuyercodetype AS pbc ON p.buyercodetypeid = pbc.id
  LEFT JOIN dbo_partscmdtycodetype AS pcc ON p.cmdtycodetypeid = pcc.id;

Suggestions please?

 

[mdlueck]

Or the other option...

If someone could suggest how to download records from a Pass Through query into a local Access table, that would work as well. I was able to paste in the full SQL and execute it via Pass Through mode.

However, I have not found a way to do an INSERT to a local Access table from a Pass Through query.

I would prefer to use Pass Through queries, it is just I have not found a way to be able to use a Pass Through query and direct that query's output to an Access table in the FE DB.

 

[mdlueck]

I came across this page:

"Create tables from the results of a pass-through query (MDB)"
http://office.microsoft.com/en-us/a...-of-a-pass-through-query-mdb-HP005188053.aspx

Which works with GUI query objects. I will attempt to do the same via VBA and ADODB objects.

 

[mdlueck]

It would appear that I must use GUI query objects and not VBA ADODB code to accomplish what I need. I am fine with that alternative.

However, next challenge... Setting a DSN-less connection for a Query object appears to need a different Rx than the one for setting DSN-less connections for Linked Tables / "PASS-THROUGH" tables.

In the ADOX.Catalog object, queries in the FE DB reside in the Procedures branch vs Tables reside in the Tables branch. To update the linked tables, I needed both an ADOX.Catalog object and an ADOX.Table object.

There does not seem to be a corresponding ADOX.Procedure type to modify things under the Procedures branch of the ADOX.Catalog object.

Suggestions?

And as I type... I realize this really now belongs in the VBA group as well... thus I guess I must cross post...