When is dbo prefix required? (1 Viewer)

[Awesomo]

Hi
I'm using an ADP in Access 2010.
I wanted to check the content of some tables, and added this code to a form:

DoCmd.OpenView Me.txtView, acViewNormal, acReadOnly

So if type PrintLog into the txtView text box, it opens the PringLog view. PrintLog is just an example view, this code will work for any of my views. Which is useful as I want to debug on a server that only has runtime Access. This button will be removed in the live version of the ADP.

One thing has come up though. For other users, it won't work if they type PrintLog. The users have to enter dbo.PrintLog, otherwise they get a message "Cannot find the object PrintLog"
Note that dbo.PrintLog or just PrintLog work for me. This happens irrespective of the PC or server that Access is on, and whether if runtime or full Access.

Any ideas on why this would be?
In particular, I wish to know what the Record Source and Record Source Qualifier should be for the forms and reports. For a report, should it be dbo.PrintLog or just Printlog?

Thanks

 

[pbaldy]

ADP's never really took off, so you may be waiting a while to get an answer from somebody who uses them. I use SQL Server all the time, but never felt ADP's were the way to go. I'd offer this:

DoCmd.OpenView "dbo." & Me.txtView, acViewNormal, acReadOnly

which will add the dbo to the beginning for you.

 

[Awesomo]

Thanks Paul.
I should have been clearer above - I'm not really concerned about that piece of code not working.
Its more that it might relate to another issue I have. Some reports are running very slow in Access 2010. And I was wondering if having the dbo in Record Source would speed things up. Or slow it down!

 

[The_Doc_Man]

Have the person looking at this problem call up a code window so that you can check the order of appearance of references. The one that contains the "dbo" prefixes can be moved down in reference order to prevent an override of the definition of the PrintLog method from wherever it is normally defined.

 

[Awesomo]

The_Doc_Man - I don't understand what you mean. PrintLog won't be in the code window, as its just a View. Do you mean click on Tools/References in the Access code window?

 

[The_Doc_Man]

OK, is DBO the name of a database or the name of an applications library? I may have gotten confused here.

 

[Awesomo]

No bother. I think I asked the wrong question in the first place.
Some background first.
In a standard mdb, you specify a Record Source for your report.
For a report in ADP, its slightly different. As you can see in the attachment, you have to specify a Record Source and Record Source Qualifier.
In the attachment, I have

• Record Source = dbo.qryR_AuditChanges
• Record Source Qualifier = dbo

However, it would also work for my logon if I had left the dbo prefix out of the RecordSource

• Record Source = qryR_AuditChanges
• Record Source Qualifier = dbo

Based on what happened with my Docmd.OpenView code, I want to know if I should leave the dbo in or take it out of the record source for my reports.
Looking online, I get conflicting statements, I see recommendations for adding the dbo prefix . But also code for removing the dbo prefix!

I hope I've made things clearer. Though I'll settle for "not as confusing"!

 

[pbaldy]

Doc, in SQL Server "dbo" is typically the owner of a database or object (table, view, etc). You can have up to a 4 part naming convention:

ServerName.DatabaseName.Owner.ObjectName

Apparently in an ADP you sometimes have to specify the owner, thus dbo.TableName instead of just TableName.

 

[The_Doc_Man]

Thanks, Paul. I don't have any SQL Server databases. Never ran into that before.

 

[Awesomo]

Thanks everyone for chiming in here.
I've figured out the issue, and it ties into another problem I had as well.
See http://www.access-programmers.co.uk/forums/showthread.php?t=284480 where I mention the report is running slow for other users.

It relates to SQL security. If a user has sysadmin rights, he does not the dbo prefix.
For my other issue, the reports run quickly under Access 2010 if the user has sysadmin rights.

To give a user sysadmin rights, you setup the user in SQL Server under Security/Logins and tick sysadmin under Server Roles.

Unfortunately, this gives the user permissions on all databases, not just the backend of the ADP. Will post a new question for advice on getting around that issue.
But at least now I know the cause!