Pass-Through Query works in SQL Server, won't work in Access? Lost

[SoleAris]

Hi everyone,

My company has decided to stick with MS Access as the Front-End UI for our basic needs with the backend being held on a local SQL Server.

I am in the process of writing pass-through queries that I will then build forms and reports from within the UI and am having some trouble getting the query just right on some of these. Since Access doesn't highlight or point out where your invalid syntax is, I have been using NaviCat to help get me through writing these (as I am no expert with T_SQL).

With this particular query, I have gotten it to run in NaviCat just fine, however when copying the query and attempting it as a pass-through query in MS Access to build the frontend I am given an error of:

----------------------------------------------------------------------------------------
ODBC --call failed.

Invalid object name 'T_ReportCodes'. (#208)
----------------------------------------------------------------------------------------

What is going on here? I was hoping that I could take the majority of my existing queries that we have developed in Access and copy their code into the new frontends and run them as pass-through queries with no problem.

Any suggestions? Here is the query in question below:


SELECT IVM_ITEMMAST.ITEM_NUMBER AS [PartNo], T_ReportCodes.Description, T_AdditionalInfo.Phase, T_AdditionalInfo.Volt, T_AdditionalInfo.Depth, T_AdditionalInfo.ShipLength, T_AdditionalInfo.ShipWidth, T_AdditionalInfo.ShipHeight, ([T_AdditionalInfo].[ShipHeight]*[T_AdditionalInfo].[ShipWidth]*[T_AdditionalInfo].[ShipLength])/1728 AS ShipCuFt, T_AdditionalInfo.ShipWeight
FROM T_ReportCodes INNER JOIN (IVM_ITEMMAST INNER JOIN T_AdditionalInfo ON IVM_ITEMMAST.ITEM_NUMBER = T_AdditionalInfo.ItemNo) ON T_ReportCodes.ReportCode = IVM_ITEMMAST.MNTH_SLS_RPT_CD
WHERE (((T_AdditionalInfo.Volt)=115 Or (T_AdditionalInfo.Volt)=208 Or (T_AdditionalInfo.Volt)=240) AND ((T_AdditionalInfo.Active)='Yes'))
ORDER BY T_ReportCodes.ReportCode, IVM_ITEMMAST.ITEM_NUMBER;




Thanks for any advice/help!

 

[Galaxiom]

It is saying that T_ReportCdes does not exist in the specified database on the SQL Server.

Why not save yourself the trouble and simply link the tables? There is not a lot to gain by using PassThrough anyway. The Access engine already passes what it can to the SQL server engine when using linked tables.

Writing PassThrough queries without a sound knowledge of TSQL would be a recipe for grief.

 

[SoleAris]

So why are pass-throughs used in Access then? I was under the impression that keeping the tables completely out of our front-end was the idea here. Obviously it would be much easier just to have linked tables and then use our already existing front-end then?

What potential drawbacks are there to using linked tables within the Access front-end?

 

[SoleAris]

I also am still wondering in the specified query above why it works in NaviCat just fine but not in Access. I have checked and there is a T_ReportCodes table present and in NaviCat it clearly recognizes the table, yet in Access it can't find it?

 

[bmal]

Why not save yourself the trouble and simply link the tables? There is not a lot to gain by using PassThrough anyway. The Access engine already passes what it can to the SQL server engine when using linked tables.

.

Here is a description of why I use pass-through queries. My Access database links to a SQL database. One SQL table in particular is north of 256 fields. I happen to want to report on one of those fields. So I use a pass through to pluck two or three fields out using the pass through query.

It may not be the wise way to solve that situation, but it was all I had in the toolbox.

 

[Galaxiom]

So why are pass-throughs used in Access then? I was under the impression that keeping the tables completely out of our front-end was the idea here.

Linked tables are not "in" the front end as such.

Obviously it would be much easier just to have linked tables and then use our already existing front-end then?

Definitely.

What potential drawbacks are there to using linked tables within the Access front-end?

Any user can see all the tables.

 

[Galaxiom]

Here is a description of why I use pass-through queries. My Access database links to a SQL database. One SQL table in particular is north of 256 fields. I happen to want to report on one of those fields. So I use a pass through to pluck two or three fields out using the pass through query.

It may not be the wise way to solve that situation, but it was all I had in the toolbox.

If you have rights to the SQL Server, an alternative would be to create a View and link to that.

Unless the table is a data warehouse, so many fields usually indicates a bad design.

 

[bmal]

Unless the table is a data warehouse, so many fields usually indicates a bad design.

Good point, Galaxiom. See, if I complain too loudly about this *achoo* industry software *gesundheit*, they might take my data out of a warehouse and make Matzo Ball Soup out of it. . So instead I walk into a padded room and scream. Just kidding. It's all good, and the software works very well.

All kidding aside, I will read about SQL views and see what I can do with that.

 

[Galaxiom]

One thing I do suggest with Views and Stored Procedures that are put inside someone else's back end.

Create a separate Schema for your stuff. This makes it clear who it belongs to.