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.
| 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.egALTER USER YourUser WITH DEFAULT_SCHEMA = dbo; |
 |
|
|
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? |
|
|
|
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!) |
|
|
|
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 ! |
|
|
|
|
|
|
|
|
Migrating Access to SQL Server