support@ascassociates.biz

sales@ascassociates.biz

Technical Support Library

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 to in the help system.

The page has a flow which takes you from planning your upsizing, through the upsizing process and onto SQL Translation, leading then to the web conversion.

Upsizing Database Structure & Data (MUST, MUST+SQL and MUST+WEB)

Planning

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
• 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.

• 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

Commissioning a System

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

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

Converting SQL to Stored Procedures and Views (MUST+SQL and MUST+WEB)

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

• Working with vw_tblParameters
• Walk-through, a brief explanation of menus for translating SQL
• Resolve SQL translation issues
• MUST+SQL, View a pdf file showing advanced SQL Translation Features.
• 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

Repeating SQL Translation

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

Troubleshooting

• Upsizing and SQL Server Collation Codes (Specialist article)

Converting Access Forms to .net webForms (MUST+WEB)

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.

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

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 Principals

Reverse Engineering

• Pdf, Sequence of steps to reverse engineer a database

General Access

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 Getting started with SQL Server Security Converting Access VBA to VB.NET – Global Variables Converting Access VBA to VB.NET – General Principals View updateability affects with multiple base tables Access and SQL Server Views with Boolean Values; when is True -1 or 1 Boolean Default Values, when linking Access to SQL Server SQL Azure and Microsoft Access

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz

sales@ascassociates.biz