Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to convert Access into SQL database". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

The method of conversion

1. Open the Database Source in the Administrative tools under Control Panel.

2, press "add" to add a new data source, select "Driver do microsoft Access (* .mdb)" in the selection bar, after completion will appear a box, enter the name you want to write in the "database source", I named it "ABC", indicating that you do not need to fill in, then, press the selection below, find your database address and select (note, please back up your own ACCESS database first), and then determine. The data source is built here, and the rest is converted.

3. Open SQL2000 Enterprise Manager, enter the database, and create an empty database "ABC".

4. Select the newly established database, press the right mouse button, select "Import data" under "all tasks", and press "next" to continue.

5. In the database source drop-down, select "Driver do microsoft Access (* .mdb)", in "user / system DSN", select the "ABC" you just added, and press "next".

6, "purpose" does not need to be modified, select the server (generally for your own native local, you can also select the server address or LAN address to determine whether your permissions can be operated,), using WINDOWS authentication refers to the operation with your own system administrator identity, using SQL authentication operation can be used for website operations, the latter is recommended.

7. Choose to use SQL authentication and fill in your user name and password. I choose the system default number, sa,****, database, and select the newly created ABC, and press next.

8. for the two individual choices in this step, copy the table and view from the data source and specify the data to be transferred with a query instruction, select the former, and press the next step to continue.

9. The table of your own ACCESS database will appear here. Press select all and then go to the next step.

10. DTS Import / Export Wizard, see run now is selected and press next step.

Press finish to continue.

12. In this step, you will see that your data is imported into SQL2000. When the words "XXX tables have been successfully imported into the database" appear, and there are green ticks in front of all tables, it means that all data has been successfully imported. If there is a problem midway or there is a red cross in front of the table, it means that the table has not been successfully imported. At this time, you have to go back and check whether your operation is correct.

Recently, another program is being upgraded to support multiple databases.

The original program database was SQL Server, so a SQL Server database was converted into an Access database using SQL Server's "Import and Export data" function, but some problems were found, but solutions were also found, which are recorded here:

1. The conversion program converts the view of SQL Server into a table instead of a query of Access

2. When setting a null character by default for a field, SQL Server uses "and Access uses".

3. There is no GetDate () function in Access, so Now () function should be used instead.

4. Access does not have the host_name () function that SQL Server uses to get the client machine name.

5. There is no Case When Then statement in Access, but you can use the IIF () function to simulate the function.

6. Found that Access uses the language structure and function of Visual Basic.

7. The conversion program will correctly convert the IS NULL property of the SQL Server field.

8. Access also supports multi-field indexes, but the setting method is a little special (see help)

9. The 1 and 0 values of bit type in SQL Server are True and False in Access.

10. When using multiple LEFT JOIN statements in Access, you must define them with parentheses

11. The SQL of Access has no comment statement, and the / * / of SQL Server cannot be used.

12. An Insert statement runs normally in the Access query, but there is a "syntax error of the Insert Into statement" in the program. Later, it is found that a column name in the statement is the keyword of Access (defined with [] to solve the problem), but it is strange that there will be no error when the statement is placed in the query of Access.

The converted Access database needs to be checked for the following items to ensure consistency with SQL Server:

1. Primary key. The converted Access database does not have a primary key, so you need to set it yourself.

2. Self-increasing fields. The conversion program converts the self-increment field of SQL Server to a numeric type, which needs to be manually modified to the "auto-numbering" type of Access.

3. Default value. The converter does not convert the default values set in SQL Server. You need to set them manually.

4. Bigint field. The conversion program converts the bigint of SQL Server to decimals and must be manually adjusted to the integer or long integer of Access.

5. Index. The converter does not convert the index and needs to build the index manually in Access.

Under Delphi, if you want your program to support both Access and SQL Server, you should pay attention to the following aspects:

1. Using SELECT * FROM Books WHERE RegDate = '2007-5-1' in Access will cause an error such as "data type mismatch in standard expression" (RegDate is date type). You must use SELECT * FROM Books WHERE RegDate = # 2007-5-1 or SELECT * FROM Books WHERE RegDate = CDate ('2007-5-1').

However, in Access, it is possible to use single quotation marks to define dates in Insert, delete, and update.

2. Try not to use the bigint type of SQL Server, especially if the field is self-increasing.

3. The maximum varchar (text) type of Access is only 255.If a text field is greater than 255, it is best to define it as comment type (in Access) or text type (in SQL Server).

4. The following errors generally occur in Access: abnormal definition of parameter objects. Inconsistent or incomplete information is provided. Set the ParamCheck of the corresponding Query to False.

5. The logical values in Access are-1 and 0 in the library and 1 and 0 in SQL Server, so there is a compatibility problem in writing statements such as BoolField = 1 and should be changed to BoolField 0.

6. Only Query with a primary key in Access can be updated, but SQL Server does not require this.

This is the end of the content of "how to convert Access into SQL database". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.