SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Migrating Access to SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bdowdy
Starting Member

USA
3 Posts

Posted - 01/13/2014 :  09:14:05  Show Profile  Reply with Quote
I just joined a small firm, and they have been using Access for all of their data warehousing. As expected, they are nearing the limits of access databases, so I have migrated their data to an SQL Server database. I would like to keep using access as the front-end, since they have many queries they have developed over the years.

And ... Here is the problem ... when I bring the tables into SQL Server, they are all prefixed with "dbo" ... after I have established the ODBC connection to SQL Server, access reports that it cannot find the table in question, since it is now prefixed with dbo.

Without changing large numbers of queries, is there any mechanism I can employ to map any table references in the access queries to the sql version?

Example - SQL query reads "select * from mytable"
It really should read "select * from dbo.mytable"

thanks in advance for any suggestions !!!

Ifor
Aged Yak Warrior

571 Posts

Posted - 01/13/2014 :  09:44:19  Show Profile  Reply with Quote
You should not have any problems if the user you connect to the DB has a default schema of dbo.

eg

ALTER USER YourUser WITH DEFAULT_SCHEMA = dbo;

Go to Top of Page

bdowdy
Starting Member

USA
3 Posts

Posted - 01/13/2014 :  10:08:24  Show Profile  Reply with Quote
Thanks for your quick reply ... however ... In the SQL Server User Mapping section, the user in question has a default scheme of "dbo" already. The problem is that in Access, after I link to the table, it prepends "dbo" to the table name, but the actual query does not have the dbo prepended to each table name. I am trying to avoid having to append "dbo" to hundreds of queries... Thoughts?

Edited by - bdowdy on 01/13/2014 10:35:10
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/15/2014 :  04:26:30  Show Profile  Reply with Quote
quote:
Originally posted by bdowdy

The problem is that in Access, after I link to the table, it prepends "dbo" to the table name, but the actual query does not have the dbo prepended to each table name


After creating the linked table(s) in Access just rename the Table Link Object from "dbo_MyTable" to "MyTable" (highlight and press F2 to rename, you can thus just use keyboard "Down Arrow" to get to next one, and "F2" to rename, which if you have LOADS to do is easier than using Mouse!)
Go to Top of Page

bdowdy
Starting Member

USA
3 Posts

Posted - 01/15/2014 :  14:43:08  Show Profile  Reply with Quote
Kristen ... perfect, simple yet elegant, obvious, and it works!! Thanks so much for your help !
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000