Dave's Technophorical Times

A blog about Microsoft's Technologies!
SharePoint :: MVC :: ASP.NET :: IIS :: SQL Server :: Visual Studio :: MS Access

From here: https://microsoft.github.io/AzureTipsAndTricks/blog/tip140.html

Note by cbattlegear One important caveat to this process (as shown below). If any writes are happening on the database while you do the export the import will be broken. Best practice is to run CREATE DATABASE AS COPY to create a copy of the database and create an export of the copy.

Right-click on the Database -> click Tasks > Export data-tier application

Now ensure you are connected to your local target SQL server instance (or SQL Azure instance) and right-click on the Database -> click Tasks > Import data-tier application and select the .backpac file that you created earlier. 



Recently I had to link to 4 SQL Azure tables that are in a Access Web App and found a way to always connect even though MS Access does not save the UserName/Password in the connection string.

The SQL Azure connection string is as follows, however note that when you view the connection string in desgin mode you will not see the UID/PWD part.

ODBC;Driver={SQL Server Native Client 11.0}; Server=[Server Name].database.windows.net; Database=[Database Name];
Uid=[Database Name]_ExternalWriter; Pwd=[Password]; Encrypt=Yes

So in order to get around this issue, create a Passthrough Query to one of the Linked SQL Azure tables and save the Uid/Pwd with the connection string. Make it some thing like SELECT TOP 1 * From Access.[Table Name]. Save the query as something like qptSQLAzureStartUp. Then Add this to the StartUp form's OnOpen Event: Dim lngCount As Long and the next line as lngCount=DCount("*", "qptSQLAzureStartUp"). Then you'll find that your Linked SQL Azure tables will open.



The Blogger

Dave Stuart I'm a Developer with a passion for coding. I enjoy the challengers that come with the job! SharePoint is one of my expert areas along with .NET Web Development with MVC and good old MS Access VBA coding. I Blog so that I can remember how I did that way back when; PLUS all this stuff is searchable! I constantly study and run my own business, Dafran Inc. I have passed 22 Microsoft Exams since 1998 when I first jumped on the treadmill of knowledge. I hope that you enjoy this Blog as much as I enjoy updating it. All the very best from Calgary, Alberta, Canada. contact me at linkedin @ dafran.ca

Calendar

<<  February 2020  >>
MoTuWeThFrSaSu
272829303112
3456789
10111213141516
17181920212223
2425262728291
2345678

View posts in large calendar

Sign in