Query using SQL backend problems

[ian_w]

I've recently been experimenting with SQL 2005 Express for when we come to move our main db out of access and onto a SQL backend..

I've created a few of our main tables in access and transferred some data into it, im now trying to re-create one of our main queries utilising the SQL tables, im running into an issue though and I have no idea what it means !

Here is the query..

SELECT dbo_PPIN_Physical_PINS.PPIN, dbo_PPIN_Physical_PINS.PPIN_IX, dbo_PPIN_Physical_PINS.PPIN_RoHS, dbo_PPIN_Physical_PINS.PPIN_Manu_Series, dbo_PPIN_Physical_PINS.PPIN_Part_Number, dbo_SDX_Supplier_Details.SDX_Short_Name, MRU_PIN_Finder_Sub_Query.Disty, MRU_PIN_Finder_Sub_Query.OPIN_IX, MRU_PIN_Finder_Sub_Query.OPIN_Disty_PN, dbo_SDX_Supplier_Details.SDX_ID
FROM (dbo_PPIN_Physical_PINS LEFT JOIN MRU_PIN_Finder_Sub_Query ON dbo_PPIN_Physical_PINS.PPIN_IX = MRU_PIN_Finder_Sub_Query.OPIN_PPIN_IX) INNER JOIN dbo_SDX_Supplier_Details ON dbo_PPIN_Physical_PINS.PPIN_Manu_ID = dbo_SDX_Supplier_Details.SDX_ID;

I have created this using Query Designer and it appears to be a carbon copy of the one in the original db which is using access tables.

I am getting the following error..

[Microsoft][ODBC SQL Server Driver][SQL Server]The multi part identifier "MS1.SDX_ID" could not be bound. (#4104)

Anyone able to shed some light on this?

 

[SQL_Hell]

Hi there

Theres nothing in that code that is called MS1.SDX_ID,

Whats MRU_PIN_Finder_Sub_Query?

Sql server view or access query?


if its an access query then you need to revise your design, the point of using sql server as a backend is that all the queries and data should be run at sql server.

 

[ian_w]

Hi there

Theres nothing in that code that is called MS1.SDX_ID,

Whats MRU_PIN_Finder_Sub_Query?

Sql server view or access query?


if its an access query then you need to revise your design, the point of using sql server as a backend is that all the queries and data should be run at sql server.

Thanks for the reply, your mention off running everything off SQL server answered something I had been wondering about.

I now have the query constructed and running on the server, i've then linked to this in access (as a table ), all seems to work exactly as i'd hoped and runs alot quicker than the access version (the outcome I was after)