• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Confused by .DBO table names after upsizing

I'm new to SQL

I have upsized an access database and converted it to an access project. The tables and queries migrated to SQL 2000  - No problems.

When I open the Access project on a client PC all of the tables and queries have (dbo) after their names.

I have a problem when I attempt to run reports - they fail to find the source table/query. If I edit the report and change the source from table to dbo.table the report works.

Is this the right thing to do, or should I sort out the table names.

Please be gentle....
0
greenwatch
Asked:
greenwatch
1 Solution
 
DivaqsCommented:
Hi greenwatch,

Within SQL there is what is called a fully qualified name, which includes Server.DB.owner.table.column

"dbo" is an owner, meaning "database owner".

It wouldn't hurt anything to use more of the qualified object name then just the table name. This reduces chances of the wrong object being found and can slightly increase performance.
0
 
greenwatchAuthor Commented:
So then is the right thing to do to go through all the forms/reports and change the source from "query" to "dbo.query/table" ? Or is there a better way? And will the tables/queries always be known as "dbo.query/table" ?

Sorry for all the questions but I want to get this right.
0
 
DivaqsCommented:
Yes, I think it would be good to do both, especially since it involves Access.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
mohit108Commented:
Hi greenwatch

The problem u are facing is directly related to ur connection parameters.

If the tables u have imported are with specific user name, then try to connect ur report tables with same username.

e.g.

Table Name
someuser.table1

now u have connection in reports using some other user (someotheruser)

Now if u change this  (someotheruser) to the real table owner (someuser), then ur problem will go away and u have to change a single line of code (The connection parameter nothing else).

The fully qualified name is Good, but here u have to change a lot in ur code. So try to use this scenerio.

Bye
0
 
greenwatchAuthor Commented:
mohit108 you appear to be going along an interesting line, but i am not totally clear. I am connecting to the database using the id which linked the tables (in your example someuser). I will increase to 250 points if anybody can give me a clear understanding of how to resolve the issue with a one line change.
0
 
greenwatchAuthor Commented:
i started with 100 points and upped it to 250 in the hope of getting a definitive answer. That didn't happen. I'd like to split the original 100 points evenly between Divaqs and mohit108
0
 
moduloCommented:
Dear greenwatch

I've refunded 200 points to enable you to accept the comment for one expert and to post a
"Points for <expertname>" Q for the other expert in this topic area.

Please:
1) Post the link to the original Q in the "Points for <expertname>" and
2) Add in the original Q a comment with the link to the "Points for <expertname>", thus the email notif will warn the expert.

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now