Opening an Existing Microsoft SQL Server Database

If another database is already open then you will need to close that database first before opening a new database (see Closing a Microsoft SQL Server Database). To open an existing At Your Service Microsoft SQL Server database, select File  Open Database  Microsoft SQL Server Database from the main menu. The database must have been created from within At Your Service (see Creating a New Empty Microsoft SQL Server Database, and Importing an Access Database to a New Microsoft SQL Server Database).

Select which SQL server connection type you wish to use, enter or select the corresponding connection parameters, and click the Login button to connect to the Microsoft SQL server. Click the Cancel button or press the Esc key to cancel without connecting to the Microsoft SQL server. The following table summarizes the connection types and parameters.

Microsoft SQL Server Connection Types

Connection Type	Description
Windows pass through authentication	Windows pass through authentication passes your Windows login user ID and password to the Microsoft SQL server. Enter or select the name of the Microsoft SQL server.
SQL server login and password authentication	SQL server login and password authentication requires that a separate user ID and login be created on the Microsoft SQL server. The same user ID and password may be shared by all users. Users that open databases must be configured to have at least a db_datareader, db_datawriter, and view server state database permissions role in the corresponding Microsoft SQL Server database. Some reports also require users to have Create View rights. (See Configuring User Security on Microsoft SQL Server.) Enter or select the name of the Microsoft SQL server, SQL server user ID, and the corresponding password. Checking the Remember User ID and Password checkbox will encrypt and remember the user ID and password for the specified Microsoft SQL server so that each time that SQL server is selected from the same workstation, the login name and password will not need to be re-entered.
ODBC DSN data source	An ODBC DSN data source utilizes one of the above connection methods configured as a System DSN within the Data Sources (ODBC) component in the Windows Control panel. Enter the name of the DSN data source, SQL server user ID, and the corresponding password. If the DSN data source is configured to use Windows pass through authentication then leave the user ID and password empty. Checking the Remember User ID and Password checkbox will encrypt and remember the user ID and password for the specified SQL server so that each time that SQL server is selected from the same workstation, the login name and password will not need to be re-entered.

 

NOTE   Even if a login ID and password were entered as part of an ODBC DSN setup, they are only used by the ODBC DSN at the time of configuring the DSN. The login ID and password still must be entered in At Your Service in order to access the database through the DSN.

If the connection to the Microsoft SQL server was successful then you will be presented with a list of all At Your Service databases on the SQL server (i.e. all databases found where the database name is prefixed with “AYS_”). Select a database and click the Open button to open the database. Click the Cancel button or press the Esc key to cancel without opening a database.

Since most companies will only use a single database file, other methods for opening the file may be more convenient:

•      When there is no database open, the bottom of the File menu will contain a list of the last four databases that were opened. Select the desired database file from the menu to open it. Microsoft SQL Server database names in the menu will be prefixed with the abbreviation SQL.

•      To enable or disable automatic opening of the last database, first close all windows within the main program shell and then select File  AutoOpen Database from the main menu. If this feature is enabled (as indicated by a check mark on the dropdown menu) then the last database that was opened from that workstation will automatically be opened as soon as a user logs in (see Logging In and Out).

•      If a user does not have rights to select which database to open then selecting File  Open Last Database from the main menu will automatically open the last database that was open (see User Security).

See Configuring User Security on Microsoft SQL Server for information on configuring user permissions in the Microsoft SQL Server database.

(See also Opening an Existing Access Database, and Opening an Existing MySQL Database.)

Updating the Microsoft SQL Server Database Structure after a Program Upgrade

If a program upgrade has been installed since the last time that the database was opened then the database structure may need to be updated in order to be compatible with new program features.

NOTE  Before updating a database structure, you must log in as a user with database administrator rights and ensure that all other users are out of the database. This includes temporarily closing the At Your Service – Internet Utility (see Exiting the Internet Utility). A prompt will verify that all users are out of the database before proceeding.

Once a database structure has been updated in a multi-user environment then all users must have their program upgraded to the same version (see Installing on a Local Area Network).

More:

Closing a Microsoft SQL Server Database