Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Migrating Access to SQL Server

Author  Topic 

bdowdy
Starting Member

3 Posts

Posted - 2014-01-13 : 09:14:05
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

700 Posts

Posted - 2014-01-13 : 09:44:19
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

3 Posts

Posted - 2014-01-13 : 10:08:24
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?
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2014-01-15 : 04:26:30
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

3 Posts

Posted - 2014-01-15 : 14:43:08
Kristen ... perfect, simple yet elegant, obvious, and it works!! Thanks so much for your help !
Go to Top of Page
   

- Advertisement -