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

Marnix

New member
Local time
Today, 06:47
Joined
Sep 15, 2020
Messages
1
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

Registered User.
Local time
Today, 00:47
Joined
Apr 9, 2015
Messages
3,611
Once the tables are linked,you can rename the table names to match the previous names.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:47
Joined
Oct 29, 2018
Messages
11,983
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

Super Moderator
Staff member
Local time
Today, 05:47
Joined
Feb 19, 2013
Messages
12,454
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

Lifelong Learner
Local time
Yesterday, 21:47
Joined
Mar 14, 2017
Messages
2,022
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom