How to convert Access to SQL Server by yourself _access

Before introducing the tutorial, let's get to know the access and SQL Server two databases.

Introduction to Microsoft Office access

Microsoft Office Access is a relational database management system published by Microsoft. Combined with the Microsoft Jet Database Engine and graphical user interface features, it is one of the system programs in Microsoft Office.

Microsoft Office Access Flaw

1, when the database is too large, the general Access database reaches about 100M when performance will begin to drop! (For example: Excessive number of visitors can easily cause IIS to suspend animation, excessive consumption of server resources, etc.)

2, it is easy to have a variety of database brush write frequency too quickly caused by database problems.

3,access database security is inferior to other types of databases.

4,access Forum is very easy to appear after the database problems, when the Forum database in more than 50M, post 50,000 or so, online also around 100 people, your forum is basically in the processing database to spend time, this time very likely will appear the database slow situation.

The general symptom is all pages that involve the database, all of a sudden it's surprisingly slow (5 seconds or even 10 seconds), and pages that involve HTML and pure ASP operations are normal, and then suddenly recover after a period of time (about 10 minutes or more) (my forum sometimes appears to be a problem). This time you can use the general ASP probe test, if the server's operation time is normal, generally is the database aspect problem.

Introduction to MS SQL Server

SQL is the abbreviation for the English Structured Query language, meaning the structured querying language. The main function of SQL language is to establish and communicate with various databases. According to the ANSI (American National Standards Association), SQL is used as the standard language for relational database management systems. SQL statements can be used to perform a variety of operations, such as updating data in a database, extracting data from a database, and so on. Currently, most popular relational database management systems, such as Oracle, Sybase, Microsoft SQL Server, Access, and so on, all adopt the SQL language standard.

SQL Server is a relational database management system. It was originally developed by Microsoft, Sybase and ashton-tate three companies and launched its first OS/2 version in 1988. After Windows NT was launched, Microsoft and Sybase went their separate ways in SQL Server development, and Microsoft ported SQL Server to Windows NT systems to focus on developing SQL Server's Windows NT version. Sybase is more focused on the application of SQL Server to the Unⅸ operating system.

Summary: For large databases, SQL Server is significantly better than access, and some sites may use an Access database for a variety of reasons, but as the web continues to evolve, Access databases are clearly unable to meet demand. So how does an Access database switch to SQL Server? Please read the following tutorial:

1, you must first install Microsoft Office Access 2003, and SQL Server2000.

2, the old database backup, backup completed, open the MDB database with Access 2003, open a warning, ignore it (security warning), press the Open key, open and press the toolbar--database utility--transform database--Convert to 2002-2003 format, Convert the database into a 2003 format.

3, after the conversion is completed and then opened with Access 2003, press the toolbar--database utility--Upsizing Wizard--New database--fill in the SQL database login name, password, and the database you want to create (ready to go to the new database), press Next, press "", and then press Next. Select all options, and then press next to choose "Do not make any changes to the application" and press finish.

4, open SQL Enterprise Manager-database-Select the appropriate database-right all Tasks-– generate SQL Script-general-all show-Write all object script--OK (remember where to store).

5, with Notepad to open just generated SQL script, in the formula bar--replace--Find content for "datetime" replaced by "smalldatetime"--replace all, and then in the formula bar-replace--find content for "nvarchar" Replace with "varchar" --Replace all, save exit after completion.

6, open SQL Enterprise Manager--database--click on the database to create a new database, and then in the Toolbar--sql Query Analyzer--FILE--open--"just generated SQL script"--query--execution, and then close the window.

7, go back to SQL Enterprise Manager--database--click on the database to create a new database, and then open the toolbar--Database Transformation Services--import data--next--Data source "Microsoft Access" file name "old database"--next--and then next-- Copy tables and views from source data--next--Select all--next--run immediately--next--complete.