Problem with Access and SqlServer

[Laura K]

Hi,

I have an access front end which is attached to a remotely hosted sql
server. My lovely database developer wandered off on an extended vacation
for an indefinite period of time without telling me. I am an ASP.net
programmer with experience in Access and database design but I have never
worked with the programming end of Access.


I had a problem in my web application with a number of tables in SQL because
the owner of the table was the user and not dbo. I changed the owner to dbo
in SQL server and fixed the WebPages. Now I have to fix the Access front
end. I have no idea where to do this. Basically I need to go from
ownername.tablename to dbo.tablename somewhere in Access. Can someone point
me in the right direction or the right newsgroup. I do not even know where
the coding is located in Access.

HELP!

Laura K

 

[Arvin Meyer]

Look at whatever naming convention the programmer used for the rest of the
tables. It is probably something like tblSomething and SQL-Server is
prefixing it to dbo_tblSomething. Just rename it back to the way the others
are. It should be OK after that.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Sylvain Lafontaine]

For Forms, you must set the "Record Source Qualifier" property in the Data
tab to dbo .

S. L.

 

[Laura K]

Actually naming is not the issue. The naming conventions are the same
regardless of Access or SQL server.

Example tblProducts

I learned today that because we did not have SP 3 for SQL server installed
when we upsized the Access database to SQL the owner of each table in SQL
was username and not dbo. I now have to change each and every table in SQL
to dbo as the owner. When I do this the tables in SQL server are no longer
linked to the original tables in access. I have no idea how to relink them
to the same tables with different owners.

I am slowly learning what went wrong but not how to resolve it.

Laura K

 

[Laura K]

Currently the record source qualifier for forms is set to the table name
from which the form is named without the owner or the user.

Example tblProducts

Are you suggesting that I change it from tblProducts to dbo.tblProducts?

Laura K

 

[Arvin Meyer]

Ok, now it's a bit more clear what your problem is. Delete each link to
SQL-Server until there are no more connections. Compact the database (Tools
.... Database Utilities ... Compact and Repair) The choose File ... Get
External Data ... Link. Choose the link type (probably ODBC) and set up a
new connection.

Now your table names will probably read "dbo_tblProducts" and you'll need to
rename them back to "tblProducts", etc. As Sylvain mentioned, make sure the
Record Source Qualifier in the table properties is set to dbo.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Laura K]

Thanks more questions - perhaps stupid newbie questions.

How do I delete the links. Remember the person who actually did the
database is not here. I am doing this blind.

Laura

Merry Christmas by the way.

 

[Rick Brandt]

Thanks more questions - perhaps stupid newbie questions.

How do I delete the links. Remember the person who actually did the
database is not here. I am doing this blind.

Click on each one, then press the <Delete> key.

 

[Laura K]

Ok really stupid here. Where are the links that I am supposed to click on
then delete. I have tables and forms in my access front end.

Laura

 

[Rick Brandt]

Ok really stupid here. Where are the links that I am supposed to
click on then delete. I have tables and forms in my access front end.

If you see a table in your list of tables that has a black arrow icon next to it
then that is not a physical table located in the front end file, but a link.
Further, if it has a globe icon instead of a standard table icon then it is an
ODBC link.

 

[Laura K]

All tables have the globe icon. From what I understand I should delete the
tables. Then Follow Arvin's Instructions below...Is this correct.


Arvin's Instructions:

"Compact the database (Tools ... Database Utilities ... Compact and Repair)
The choose File ... Get External Data ... Link. Choose the link type
(probably ODBC) and set up a
new connection.

Now your table names will probably read "dbo_tblProducts" and you'll need to
rename them back to "tblProducts", etc. As Sylvain mentioned, make sure the
Record Source Qualifier in the table properties is set to dbo. "

 

[Brendan Reynolds]

Yes. Access will prompt for confirmation before deleting the link. If the
table is a linked table, the confirmation prompt will say 'Do you want to
remove the link to the table '<table name>'. If it is a local table, the
confirmation prompt will say 'Do you want to delete the table '<table
name>'. (At least, that is what it says in Access 2003. The exact wording
may differ in other versions, but to the best of my memory all versions make
the distinction.) So if you're still concerned about deleting the wrong
thing, check those confirmation prompts. And of course it's always wise to
make a backup copy of the MDB before deleting anything.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.