Build new Access cloud web apps and migrate desktop databases to the cloud This is your complete, practical guide to creating Microsoft Access web apps and migrating existing databases to the cloud. Access MVP Andrew Couch guides you through the entire web app life cycle, from design through deployment and upgrades. After introducing Microsoft Office 365 and the web app development environment, he reviews key issues associated with moving data into a web app or creating cloud apps with new data. Next, he drills down into app construction, from table design to integration. You’ll learn how to extend Access with Microsoft Azure SQL, PowerPivot, Visual Studio 2013, SQL Server Reporting Services (SSRS), and Apps for Office, and master important new enhancements in Office 365 SP1. Learn best practices and techniques to: Capitalize on key Office 365 features in your Access web apps Design and integrate all the features of Access web apps Make your desktop databases compatible with web app table structures Implement and test business rules by using the Macro Programming Tools Understand how your app design translates to objects in the cloud-based Azure SQL Database Use Microsoft SQL Server Management Studio (SSMS) to connect with and manage web apps Improve reporting with PowerPivot, Visual Studio 2013, and SSRS Extend Access web apps with Apps for Office features Capitalize on Office 365 SP1 improvements in change deployment, intellectual property protection, and integration Get all code samples, including complete apps, at: http://aka.ms/AccessApps/files About This Book For experienced Access developers who want a deep understanding of web app design and implementation For new web app developers who want to develop Access web apps with Office 365

Table of Contents:
Introduction i Chapter 1 Finding your way around Office 365 1 Office 365 subscriptions 2 Office products in a browser 5 Getting started with Office 365 5 Team site, personal site, and public site 7 Installing Office Professional 10 OneDrive and OneDrive For Business 2013 13 Creating a web app using a template 19 Opening a web app with Access 23 Saving a web app as a package 24 Uploading a web app package 26 Editing a web app package 30 Displaying a web app in a browser 32 Sharing a web app with external users 32 Creating sites and subsites 36 Creating site collections (Enterprise subscription) 39 Applying a theme 45 Document storage and libraries 47 Microsoft Azure SQL Database, Office 365, and Access 51 App Catalog 53 Summary 55 Chapter 2 Finding your way around Access 2013 57 A new way of building applications 57 Create a custom web app 61 Importing data from Access 62 Using the navigation pane 64 Working with tables, lookups, and relationships 65 Displaying data in a browser 68 Working with different views 71 Controls available in a view 73 Autocomplete control 74 Related items control 75 Subview control 76 Action bar 77 List control 77 Hyperlink control 78 Multiline Text Box control 79 Web Browser control 79 Overview of macro programming and data macros 80 Upgrading and deploying a web app 82 Side-loading a web app 96 Designing and commissioning with existing data 96 Summary 98 Chapter 3 Converting a desktop database to a web app 99 Tables and primary key fields 99 Relationships and lookups 102 Table and field properties 108 Boolean data 112 Value-based lookups 112 Multi-value data 113 Importing different data types 113 Long Text (memo data) 115 Calculated fields 115 Image data type 115 Attachments and OLE objects 115 Reporting 116 References 119 Quick conversion to external writer 120 Attachment and OLE data 121 Uploading multiple files 122 Extracting attachment files 123 Uploading image files 125 Summary 132 Chapter 4 Creating a blank web app and using templates 133 Creating a blank web app 133 Adding template tables 134 Creating a new table 136 Editing the design of a table 137 Creating lookups and relationships 139 Adding indexing 142 Adding validation rules 143 Adding calculated fields 145 Summary 146 Chapter 5 Displaying data in views 149 Customizing the table selector 150 Customizing the view selector 151 Creating a pop-up window interface 152 Datasheet view 156 Views and record sources 164 Duplicating views 167 List Details view 169 Summary view 173 Standalone view 177 Related items control 180 Subview control 182 View controls 186 Combo box control 187 Combo box synchronization 188 Autocomplete control 190 Web browser control 191 Adding color to controls 192 Summary 193 Chapter 6 Creating data sources by using queries 195 Creating basic queries 196 Adding criteria to queries 198 Adding calculations to queries 200 Adding parameters to queries 202 Totals and queries 208 Top value queries 209 Unique values in queries 212 Summary 214 Chapter 7 Programming a web app by using macros 215 Macro-programming capabilities 216 Macro editor and standalone user-interface macros 219 User-interface events for views and controls 222 User-interface macros 227 Named data macros and stored procedures 228 Data macros and triggers 239 Data macro tracing 247 On Start macro 249 Transactions and recursion 250 Indirect recursion 250 Direct recursion 251 Presenting a view for printing 253 Creating a cross-tabulation of data 262 Side-loading a web app 267 On Deploy Macro 274 Using an alias with a macro action 287 Summary 288 Chapter 8 Managing security and a public-facing web app 289 Creating a public website 289 Creating a public-facing web app in Office 365 291 Managing security in a public web app 292 Summary 300 Chapter 9 Looking under the hood at Microsoft Azure SQL ¿Database 301 SQL Server Management Studio 302 ODBC drivers 303 Connecting to Microsoft Azure SQL Database 303 Schemas 308 Security in SSMS 309 Linked Microsoft SharePoint lists 309 Manually creating an ODBC DSN connection 310 Linking from the desktop to use an ODBC connection 317 Creating DSN-less connections with program code 319 Extracting information on relationships 321 Displaying structural information using data access objects 323 Displaying structural information using ADOX 327 Displaying structural information with a query 330 Validation rules 330 Data macros under the hood 331 Views and table-valued functions 336 Summary 338 Chapter 10 Other techniques for reporting 339 Excel and data connections 339 Excel PowerPivot 344 Creating a report for SQL Server Reporting Services 351 Publishing a report to SSRS 360 Linking a web app to an SSRS report 363 Using Visual Studio 2013 with a web app 364 Summary 374 Chapter 11 Using apps for Office with Access 375 Apps for Access concepts 375 Consuming apps for Access 377 Developing apps for Access 381 Summary 386 Index 387