UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Moving Awa To SQL Azure - Table Name Of Access.tblxyz, Access 2013 Web App    
 
   
alancossey
post Jul 31 2017, 04:58 PM
Post#1



Posts: 545
Joined: 26-September 05
From: Norfolk, UK


I am moving my AWA to a SQL Azure backend with standard Access front end since users will have access to Microsoft Access and I really cannot be bothered to learn yet another tool having carefully created some AWAs only to be told I've got less than a year to convert them to something else. In fact on this one, it looks like the AWA is corrupt as I can't open it any more in design mode in Access proper, so this move is an attempt to get my database back (though it is still working OK through the web front end).

I have used a dacpac to import the data into Microsoft Azure SQL Database (aka SQL Azure) following https://gpgonaccess.blogspot.co.UK/2017/04/...ing-access.html (thanks, George). I have hacked out lots of tables and will hack out lots more stuff that is no longer needed, but my tables are all named, "Access.tblXYZ" and I would really love them to be just "tblXYZ". If I try to rename them it suggests "tblXYZ", but when I press enter, it goes back to "Access.tblXYZ".

What do I need to do to get just "tblXYZ", please? There is a schema called "Access", but I daren't delete that in case everything goes belly up.

Yours nervously,

Alan Cossey

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
GroverParkGeorge
post Jul 31 2017, 05:30 PM
Post#2


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


That is a schema.

The default schema is dbo in both SQL Server and SQL Azure, but in the AWA environment, there are several schemas, Access.xxxx, AccessExternal.xxx and AccessSystem.xxx, and so on.

When you do the migration, those schemas are recreated. I really need to update the blog posts on that point.

Anyway, it doesn't hurt to leave them in the original schemas, but you can move them into the "default" dbo schema, if you wish.

And, once you link to them in Access, you can just give them the "standard" names.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Jul 31 2017, 05:33 PM
Post#3


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Sorry, left off a point.

You can drop the Access schema after moving all of the tables you want to keep into the default dbo.

--------------------
Go to the top of the page
 
alancossey
post Aug 1 2017, 02:03 AM
Post#4



Posts: 545
Joined: 26-September 05
From: Norfolk, UK


Thanks, George. I realised what I had said when I woke up during the middle of the night. I think I will change it to dbo just so I can have it as near as possible to a "normal" database. Hopefully I can now delete most of the AWA-specific stuff without affecting the functioning of the database itself.

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
alancossey
post Aug 1 2017, 02:14 AM
Post#5



Posts: 545
Joined: 26-September 05
From: Norfolk, UK


If you get the time on your blog, it might be helpful to add details of stuff which you know can be deleted from the SQL Azure database. Currently I'm deleting all the tables and views that did not appear in the Access designer when it was in an AWA, e.g. a parallel SQL view/query for each of the "proper" queries. For each query/SQL view which appeared in the Access designer, e.g. "Access.qselXYZ", there is one called "AccessRuntime.qselXYZ", which I am deleting. I also had some with weird names, almost GUID-like. I think, repeat think, they were ones which appeared when something went wrong in the Access designer. They may be there because this AWA is corrupt and I am having to move the data to SQL Azure earlier than intended, because I was no longer able to open it in the Access designer. Moving it from AWA/Office 365 seems to SQL Azure seems to have saved my bacon.

--------------------
Alan Cossey
Premier Data Technologies Limited
www.pdtl.co.UK
Go to the top of the page
 
GroverParkGeorge
post Aug 1 2017, 07:38 AM
Post#6


UA Admin
Posts: 31,197
Joined: 20-June 02
From: Newcastle, WA


Hi, yes. It's past time for a follow up.

Frankly, I've not bothered to delete anything in my own dbs yet, partly because I am not 100% sure there won't be something useful or just interesting.

As it stands, though, the only tables you should need are in the Access schema.

And, as you know, once you link to them in an Access accdb, you can just rename the linked tables there.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 02:16 AM