Saturday, October 18, 2014

Two Things I Learnt Making My Second Access Web App

Web apps are being closed down in  2018

When making my first web app, I charged at the App like a bull in a china shop and didn’t really pay much attention to the whole App building experience.
With my second App I noticed two important things
Tip 1: Importing Auto-number Related Tables Make Life So Much Easier
Access Web databases love Autonumber fields so much that they will always add one for you. So if you want to save yourself a lot of time, make sure the Primary Key in the database you are converting is Autonumber and the Key in the Related table is Long Integer.  This is really important because the Web Forms Access creates for you relies on these features to set up the Automatic forms. So even if you don’t understand this concept now, be prepared to rewrite the parts of your online database to suit this model before you import. And believe me you will import your database a few times before you get it right.
Tip 2: A Desktop Database with Read-Write Linking Is Essential
The second thing I found I just had to have was a read-write connection to the Office365 tables to change and add data. This is explained in this video
To explain why this is important, consider this simple example. In the online Authors tab at, I was showing Authors that had written for Smart Access but there was no actual article online. This meant viewing that person drew a blank in the related articles list. So to solve this I am going to add Yes/No field called ArticleOnline in the Office365 database as in the picture below.

Now I need to populate that field and because I am an older mouse, I like to follow old paths to solve problems the same way as I am used to. So I open my Read-Write connected database and build a normal update query. In the picture below you can see the query on the right and on the left you see the World Icons in the linked fields. These are ODBC links to the Office 365 database. So as the simple linking of the tables fulfils my Yes/No test, I can run the query.

Now I can make a small change to my Access Web Form so that it filters on the Yes/No field as in the following picture
 If you don't think connecting using linked tables is important, try programming XML packets to transfer data, its 100 times more difficult.
Garry Robinson
Editor of


grovelli said...

Hi Garry, I've watched but can you explain why is Sharepoint needed since the tables are in SQL Server?

grovelli said...

Doesn't your update query set ArticleOnline to true for all the records in the tblAuthors table? If so, how do you know which authors don't have articles online?