Introduction to Databases and Microsoft Access 5/8

Upsizing an Access database
What is SQL server?
Why SQL Server?
Creating an ODBC DSN

* Upsizing a Database

Over time, most database applications grow, become more complex, and need to support more users. Upsizing from MS Access 2000 to Microsoft SQL Server can help to optimize database and application performance, scalability, security, reliability, recoverability, and availability. There is frequent debate on Oracle Vs MS SQL Server, which was initially viewed as a scaled down product. Recent versions like 7.0 and 2000, however, have proven quite popular, and drive a large number of websites, including names like ASB Bank, Barnes and Noble, CBS Sportsline, CompUSA, Domino’s Pizza, Hewlett-Packard, Microsoft (surprise, surprise), Pennzoil, etc.

Upsizing wizards provided with MS Access 2000 and SQL Server 7.0 make it a pretty simple process.

1.    Open the database in MS Access. Then click on the Tools menu, then on Database Utilities, then on Upsizing Wizard

2.    Select Create New Database and click on Next

3.    In the next screen, first select the SQL Server you want to use form the drop down list. In the next section, fill in the login and password of a pre-existing account on the server, which has been authorised with CREATE DATABASE privileges. Lastly, name your new database.

4.    Next, add the tables which you’d like to export to the new database.

If you want to export all the tables, just click on the >> button.

5.    In the first part of the next screen, you can select which table attributes you want to upsize.

For more information on any of the options, click on the Help button, which will provide you with comprehensive information. In case you’re unsure about any options, stick with the default values as shown above.
To create an empty database with the existing structure of your current Access application, check the “Only create the table structure; don’t upsize any data” box.

6.    In the next screen, select No application changes if you only want to upsize your data and not make any other changes to your existing Access database application.

If you select Link SQL Server tables to existing application, the Upsizing Wizard can modify your Access database so that your queries, forms, reports, and data access pages use the data in the new Microsoft SQL Server database rather than the data in your Microsoft Access database.  Once again, for more detailed information, click on the help button.

7.    At the Finish splash screen, click on the Finish button.

* What is SQL server?

·        MS SQL Server is a client/server relational database system.

·        Client/server, also referred to as distributed computing, means that all of the data processing of the program does not occur on a single computer. With SQL server, the database engine part of the program resides on a designated server computer, whereas the other part of the program, the client interface resides on a user’s desktop computer. The components of the client/server system communicate over a network as if they were one and the same program.

·        A relational database system is a type of database architecture that is divided into multiple tables of data, each of which is further divided into rows (records) and columns (fields).

·        MS SQL Server isn’t a physical server itself, but a computer program that runs on a physical server. Only one copy can run on a single physical server at a time.

·        Each copy of SQL server has the ability to manage one or more separate databases. Each database is distinct from the others, and managed separately.

* Why SQL Server?

·        Cost of ownership is lower than competitors

·        Runs under NT Server, Windows 95/98/2000

·        Scalable to meet Enterprise-wide needs: SQL Server 7.0 Enterprise Edition now supports databases over a terabyte in size, and up to 32 CPUs

·        Supports Data Replication to virtually any other kind of database, from MS Access to Oracle.

·        Supports Data Marts and Data Warehouses

* Creating an ODBC DSN

Open Database Connectivity (ODBC) is a standard or open application programming interface (application program interface) for accessing a database. By using ODBC statements in a program (or an ASP page, for example), you can access files in a number of different databases, including Access, SQL Server, dBase, DB2, Excel, and Text. In addition to the ODBC software, a separate module or driver is needed for each database to be accessed. The main proponent and supplier of ODBC programming support is Microsoft.

ODBC is based on and closely aligned with the the Open Group standard Structured Query Language (Structured Query Language) Call-Level Interface. It allows programs to use SQL requests that will access databases without having to know the proprietary interfaces to the databases. ODBC handles the SQL request and converts it into a request the individual database system understands.

An ODBC System data source stores information about how to connect to the indicated data provider. To create an ODBC system Data Source Name (DSN) on a windows 9x/ME system,

1.    Open Control Panel

2.    Open ODBC Data Source Administrator.

3.    Click on the System DSN tab), and click on the Add button

4.    Choose the appropriate driver (e.g. MS Access Database) and click on Finish

5.    Fill out a DSN Name and Description in the box that pops up, and click on Select in the Database section.

6.    Find the file, and click on Ok.

7.    Click on Ok to close the ODBC Microsoft Access Setup.