Best practice migrating access split database to Azure SQL (1 Viewer)

[Marnix]

Hi,
We have a split database. I used the migration tooling to migrate tables to an Azure database. All tables have been renamed from TABLENAME to DBO.TABLENAME because of the way to use SQL in a proper way.

My frontend however is using the plain tablenames. What's the best way to go: rename all references in SQLscripts/VBA code etc. to the new tablenames? Or (manually) change the linked tablename to TABLENAME where it references DBO.TABLENAME at SQL side. The disadvantage of this is that I can no longer easily switch to a new dataset because external and internal tablenames are different.

Any suggestions?

 

[Ranman256]

Once the tables are linked,you can rename the table names to match the previous names.

 

[theDBguy]

Hi. Welcome to AWF!

Not necessarily what I would do, but you could also create a query for each linked table and use the names of your old tables.

 

[CJ_London]

personally I would go with using the names provided (i.e. including dbo.) and modify your code to work with the new names, particularly if the app is still being developed. It's one less thing to remember down the road.

if you turn on autocorrect, you can rename your original linked tables with the dbo. prefix which will take care of most queries, and in vba use find and replace. But make sure you work off a copy of the front end before starting.

note that dbo is just a domain name - within sql server/azure you can restrict user rights by domain, it can also be useful for identifying tables/queries with specific functionality. Not tried it myself but you could try renaming your tables in azure to not include dbo.

I would also take the opportunity to move most, if not all, your queries to azure, you will get better performance.

 

[Isaac]

I respect previous responses, as there may be pro's and con's of each, but my personal approach would be to rename the linked tables back to what you really WANTED the table names to be - their original names.
To me, that Access names linked tables SchemaName.Tablename, or SchemaName_Tablename is kind of silly....it violates what the name of the table really IS.
You chose a name that you thought made the best sense for these tables - in Access. Then you retained that name, for the same reasons, in SQL Server (Azure). DBO is just a schema and is not a table name.
Only Access linking process messed it up - I'd rename them back to what you really had deemed best. The other way seems arbitrary to me.

One question I had:

(manually) change the linked tablename to TABLENAME where it references DBO.TABLENAME at SQL side. The disadvantage of this is that I can no longer easily switch to a new dataset because external and internal tablenames are different.

What do you mean by that? The disadvantage? I don't understand that at all. If you retain the table names (inside Access), that you originally had, all of your remaining FE design should work (as relates to naming).
What is meant by switch to a new dataset?

As far as "manually", you can easily write a few lines of code to go through the db TableDefs collection and rename anything that starts with DBO_ or DBO. to remove the first 3 or 4 characters.

 

[Pat Hartman]

I agree with Isaac. Access cannot use the same table names as SQL Server because SQL Server uses the dot to separate the owner from the table name and Access uses the underscore so you actually gain nothing but pain.

I always rename my linked tables to the original Access name. as you refresh the links, your local name remains so you only have to do the rename once. When the upsizing wizard was part of Access (why it was removed is beyond me), the wizard used to do the renaming for you.

Also, many DBA's actually use Owner to separate schemas in a database and if you include owner as part of your local link name, you're in for a world of hurt in that type of environment since you won't be able to swap from test to production by just relinking since your whole table name would have to change.

People seem to forget what "dbo" actually means (it is the abbreviation for database owner) and that it is useful as a segregation tool.