ADO SQL Query - Multi Users, Schema Name Required?

[bs0d]

I created an Excel workbook that uses ADO query a database on our SQL server:

    Set myConn = New ADODB.Connection
    Set myRS = New ADODB.Recordset

    strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=db_server_name;INITIAL CATALOG=database_name;INTEGRATED SECURITY=sspi;"

    myConn.Open strConn
    myRS.Open sSQL, myConn, adOpenStatic, adLockReadOnly, adCmdText

I have an ODBC connection for the "database_name". I'm able to query the database just fine without using prefacing each table name reference with the schema name. However, when another user executes queries in the workbook, they get the "invalid object name" error - and it specifies the table name ad invalid. If I update the code to xxx.table_name (where xxx = the schema), the error goes away.

If they're using the same credentials to access the SQL database, why would they be seeing this error? Google search results indicate many need to CTRL+SHIFT+R the IntelliSense in SQL Serve Management Studio-- But I don't have access to that. This is all through Excel. Is there a time period that must pass before IntelliSense is refreshed on its own? Anyone else work through this unique experience?

 

[bs0d]

I created an Excel workbook that uses ADO query a database on our SQL server:

    Set myConn = New ADODB.Connection
    Set myRS = New ADODB.Recordset

    strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=db_server_name;INITIAL CATALOG=database_name;INTEGRATED SECURITY=sspi;"

    myConn.Open strConn
    myRS.Open sSQL, myConn, adOpenStatic, adLockReadOnly, adCmdText

I have an ODBC connection for the "database_name". I'm able to query the database just fine without using prefacing each table name reference with the schema name. However, when another user executes queries in the workbook, they get the "invalid object name" error - and it specifies the table name ad invalid. If I update the code to xxx.table_name (where xxx = the schema), the error goes away.

If they're using the same credentials to access the SQL database, why would they be seeing this error? Google search results indicate many need to CTRL+SHIFT+R the IntelliSense in SQL Serve Management Studio-- But I don't have access to that. This is all through Excel. Is there a time period that must pass before IntelliSense is refreshed on its own? Anyone else work through this unique experience?

What I found was that INTEGRATED SECURITY-sspi means to use Windows authentication. So regardless of how your ODBC is setup, the code runs Windows authentication to access the data. Further, only the owner of the schema may reference tables without the schema name. If another user is executing a query, the schema is required. INTEGRATED SECURITY-SSPI is recommended, but I suppose this post should provide some clarity on invalid object name errors an schema.