SQL Azure linked tables in MS Access

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.

Add comment

Loading