472,988 Members | 3,278 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,988 software developers and data experts.

Weird Left Join error when moving to SQL

1,266 Expert 1GB
I have some code that works fine in an all Access environment. I am moving the back-end to SQL Server 2012. After moving the back-end I am getting this error when I run a particular query.

ODBC--Call Failed
[Microsoft][SQLServer Natrive Client 10.0][SQL Server]The multi-part identifier "ro.RoleID" could not be bound. (#4104) [Microsoft][SQL Server Native Client 10.0][SQL Server]The multi-part identifier "ro.RoleID" cound not be bound.(#4104)

The error repeats itself, just like that. The text is slightly different when I use the SQL Server driver instead of the Native Client but the reported error is the same.

The query I want to run is PermittedPrograms_qry. PermittedPrograms_qry should be a join of the dbo_sysPermissions_tbl table to AllProgramRoles_qry on AppID, RoleID and ProgramID. In this case, I am trying to find what programs are not permitted, so I need to do a Left Join. INNER JOIN works fine, no problem. Left Join works in some cases and not in others.

There is some weird problem joining to the dbo_sysRoles_tbl table. AllProgramRoles_qry works fine by itself but when the error happens it seems to point back to a problem in that query.

These joins work:
1 Left join, RoleID
2 left joins, programID and AppID, missing RoleID
3 INNER Joins

These joins do not work and result in the error I showed above:
all 3 left joins
2 Left Joins, RoleID, ProgramID, missing APPID left join
2 Left Joins, AppID and RoleID, missing ProgramID left join

Note that joining on RoleID by itself works okay.

I've tried this, using both the SQL Native Client driver and the SQL Server driver. I've also tried it in SQL 2008 mode (I have tried this with both COMPATIBILITY_LEVEL = 100) and 2012 mode(COMPATIBILITY_LEVEL = 110).

Below you will find the SQL code for the various joins I've described. Note that all ID fields are integers.

Thanks for any advice you may have.

The SQL for the AllProgramRoles_qry is here. Note the "ro." that appears in the error messages. This query works fine by itself, and in some combinations below.
Expand|Select|Wrap|Line Numbers
  1. SELECT pr.ProgramID, pr.AccessType, pr.AppID, ro.RoleID
  2. FROM dbo_sysPrograms_tbl AS pr, dbo_sysRoles_tbl AS ro
  3. WHERE (((pr.Menu_YN)=True));
  4.  
DOES NOT WORK
all 3 left joins
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID) AND (apr.RoleID = per.RoleID);
  3.  
DOES NOT WORK
2 Left Joins, RoleID, ProgramID, missing APPID left join
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.RoleID = per.RoleID) AND (apr.ProgramID = per.ProgramID);
  3.  
DOES NOT WORK
2 Left Joins, AppID and RoleID, missing ProgramID left join
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.RoleID = per.RoleID);
  3.  
DOES WORK
1 Left join, RoleID
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON apr.RoleID = per.RoleID;
  3.  
2 left joins, programID and AppID, missing RoleID
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID);
  3.  
3 INNER Joins
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID
  2. FROM AllProgramRoles_qry AS apr INNER JOIN dbo_sysPermissions_tbl AS per ON (apr.RoleID = per.RoleID) AND (apr.AppID = per.AppID) AND (apr.ProgramID = per.ProgramID);
  3.  
  4.  
Interestingly enough, this code "works" in that it does not return an error, but nor does it return the correct recordset.
Expand|Select|Wrap|Line Numbers
  1. SELECT apr.ProgramID, apr.AccessType, apr.AppID, apr.RoleID, Nz([per].[RoleID],-1) AS Expr1
  2. FROM AllProgramRoles_qry AS apr LEFT JOIN dbo_sysPermissions_tbl AS per ON (apr.ProgramID = per.ProgramID) AND (apr.AppID = per.AppID)
  3. WHERE (((Nz([per].[RoleID],-1))<>[apr].[RoleID]));
  4.  
Dec 18 '15 #1
5 1708
zmbd
5,501 Expert Mod 4TB
I've not gone thru all of your SQL yet; however, most common issues are reserved words for table and/or field names, mis-spelled table/field name, extra or missing spaces in the SQL string, incorrect placement of the alias (if used).... and... I'm sure I'm missing one or two more causes.

NeoPa had something like this too>
home > topics > microsoft sql server > questions > microsoft sql server native client 11.0 error #4104
wonder if this could be related??
Dec 18 '15 #2
jimatqsi
1,266 Expert 1GB
Thanks for plowing through all that and pointing me to a similar report. In NeoPa's case, I would be suspicious of the VBA function calls, as I could imagine the query engine in SQL might try to find that in the SQL library.

I wonder if those calls could be prefixed with "vba." to eliminate the problem in that instance. But other than the use of a home-grown function within his query, his case looks a lot like my own, so I doubt it.

I'm going to look again for possible keyword conflicts. I already tried renaming RoleID and that did not help. I've struggled with avoiding the joined queries/subquery idea but I don't see how that's gonna happen without building a temp table.

Thank you again!
Dec 18 '15 #3
jforbes
1,107 Expert 1GB
The only thing that I can suggest it to take the parenthesis out of the Joins as you don't need them and their processing by Access or SQL or both might be causing your error.

What happens when you paste these queries into SQL Server Management Studio?
Dec 19 '15 #4
jimatqsi
1,266 Expert 1GB
jforbes, that was a good suggestion. I rewrote the query I wanted in SQL and it works as it should. So I guess it's a problem on the Access side. It works like that and is probably faster.

I don't NEED to fix it now, but it bothers me a lot to not know what is going on and when it might happen again. Maybe there's some other driver I could try? Or some change I could make to the table connection string? Here is a typical one:
ODBC;Description=IBEW SQL MS ODBC 11 driver;DRIVER=ODBC Driver 11 for SQL Server;SERVER=xxx.xx.xx.xxx;UID=AccessUser;PWD=*** ****;APP=Microsoft Office 2013;DATABASE=IBEWG;StatsLog_On=Yes;
Dec 22 '15 #5
Rabbit
12,516 Expert Mod 8TB
I use the "SQL Server Native Client xx.x" drivers that come when they install SQL Server Management Studio.
Dec 22 '15 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

13
by: StealthBananaT | last post by:
My database has two tables - films has 10,000 records and reviews has 20,000 records. Whenever I try to list all the films and the count of its reviews, MySQL locks and I have to restart the...
2
by: michael | last post by:
Gotta post because this is driving me nuts. Trying to DELETE orphans. I can successfully: SELECT GroupID FROM Groups LEFT JOIN Users ON UsersID = UserID WHERE UsersID IS NULL; but when I...
9
by: Alan Lane | last post by:
Hello world: Background: Yesterday, January 21, Doug Steele was kind enough to help me out on a Left Join problem. I was trying to return all stores and their Gross Adds for December, 2004...
5
by: ElGordo | last post by:
In our dev environment, our ASP.Net application works fine and outside users can access it when provided with the proper URL. Once we moved the application to our public server, we now get a...
3
by: eswanson | last post by:
I have a site that has multiple web site projects. I got it working on my windows xp sp2 computer in pre-compiled mode. I created a new site on a windows 2003 server and copied the pre-compiled...
0
by: yeahuh | last post by:
Quick and dirty version. Godaddy server using MySQL 4.0.24 Iím trying a left join to obtain idís in table A(cars) that are NOT in car_id in table B(newspaper): *This is a cut down version...
4
by: phytorion | last post by:
i'm working in access2003 trying to bring three large tables(sys_sysobjects, sys_syscolumns, and dbo_pskeydefn) into one table filtered on by fieldname of a fourth table(table_name_master) ...
6
by: prodsd | last post by:
Here is my challenge . I have a new syatem That when I save a Adobe Illustrator file to my desktop it is ok but when I try to move or delete the file I get a error key that is open ??? I never had...
9
by: jmarcrum | last post by:
i need some help i have a table with 5 city divisions 1 = D1 2 = D2 3 = D3 4 = D4 and
6
by: ipskalsi | last post by:
I have a table Multiple user at same time perform multiple operation on that table like insert , update , fetch etc. During fetching records i use many left outer join which decrease the...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.