This page contains a summary of key resources that you will find useful in getting started with MUST, MUST+SQL and MUST+WEB. The on-line articles are also available to link directly into the help system.

The page has a flow which takes you through the process of upsizing:

• Planning Your Upsizing
• Upsizing Proccess
• SQL Translation
• Web Conversion

The following articles should be read in relation to planning to upsize a database, they explain some of the subtle differences between Access and SQL Server, will help you decide on how to use file dsn’s, the best type of drivers to use, and how to plan using schemas and security to achieve a good design.

• Building applications with schemas and database roles
• Using new SQL Server 2008 Data Types
• Choosing between System and File DSN’s
• ODBC Drivers and OLEDB Providers for connections from Access to SQL Server
• Handling tables and views of temporary data
• Use your own naming convention for constraints created in SQL Server
• SQL Azure and Microsoft Access
• Getting started with SQL Server Security

Creating Auditing Databases and Adding Auditing Fields

MUST has special features to add auditing fields to your tables and to create auditing databases which track row level changes to data, these features need to be considered during the planning phase, as choices must be made before starting a conversion

• View a pdf file showing how to use full database auditing for enhanced traceability.
• Walk-through, a brief explanation of menus for auditing

Upsizing Data and Structure

The video below will take you through the most common sequence of steps required when upsizing a database.

• Walk-through, a brief explanation of menus when upsizing a database
• Walk-through, a brief explanation of menus to use schemas in simplifying a design and simplifying security

The following articles describe how to understand issues that can arise as you are converting the database.

• Relationships which will not upsize, ..cycles and multiple cascade paths..
• Understanding problems when converting time data to SQL Server
• Multi-part foreign keys, subtle differences between Access and SQL Server
• Resolving issues when upsizing one-one relationships
• Linked tables and triggers which insert data into other tables
• Upsizing Attachment and OLE Data Types (PDF).

After Upsizing, Application design

The next group of articles will help you to understand how performance is effected by your Access design, and provides additional stock code for re-linking tables and views, and performing maintenance tasks in SQL Express.

• Searching and Displaying Data with multiple subforms, Bound/Unbound Form
• Using UNPIVOT to normalize mapped data sets in SQL Server
• SQL Server Database Auditing
• Searching and Displaying Data using a single Bound/Unbound Form
• Access application changes after upsizing to SQL Server: Part I code changes
• Access application changes after upsizing to SQL Server: Part II design changes
• Relinking tables and views between Access and SQL Server
• Generating default values for fields in a form
• Boolean Default Values, when linking Access to SQL Server
• Triggers and Denormalized Tables of Data
• Access and SQL Server Views with Boolean Values; when is True -1 or 1
• View updateability affects with multiple base tables
• View updateability and Timestamp columns
• Packing up Text Fields using a Common Table Expression

After you have completed your development work, you will decide to upsize the live system, these resources discuss how to do this.

• View how to use the commissioning module to repeat upsizing clearing down and re-populating the data when a system goes live (PDF).
• TSQL Script files can also be generated for commissioning a system.
• Walk-through, a brief explanation of menus for commissioning

The video below will take you through the most common sequence of steps required when converting your SQL from Access to SQL Server.

These resources are aimed at helping you understand the techniques which MUST uses when translating SQL, a key technology to understand is our unique approach to converting query and screen parameters.

• Working with vw_tblParameters
• Walk-through, a brief explanation of menus for translating SQL
• Resolve SQL translation issues
• MUST+SQL advanced SQL Translation Features (PDF).
• Converting SQL which refers to screen controls using a parameter table
• Translating SQL data between Microsoft® Access™ and SQL Server - Parts I and II (Visual Systems Journal)
• Solving complex query calculations using nested queries
• Using a UNION in nested queries with SUM calculations
• Converting queries to views, general points to consider
• Converting queries to views, handling IIF using CASE and COALESCE statements
• Converting Access Append Queries and IDENTITY_INSERT Problems

MUST enables you to do partial conversions on blocks of queries, these articles in the section illustrate how this can be used.

• Case study: converting Access queries to Views used in Excel

• Upsizing Tables with Data Corruption and reserved error -7776
• Upsizing and SQL Server Collation Codes (Specialist article)

The articles below will explain the CSS design strategy that MUST use and its support for Ajax.

• Access Form to WebForm methodology
• Enabling Ajax in WebForms
• CSS Design Strategy
• Altering a webform layout with absolute control positioning in VWD 2008

MUST supports SQL, LINQ and Entity data sources, these articles will help you decide which technology is best for you.

• Choosing a SQLDataSource, LINQDataSource or EntityDataSource
• Getting started with LINQ
• Getting started with the Entity Data Model
• Working with foreign keys in tables and views in the Entity Data Model

The articles below contain background information which is useful in understanding our approach topics such as Data Binding and drop-down lists (this is a very important article and MUST has special features for checking data intergrity to highlight possible issues here).

• .net data challenges, drop-down lists and SET NULL on relationships
• .net data challenges, data binding
• Formatting date and currency data in ASP.net
• Integrating ASP.NET security with SQL Server security using LINQ

The articles below discuss converting VBA code to VB.NET.

• Converting Access VBA to VB.NET – Global Variables
• Converting Access VBA to VB.NET – General Principles

Reverse engineering is a process which allows you to back-convert a set of SQL Server tables and relationships back into Access.

• Sequence of steps to reverse engineer a database (PDF).

We also have a number of articles on various Access topics from using simple query by form (which is useful to understand if you are not an Access expert, but get landed with an Access database to convert), to the basics of writing recordset and querydef code.

Techniques

• Query By Form & Query By Where
• Recordsets & QueryDefs
• Creating Tables using DDL, DAO and ADOX
• Domain Functions

Administration

• Access Database Security
• Maintaining Access Databases - Compact, Repair & Decompile
• Sandbox Mode In Access 2003
• Query Statistics & Analysis Of Performance - Comments On Access & SQL Server
• Documenting Access Databases

---

Latest Articles Multi-tenant Database Design

---

Top of Page