Link to home
Start Free TrialLog in
Avatar of Davisro
DavisroFlag for United States of America

asked on

Linking Sql Server tables into Access with dbo_ prefix on all table names breaking queries

Experts,


I uploaded all my tables into Sql Server using SSMA for Microsoft Access...it took about an hour, but there was one table that wouldn't upload (dont know why). So I uploaded it separately, again in SSMA. 

Then all of my linked tables from Sql Server wouldnt open in MS Access, so I relinked them using Sql Server Native Client 11.0, and they all came in with the schema dbo_ preceding each table name. 


There are many hundreds of queries in the database, so does that mean I have to rewrite them all to find the new table names?

ASKER CERTIFIED SOLUTION
Avatar of Member_2_8330475
Member_2_8330475
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Davisro

ASKER

ooOOOOhhh. Omg this is amazing bc I was thinking of all the table references in the code as well. Thanks for this! Are queries faster with the dbo prefix because they don't have to figure out which schema? I only have the one schema in this db.

thanks!

No.   The local table name means nothing.   What counts is what is in the connection string.   Open the VBA editor with Ctrl/G and type:

?  CurrentDB().TableDefs("OneOfMyTableNamesHere").Connect


 and hit return.   You'll see the connection string that Access uses to connect to the remote data.


Jim.

I just want to add that the reason for the inclusion of the dbo_ is that SQL has schemas.  The dbo is usually the default (database owner) schema.  You can actually have 2 tables in the same database with the same name but different schemas in SQL server.  So in that case if you were to link to both, then you could tell them apart.  Or if you were to have 2 or more schemas you could organize them by the prefix.  Jim provided the most common solution people want which is to use code to rename them to remove the leading dob_