ADP Object naming convention problem

ADP Object naming convention problem

Post by CSDun » Fri, 30 Aug 2002 06:10:00



Hello,
I have an Access 2000 Project connected to a SQL Server 2000 database that I
created in which when some other user logs on to my machine or another
machine, '(dbo)' shows up at the end of the names of the tables, views, and
stored procedures.

I first came across this when I started the project, and the users of the
project were not able to pull up reports in the Project. Upon clicking
buttons to pull up Access Project Reports, they would get messages that
expressed, "The record source "(object name)" specified on this form or
report does not exist.". My boss ran into the same problem, and when we
looked at the Access Project window under his log in, would see (dbo) beside
the table, view and stored procedure object names, but I did not see this
under my log in.

The version of Access 2000 on my machine showed that I had SR-1A installed
as well as SP-2, but my boss did not. When we installed SR-1A and SP-2 on
his machine, the (dbo) affix went away, and the Project then worked okay for
my boss. Before we deployed this particular project, I ran the same updates
on the machines where the users using the Project were having the same
problem. Installing SR-1A and SP-2 seemed to fix the problem for them as
well.

However, today, when a different user logs in on my machine and opens the
project, the same error messages appear when clicking buttons to open
reports, and the objects in the Project window have the '(dbo)' affix on
them.

I thought this was a software, machine specific problem, but it seems there
must be some sort of directory permissions problem or something. All of the
users who are using the Project without this problem have the same
permissions in SQL Server 2000 as the ones who have the problem.

If you have any ideas on this or need more information, please let me know.
Thanks.

CSDunn

 
 
 

ADP Object naming convention problem

Post by Ananda Si » Fri, 30 Aug 2002 07:14:28


Hi CS,

My commiserations. I've had the sting as well. I've not taken the time to
document how it happens or why, but from my readings,

1. every table, view etc... in SQL Server has an implicit prefix - the user
account who created the object. You may not "see" this prefix depending on
what software you use - Access .adp, Enterprise Manager whatever..

2. when you are db_owner equivalent, instead of the actual account, the
prefix dbo is automatically used as prefix.

3. why or when this occurs is something that confuses me no end. It is
highly probable as you describe that if you use a db_owner account to create
the object and then let a non db_owner account connect to the database that
dbo. will automatically appear. Because that non owner account could
potentially create objects like Fred.Mytable as opposed to using a table
called dbo.Mytable - both are independent tables and allowed to exist.

4. I am surprised that you are using Access 2000 to create objects with SQL
Server 2000 - I am also in an Access 2000 delivery environment but Access
2000 can't do much with SQL Server 2000 (on my machine anyway - I have
patched it with the latest dialogs etc... because it precedes SQL Server
2000 and everytime I open a table in design view - those blasted dialogs
tell me that some things don't work) I have Access 2002 to create objects
and Access 2000 to produce deliverables.

HTH
Ananda


Quote:> Hello,
> I have an Access 2000 Project connected to a SQL Server 2000 database that
I
> created in which when some other user logs on to my machine or another
> machine, '(dbo)' shows up at the end of the names of the tables, views,
and
> stored procedures.


 
 
 

ADP Object naming convention problem

Post by Mary Chipma » Sat, 31 Aug 2002 00:32:09


--In SQLS, you can have two tables named Table1 as long as they have
different owners. You refer to them as Fred.Table1 or dbo.Table1 using
the ownername.objectname syntax.

--When a sysadmin (system admistrator) creates an object, it is owned
by dbo, not by their user name.

--Access 2000 requires you to fully qualify references using
ownername.object name UNLESS you are logged on as a system
administrator. In that case, just the object name is sufficient. Since
nobody in full possession of their senses wants users to have sysadmin
privileges...

--You need to fully-qualify all references in your forms and code if
you expect anyone other than system administrators to run your app.

Access 2002 handles this better by hiding this--Open a Profiler trace
and you'll see what's going on.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446



Quote:>Hello,
>I have an Access 2000 Project connected to a SQL Server 2000 database that I
>created in which when some other user logs on to my machine or another
>machine, '(dbo)' shows up at the end of the names of the tables, views, and
>stored procedures.

>I first came across this when I started the project, and the users of the
>project were not able to pull up reports in the Project. Upon clicking
>buttons to pull up Access Project Reports, they would get messages that
>expressed, "The record source "(object name)" specified on this form or
>report does not exist.". My boss ran into the same problem, and when we
>looked at the Access Project window under his log in, would see (dbo) beside
>the table, view and stored procedure object names, but I did not see this
>under my log in.

>The version of Access 2000 on my machine showed that I had SR-1A installed
>as well as SP-2, but my boss did not. When we installed SR-1A and SP-2 on
>his machine, the (dbo) affix went away, and the Project then worked okay for
>my boss. Before we deployed this particular project, I ran the same updates
>on the machines where the users using the Project were having the same
>problem. Installing SR-1A and SP-2 seemed to fix the problem for them as
>well.

>However, today, when a different user logs in on my machine and opens the
>project, the same error messages appear when clicking buttons to open
>reports, and the objects in the Project window have the '(dbo)' affix on
>them.

>I thought this was a software, machine specific problem, but it seems there
>must be some sort of directory permissions problem or something. All of the
>users who are using the Project without this problem have the same
>permissions in SQL Server 2000 as the ones who have the problem.

>If you have any ideas on this or need more information, please let me know.
>Thanks.

>CSDunn

 
 
 

1. Secure ADP, Distribute ADP and copy ADP

Ok...I have a couple of questions that revolve around the project I am
working on.

1.  Do I need to have separate ADP files for different users (i.e.
different logons)?
2.  I am setting up the users and their rights in the SQL Enterrpise
Manager - how to do I make them effective via an ADP?
3.  I need to distribute this ADP to about 10 users - and I have NO
access to their network except remote connection - how do I deploy
this to the users?
4.  I need to copy the ADP and ALL of database work I have done to
this server via email (if possible) and that includes the databases
files, etc.  Help?

--Matt

2. HELP Security in VB4: no read permission on table

3. A2000 adp project reference another A2000 adp project

4. 32588-TN-East Tennessee-ORACLE-Project Management-Manufacturing Applications-Oracle Development Team Leader

5. Access 2000 adp project reference another A2000 adp project

6. new SP3 bug

7. MD-Bethesda-266318--Object Oriented Design-Object Oriented Devel.-Java-CORBA-ORA

8. US-OH-ORACLE ADMINISTRATOR / DBA

9. VA-McLean-241246--CORBA-Object Oriented Design-ORACLE-Senior Object Modeler/Architect

10. SQL Server Copy Object Task: include dependent objects

11. Error Assigning Task object to Custom Task object while calling DTS APIs in VB.NET

12. VA-Ashburn-242117--Object Oriented Design-Object Oriented Devel.-ORACLE-Life Cyc

13. system objects vs. user objects