How to migrate a Standard Time MDB database to SQL Server

Watch the video »

Applies to Microsoft SQL Server.  Standard Time ships with a Microsoft Access MDB database.  This database is suitable for small groups of twenty users or less.  Adding additional users can result in performance issues.  This article explains how to migrate from the MS Access database to an MS SQL Server database.

1. Create an SQL Server database for Standard Time.
   Use the SQL Server Enterprise Manager DTS Wizard to import the MDB database to a new SQL Server database.  Alternatively, you can create an empty SQL database, and have Standard Time automatically create all the necessary tables and columns in it.
   Details
    
2. Create an ODBC data source for the new database.
   Each Standard Time client needs an ODBC data source that points to the SQL Server database.
   Details
    
3. Setup Standard Time to access the new SQL Server database.
   Each Standard Time client needs to use the data source to access data.
   Details
   
    
      

Additional items to check:

1. Check for smalldatetime data types.
   After importing an MS Access database, sometimes date fields will use the smalldatetime rather than datetime data type.  Make sure all tables use the datetime data type.
    
2. Make sure all users have NT Authentication access to SQL Server.
   Each user must have NT Authentication access to SQL Server in order to gain access through ODBC.
    
3. Make sure the administrator has rights to create and alter tables.
   New upgrades of Standard Time often include new database tables and columns.  The installation process of Standard Time includes a step that upgrades the database.  The first user who installs a new version will need SQL Server rights to create new tables and alter existing tables.  If these rights do not exist the installation will not succeed.  Normally, these administrative users will need the db_owner role, while normal users should use db_datareader and db_datawriter roles. 
    
4. Use "British English" or "English" if you are in Europe
   If you are using SQL Server in Europe, you may need to choose "British English" or "English" for the language in the File DSN setup.  This is necessary to translate dates for your SQL Server.  Check the option named "Change the language of SQL Server system messages" and change the language to "British English" or "English" in the File DSN configuration.  This will ensure that dates send to SQL Server will be translated correctly.
    
5. Make sure you have sufficient server horsepower.
   The following recommendations may help to ensure that your server meets user demand.
   Details