I was trying to help a user out in a forum get a listing of all the SubKeys of a specific registry Key and thought I’d share my solution here.
Per the usual, there are a number of different ways to read the registry, but I found that using WMI is very simple.
To simplify things, I added the use of a ‘RegistryClass’ Enum so you don’t need to remember the various registry class constants.
Enough talk, here’s the code!
A new bug has surfaced impacting Microsoft Access Web Applications (AWAs) in which queries only return the first 50-60 records. This is has been confirmed by multiple sources now.
The good news, well sort of, is that fellow MVP George Hepworth found a simple workaround to the problem which is to simply apply a sort. Once you get the query results, apply a ascending or descending sort and all the records show. I know it’s not ideal, but for now, it will have to do until Microsoft fixes whatever it is that they’ve broken.
The original MSDN thread can be found at Access web app queries now being throttled to first 50 records only?
Did your AWA Hybrid application suddenly stop working over the week-end?!
First reported by fellow MVP George Hepworth, it would seem that Microsoft, in their very finite wisdom, decided to change some, or all of their AWAs server(s) over the course of the week-end without notifying a single user. As a result, the connection string and credential you’ve always used, today, are invalid and your database is dead in the water.
George reported that the External Connections setting was also disabled.
Below are a few threads that have surfaced regarding the problem.
AWAs are going the way of the Dodos Microsoft has just announced!
In their recent post from the Office Retirement Blog, Microsoft states formally:
We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018
So the writing is on the wall, steer clear of developing any new AWAs and look into migrating your existing data prior to the deadline so as to not loose it.
This won’t come as a terrible surprise to many as Microsoft hadn’t invested in any new features in quite some time and had by appearance abandoned it over a year ago. That said, just when we finally thought we were moving forward with a web solution, Microsoft, once again, pulls the rug from right under our feet! Sad, but not surprising as this was their 3rd failed attempt at porting Access to a WAN environment:
Does anyone else feel the BETA Burn?!
It is just sad to see Microsoft’s lack of engagement with their own products that they continue to walk away from products, time after time, rather than completing them properly. They finally were heading down the right path, but once again never finished the product, never added the features that people were requesting! They keep ignoring the feedback provided by MVPs and end-users alike!
Now Microsoft wants us to turn towards PowerApps. All I can say is think long and hard before jumping into this new product because if history is any indicator … I’ll be posting about it’s demise in 2-3 years! Moreover, from what we have seen of PowerApps, the supposed replacement for AWAs, are far from being capable of doing what we, Access users, need. For one thing, they do not have any Data Macros so implementing business logic is simply not possible at present! So as it stands right now, Microsoft provides AWA users with no viable alternative solution to turn to, you need to use alternate technologies altogether.
What irks me however is the fact that they promoted https://access.uservoice.com/forums/321915-access-web-apps when they knew that they weren’t going to move forward with the product/service. Why give people false hope and tell them you’re listening when that simply is not the truth! 184 Web App suggestions made and not 1 was ever implemented! People were talking, but Microsoft sure wasn’t listening by all appearances. Is uservoice as a whole just another Microsoft feel good PR stunt to make user believe they are being heard I wonder?
Let me also be perfectly clear, this announcement in NO way impacts the future of standard MS Access Desktop databases, they continue as they always have!!!
Microsoft has closed the comment section on there original announcement page after 2 weeks. Great way to support your users, just don’t allow them to say anything!
Please note, that there are already reports of problems with the data migration tool, so you may wish to wait a few days to hopefully allow Microsoft to fix whatever the issue(s) is(are).
So with Microsoft terminating AWAs what is one to do exactly! Well, I personally have always believed that if you are going to develop something for the web then you need to use proper web technologies, things like:
That all said, fellow MVP Julian Kirkness recently found himself scrambling to find a suitable replacement for his AWAs, and since PowerApps are not capable as mentioned above, he did some digging and has recently found a tool called Zoho Creator. You can review his experience and thoughts on his blog at Zoho Creator Review and see if it might suit your needs too.
I decided I wanted to try and fill a noticeable gap in information for beginners trying to get started with developing MS Access Web Apps. As such, this is my first posting, well sort of, on the subject. In this posting, I simply wanted to try and centralize good resources on the subject.
First things first, let cover some minimal terminology.
Don’t these terms mean the same thing? No, actually they don’t! Roger Carlson recently wrote an article entitled: “Access Web Databases: 2010 vs. 2013” explaining in detail the differences. The gist of it is that a Web Database is an Access 2010 SharePoint based database and a Web App is an Access 2013 SQL Azure database. Microsoft abandoned the SharePoint driven web databases as of Access 2013 and has switched it over of SQL Azure. These technologies are not interchangeable. If you have a choice, be sure to develop a Web App, since Web Databases are already obsolete!
So a standard Web Database or Web App is entirely stored and run through your server/web browser. On the other hand, a Hybrid Database stores the data (your database back-end) on a server, but the Front-End (the GUI) remains on the client’s computer.
Why would anyone want to setup things in this manner? I have one word for you, VBA! Although Web Databases and Web Apps offer the beauty of being accessible worldwide, they do not support any VBA. Web Databases and Web Apps are limited to the use of Macro for any form of ‘programming’ (I use that term very loosely in this context). Sadly, Macros are simply too limited to enable many features, so by keeping the database Front-End on the user’s computer, and thus running it through Access (rather than a web interface which is limited to HTML and javascript), you can continue to enjoy and benefit from the power of VBA. Basically the best of both worlds.
For more information on this, see my article entitled: “How to ‘Hybridize’ your MS Access Database In Office 365 Azure Database“.
Below are a few links to get you initiated with Web Apps. I am trying to only list the good ones worth checking (filtering out the garbage since real good information on the subject appears to be hard to locate).
How to: Create and customize a web app in Access 2013
Concepts (Access 2013 custom web app)
Access Web App Primer
Access 2013 Inside & Out by Jeff Conrad
Professional Access 2013 Programming by Teresa Hennig, Ben Clothier, George Hepworth, Dagi (Doug) Yudovich
If you have other valuable sources of information on the subject and care to share, please send my an e-mail though the contact page with the link and I will be sure to check it out and add it here if appropriate and no string attached to accessing the material(s).
Before going any further, please quickly read: Steer clear of Access Web Applications (AWA)
The following article explains How-to ‘Hybridize’ your MS Access Database In Office 365 Azure Database or How-to use an Office 365 Back-End database with your MS Access Desktop Front-End (FE). It is meant to be a step-by-step instructional guide of how-to to convert a standard split desktop database so that the data, the back-end (BE), is imported and available through an Office 365 account. So the data thus becomes available to anyone with an Internet connection, but the application itself, the front-end (FE), remains on each user’s local computer.
A few people have now asked me ‘Why such an article?’ If you are here, you already know the reason! Simply put, Microsoft has, once again, not provided a simple step by step explanation of how to accomplish this task. I was banging my head trying to get a simple ODBC connection functional and couldn’t locate instructions that actually worked anywhere. So once I finally got everything working smoothly, I decided I’d share with the rest of the world to save the days of frustrations I faced! Microsoft’s support (whether it be online documentation, Knowledge base, forums), more and more, simply disgusts me. This learning experience allowed me to learn that there is a flaw (it does not work! just a small flaw, lol!) in the Windows ODBC Data Source Administrator when creating File DSN to Office 365 SQL Azure Databases (that has been confirmed now by fellow MVPs), which I provide a work around to below. No where have I found this mentioned anywhere. This cost me 6 days of development and countless frustrations. Hopefully Microsoft will address this, eventually. I can only imagine the number of people faced with the same issue with no solution. But if you are here, you now have a solution!
Another question asked is ‘why ‘Hybridize’ a database when you could simply create a Web Database?’ Simple, VBA! Need I say more! Web Databases are primitive versions of MS Access databases and you cannot use any VBA whatsoever. A hybrid database bypasses this limitation while making the data available over a WAN (in the case of Office 365 – The Internet/World). Sort of the best of both worlds; get around Access’ native limitation of not being available over a WAN while bypassing Access’ Web Databases’ limitation of not allowing VBA.
Also, for existing databases, converting them into Web Databases can be involve a tremendous amount of work, to the point of being inconceivable altogether. By Hybridizing them, you can greatly benefit from this new technology while requiring very little changes to your standard database.
So what is involved?
Let’s get going.
First, before doing anything whatsoever, PLEASE make a backup copy of the database you are wanting to ‘hybridize’, before going ahead with any of the following instructions! This way should anything go wrong you can at any time revert back to the original copy. Never work directly with your good copy of your database!
Below are the step-by-step instructions as of 2014/01/07.
Obviously, you need to open an account.
Login to O365 account.
Once logged in, click on Sites
Click on Team Site
Click on Site Contents
Click on Add an app
In the search control, perform a search for ‘Access’
Click on Access App
A Adding an Access app dialog will pop-up. Enter a name and then click on OK (will now appear in your list of assets, allow a couple minute for Office365 to configure things for use.). You should, after a moment, see something similar to the following where your newly created Access app is flagged as New.
Click on the newly created App.
Once the App loads, Click on Settings (the Gear in the top right-hand corner)
Click on Customize in Acces
Click Open (the pop-up at the bottom of the screen – Access should then launch on its’ own)
At the bottom of the Access screen, Click on Access (assuming your be is an Access database) from the Create a table from an existing data source menu
Simply follow the prompt to select your existing database back-end to pull the tables from to import into Office 365 SQL Azure Database
Now that you have successfully uploaded your database tables into your Office 365 SQL Azure database, you have to enable remote connections and take note of the DSN setting to create said connection. To do so:
Goto the File tab and click on Manage (under the Connections)
The following menu/dialog will appear. Click on From Any Location, as well as, Enable Read-Write Connection.
Click on click on View Read-Write Connection Information.
The following dialog will appear. Be sure to copy the information verbatim as it will be required to create a File DSN (or a DSN-less connection).
Close the dialog and then close Access. You can now log off of Office 365.
If you do not already have the SQL Server Native Client 11.0 install you need to get a copy and install it.
Goto http://www.microsoft.com/en-ca/download/details.aspx?id=29065
Click on the Download button.
On the following page, click on the Install Instruction link which will expand a listing of the various files that you can download. Scroll down until you find the Microsoft® SQL Server® 2012 Native Client (about midway down).
If you are running on a 32bit OS, then download and install the X86 Package (sqlincli.msi). If on the other hand you are running on a 64bit OS, then download and install the X64Package (sqlincli.msi).
Note: If you are going to create a DSN-less connection (which is always a good idea) you can step over this step). That said, this is still the easiest method for beginners to get things up and running.
I’m not going to go into the details, just let me say that do not try to create a File DSN using the Windows ODBC Data Source Administrator utility as it seems to be very unreliable. Instead, simply create a new blank text document and give it whatever name you wish with a dsn file extension.
Then open the newly created text file and enter the following (replacing the appropriate value with those you retrieved from the MS Access ‘View Read-Write Connection Information’. Pay particular attention to the UID (username/user id as it includes the server!) and the SERVER as it is prefixed by ‘tcp:’.
[ODBC]
DRIVER=SQL Server Native Client 11.0
UID= db_1446956c_xxx_xxxx_xxxx_xxxxxxxxxx_ExternalWriter@ kcxxxxxxxxfx
Pwd=jsxxxxxxxxxxxxxxFu
Encrypt=yes
DATABASE= db_8332e578_xxx_xxxx_xxxx_xxxxxxxxxx
SERVER=tcp:kcxxxxxxxxfx.database.windows.net
Save and close the file.
Launch your MS Access Database front-end (FE).
Goto the External Data tab and then click on the ODBC Database from the Import & Link group
Click on the Link to the data source by creating a linked table and the click on OK
Go select the File DSN from wherever you saved it and then click on OK
Select the table(s) you wish to create linked table to and then click OK
You are done. Your front-end should now have read-write access to the Azure database on Office 365!
At the end of the day, the hybrid database fills a need. It has to be noted that performance does take a hit when compared to local database or LAN databases. That said, performance tuning can greatly help with overall performance (like with any database) and aspects such as your Internet Connection can also greatly impact your ability to work smoothly.