Hi - I've recently converted an Access 2003 database (. mdb) to Access 2003 .adp front end / SQL 2005 Server backend and in the main it works very well. When I uploaded the Access 2003 .mdb tables to the SQL 2005 Server they were prefixed with dbo. - eg tblName --> dbo.tblName. From the .adp front end prespective these objects appear without the dbo. prefix. Other objects in the SQL 2005 Server (Views, Stored Procedures etc) also have the dbo. prefix. Views, Stored Procedures etc appear under the Queries tab in the access .adp front end without the dbo. prefix.
I then decided to create an Access .mdb front end and attach the SQL 2005 Server objects via an ODBC connection (as a lot of the literature indicated that attaching SQL Server objects was a better alternative than using Access .adp). Unfortunately when I linked the SQL Server objects via the OBDC connection their reference in the Access mdb appeared as dbo_ (underscore - not dot) eg dbo_tblName not dbo.tblName and/or tblName. Also the linked Views and Stored Views - apart from having the dbo_ prefix - appear under the Tables tab (rather than the Queries tab) in the Access.mdb front end. All this adds up to a lot of aditional work in getting things to work. For example
1) a report based on a view now has to be changed to have its Record Source property modified to include the dbo_ prefix (vwXYZ --> dbo_vwXYZ)
2) Macros/VBA running Views have to be changed to include the dbo_ prefix as well as changing code like DoCmd.OpenView vwXYZ to DoCmd.OpenTable dbo_vwXYZ.
My question is basically - am I doing something wrong or do I simply have to wear the differences between the alternative approaches (.adp vs .mdb with linked tables/views etc) or can I do something to reduce the the effort - eg: how can I get both approached to use the dbo. (dot) prefix
Start Free Trial
