multi-part identifier could not be bound (#4104)

[LoserName]

Hello!



I'm getting an error when attempting a query: multi-part identifier could not be bound (#4104).

My goal: I need to return records for users from a query that are not found in another query.

First, let me state that I have no control over any of the tables referenced and they are stored in SQL Server with an ODBC connection to Access.

The first query returns records of what are known as internal users from a table that contains both internal and external users:

SELECT PCutSQL_tbl_user.user_id, PCutSQL_tbl_user.card_number, PCutSQL_tbl_user.user_name, PCutSQL_tbl_user.full_name
FROM PCutSQL_tbl_user INNER JOIN PCutSQL_tbl_user_group ON PCutSQL_tbl_user.user_id = PCutSQL_tbl_user_group.user_id
WHERE (((PCutSQL_tbl_user.deleted)="N") AND ((PCutSQL_tbl_user_group.group_id)=5014));

The second query returns records of users that belong to shared accounts from a table that contains both shared and personal accounts:

SELECT PCutSQL_tbl_user.user_id, PCutSQL_tbl_user_account.account_id
FROM (PCutSQL_tbl_user INNER JOIN PCutSQL_tbl_user_account ON PCutSQL_tbl_user.user_id = PCutSQL_tbl_user_account.user_id) INNER JOIN PCutSQL_tbl_account ON PCutSQL_tbl_user_account.account_id = PCutSQL_tbl_account.account_id
WHERE (((PCutSQL_tbl_account.account_type)="SHARED"));

And finally, I have an unmatched query to return users that are internal and not a member of any shared accounts:

SELECT qryPrintUsersInternal.user_id, qryPrintUsersInternal.card_number, qryPrintUsersInternal.user_name, qryPrintUsersInternal.full_name
FROM qryPrintUsersInternal LEFT JOIN qryPrintUserAccountDirect ON qryPrintUsersInternal.[user_id] = qryPrintUserAccountDirect.[user_id]
WHERE (((qryPrintUserAccountDirect.user_id) Is Null));

This final query returns the following error:


I'm sure I could get this working if I created temp tables but I don't think that should be necessary and I'd rather learn the right way. I have found other posts on this error but they didn't seem similar enough for me to figure out my problem. Any help would be greatly appreciated!

[LoserName]

Nobody wants to tackle this one?

[ItsMe]

Maybe put square brackets around the table names. [PCutSQL_tbl_user]

[June7]

I wonder why the query engine is converting first underscore in PCutSQL_tbl_user to period.

[LoserName]

I wonder why the query engine is converting first underscore in PCutSQL_tbl_user to period.

Huh. I didn't notice that... Good catch! Now what can be done about it?

[ItsMe]

Huh. I didn't notice that... Good catch! Now what can be done about it?

Maybe you could employ brackets?

[LoserName]

Maybe you could employ brackets?

Where am I supposed to put brackets exactly? The first two queries run perfectly and the query that throws the error does not directly reference the tables.

This does nothing:

SELECT qryPrintUsersInternal.[user_id] AS orphanID, qryPrintUsersInternal.[card_number] AS orphanCard, qryPrintUsersInternal.[user_name] AS orphanUsername, qryPrintUsersInternal.[full_name] AS orphanName
FROM qryPrintUsersInternal LEFT JOIN qryPrintUserAccountDirect ON qryPrintUsersInternal.[user_id] = qryPrintUserAccountDirect.[user_id]
WHERE (((qryPrintUserAccountDirect.[user_id]) Is Null));

[ItsMe]

If you add the brackets to the table names within the first two queries and save the queries, does Access remove them? I would try to add the brackets to the names of the tables.

If that does not help, I would then start looking into creating a pass through query and using the pass through queries as subqueries.

[LoserName]

Yes, Access removes the brackets.

[LoserName]

When I convert the queries to pass-through, it asks for my data source every time and when I select it, I get this error:


Now this is weird...
In both the Navigation Pane and in the Linked Table Manager, it shows and underscore between PCutSQL and the table name:


But when I look at the ODBC objects in Import and Link, there is a period:

[ItsMe]

Hmmm, you should be able to use the actual table names since it is a pass through. Within your pass through query try typing the name as PCutSQL.tbl_user rather than PCutSQL_tbl_user.

As for the security thing, you will have to use the same credentials that your linked tables are using.

The only other thing I can think of and I do not think it will help, would be to use alias names for your tables within the SLECT statement of the first two queries. But, I think the error is happening when the subqueries are using the SQL engine.

So maybe something like ...

SELECT a.user_id, a.card_number, a.user_name, a.full_name
FROM PCutSQL_tbl_user AS a INNER JOIN PCutSQL_tbl_user_group ON a.user_id = PCutSQL_tbl_user_group.user_id
WHERE (((PCutSQL_tbl_user.deleted)="N") AND ((PCutSQL_tbl_user_group.group_id)=5014));

[LoserName]

Hmm, no it still asks for the data source and gives the ODBC error.

I also attempted aliases for every table and still got the "could not be bound" error.

Sorry I didn't respond sooner- I've been out a few days...

[ItsMe]

Hmm, no it still asks for the data source and gives the ODBC error.
...

If it is asking for the data source it is probably not the same error. So you will need to connect to the server before you can use the Pass Through query. Looking at one of your screenshots it appears your tables are linked. So the connection string for the query will look like the strings for your tables. If you include the name of your database in the string your table names within your query will probably look like (MPSGroupManagers or tbl_account). If you get the connection string correct and the table name wrong the error should tell you that the table name is wrong.

Here are some examples of connection strings.

you can a put something like the following into the Connection String Property of a passthrough query. Take a look at one of your linked tables and check the Description property. All of this stuff is available via the property sheet while in design view.

 ODBC;DRIVER={SQL Server Native Client 11.0};SERVER=tcp:ServerName.domain.local,1433;Database=DataBaseName;UID=UserName;Pwd=P@$$W0RD;Encrypt=Yes

You can also change the connection property of a pass through query using code ...

'Programatically adjust the ODBC Connection String
'of a passthrough query named Query1
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strConnect As String

strConnect = "ODBC;" & _
             "Driver={SQL Server Native Client 11.0};" & _
             "Server=tcp:ServerName.domain.local,1433;" & _
             "Database=DataBaseName;" & _
             "Uid=UserName;" & _
             "Pwd=P@$$W0RD;" & _
             "Encrypt=yes;" & _
             "Connection Timeout=30;"
             
             
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query1")
    qdf.Connect = strConnect
    
    Set qdf = Nothing
    Set db = Nothing

[lbrannon]

I know this post is over a year old, but I'm having this exact same issue with no luck finding a solution. Were you able to get this working?

Hello!

I'm getting an error when attempting a query: multi-part identifier could not be bound (#4104).

My goal: I need to return records for users from a query that are not found in another query.

First, let me state that I have no control over any of the tables referenced and they are stored in SQL Server with an ODBC connection to Access.

The first query returns records of what are known as internal users from a table that contains both internal and external users:

SELECT PCutSQL_tbl_user.user_id, PCutSQL_tbl_user.card_number, PCutSQL_tbl_user.user_name, PCutSQL_tbl_user.full_name
FROM PCutSQL_tbl_user INNER JOIN PCutSQL_tbl_user_group ON PCutSQL_tbl_user.user_id = PCutSQL_tbl_user_group.user_id
WHERE (((PCutSQL_tbl_user.deleted)="N") AND ((PCutSQL_tbl_user_group.group_id)=5014));

The second query returns records of users that belong to shared accounts from a table that contains both shared and personal accounts:

SELECT PCutSQL_tbl_user.user_id, PCutSQL_tbl_user_account.account_id
FROM (PCutSQL_tbl_user INNER JOIN PCutSQL_tbl_user_account ON PCutSQL_tbl_user.user_id = PCutSQL_tbl_user_account.user_id) INNER JOIN PCutSQL_tbl_account ON PCutSQL_tbl_user_account.account_id = PCutSQL_tbl_account.account_id
WHERE (((PCutSQL_tbl_account.account_type)="SHARED"));

And finally, I have an unmatched query to return users that are internal and not a member of any shared accounts:

SELECT qryPrintUsersInternal.user_id, qryPrintUsersInternal.card_number, qryPrintUsersInternal.user_name, qryPrintUsersInternal.full_name
FROM qryPrintUsersInternal LEFT JOIN qryPrintUserAccountDirect ON qryPrintUsersInternal.[user_id] = qryPrintUserAccountDirect.[user_id]
WHERE (((qryPrintUserAccountDirect.user_id) Is Null));

This final query returns the following error:


I'm sure I could get this working if I created temp tables but I don't think that should be necessary and I'd rather learn the right way. I have found other posts on this error but they didn't seem similar enough for me to figure out my problem. Any help would be greatly appreciated!

[ItsMe]

@lbrannon
I see more than one issue in this thread. Perhaps you can start a new thread and identify what issue you are having. You can also link to this thread as reference.