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 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?
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!)