none
Access Project (ADP) Record Source Qualifier RRS feed

  • Question

  • So, I have an Access 2007 ADP Project connecting to SQL Cloud Database, which is working fine. The problem I'm running into is that if any object is added to the SQL Database using a different owner as "dbo" than all reports stop using the Record Source Qualifier entered. This causes all fields in the existing reports to be unknown. I have to go through every report and change the Recordsource from "xxxxx" to "dbo.xxxxxxxx" and the Record Source Qualifier is ignored. Why?? What is the point of the Record Source Qualifier in Reports if it is not used.

    As a last note, any form/report that uses a base table and not a query or stored procedure as a Record Source appears to work fine.

    I guess things like this explain why Microsoft discontinued support for Access Projects.

     

    Dweezil

    Tuesday, May 3, 2016 3:05 PM

Answers

  • Thanks, but, going through SQL Profiler may not necessarily indicate what is wrong with Access. So, I guess I have my answer. I'll have to go through all reports / forms and adjust the RecordSource property to now say "dbo.xxxxx" or change the owner of all SQL object in my database to be the same. What a pita!

    Dweezil

    Wednesday, May 4, 2016 1:17 PM

All replies

  • Hi Programmer Guy,

    Are you using Access Object Model? or you are performing above mentioned operation using User Interface?

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 4, 2016 3:52 AM
    Moderator
  • I'm using the Access 2007 Report Designer interface. The properties of the report have a stored procedure as the Recordsource and "dbo" as the Record Source Qualifier. But, as I mentioned, if the database has multiple objects with different owners the Record Source Qualifier doesn't appear to work. WHY?

    All fields in the report design indicate as unknown.


    Dweezil

    Wednesday, May 4, 2016 11:49 AM
  • That dbo is not longer necessarily the owner in SQL Server. It's the schema where your object is added to. And a schema, even the dbo schema can have a different owner. So the question is: have you tried to specify the owner instead of the schema name?

    And do you have the necessary permissions to read the others owners objects?

    Wednesday, May 4, 2016 11:58 AM
  • Sorry very confused. I have an SQL Database with Tables, Views and Stored Procedures. Most items were created by the SA account and as such are assigned to "dbo" as the owner. Some new Stored Procedures were created by another SQL user and as such now have an owner other than "dbo". All of these SQL Object are accessible for Read/Write.  How do you find the schema of a stored procedure?

    You say "So the question is: have you tried to specify the owner instead of the schema name?" how do you do that in Access? Isn't that the Record Source Qualifier in the Report design properties?? Microsoft's help defines it as "a String indicating the SQL Server owner name of the record source for the specified form or report". So is that the schema? Do you have an Access Project (ADP) connected to SQL database and looked at the properties of a Report?


    Dweezil

    Wednesday, May 4, 2016 12:14 PM
  • As you're working with a cloud SQL Server I assume it's an actual SQL Server.

    The RecordSourceQualifier is the owner of the table or view. When take a look at an actual T-SQL statement:

    SELECT * 
    FROM dbo.qryJobsCompletedNEW;

    This dbo in the T-SQL statement is the schema to which the query belongs. The schema and the owner are to different things. See CREATE SCHEMA:

    Beginning with SQL Server 2005, the behavior of schemas changed. As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In such databases you must instead use the new catalog views. The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL).

    Thus it maybe a permission problem. Cause it is possible, that you cannot read the table definition.

    Or it maybe just an outdated functionality. So you may test it by specifying the actual owner, not the schema where to object is created.

    Wednesday, May 4, 2016 12:33 PM
  • This is not a permission issue as I can read the table and Store procedures definitions. I'll just mention that if all objects in the SQL Database come from the same owner (dbo) then the RecordSource and Record Source Qualifier function as expected and all data bound controls in a report design show normally. I really do not want to edit all my reports that use a stored procedure to a select statement that gets all fields from my stored procedure.

    You say "So you may test it by specifying the actual owner, not the schema where to object is created." isn't that what the Record Source Qualifier is for?


    Dweezil

    Wednesday, May 4, 2016 12:45 PM
  • That is what the qualifier was for. Because before SQL Server 2005 owner and schema (dbo) where the same. But since SQL Server 2005 they are separate things.

    As the help tells us the RecordSourceQualifier needs the owner, this can imho mean:

    a) you really need to specify the owner. So this maybe different from dbo.

    b) the doc was not updated and also the code behind that functionality in Access. But as SQL Server evolved it is possible, that the column discovery functionality in Access is just broken.

    c) it maybe an permission problem due to the kind of outdated discovery.

    An approach would be using SQL Profiler to see what is going wrong here.

    Wednesday, May 4, 2016 1:09 PM
  • Thanks, but, going through SQL Profiler may not necessarily indicate what is wrong with Access. So, I guess I have my answer. I'll have to go through all reports / forms and adjust the RecordSource property to now say "dbo.xxxxx" or change the owner of all SQL object in my database to be the same. What a pita!

    Dweezil

    Wednesday, May 4, 2016 1:17 PM
  • Yes, this may be the way to go.

    Wednesday, May 4, 2016 1:21 PM