How to convert Access to SQL database, and convert access to SQL database

How to convert Access to SQL database, and convert access to SQL database

First of all, I am talking about the conversion between ACCESS2000 and SQL2000, and I have not tried any other products yet. I hope you can experiment more and there is definitely a solution.

Conversion Method

1. Open "database source" in "Administrative Tools" under "Control Panel ".

2. Press "add" to add a new data source, and select "Driver do microsoft Access (*. mdb) ", a box will appear, enter the name you want to write in" database source ", I name it" ABC ", it means no need to fill in, then, find your database address and select the address (Note: Back up your own ACCESS database first), and then confirm. The data source is created here, and the conversion is left.

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

4. Select the newly created database, right-click the database, select "import data" under "all tasks", and click "Next" to continue.

5. Select "Driver do microsoft Access (*. mdb) ", in" User/system DSN ", select the" ABC "you just added and click" Next ".

6. You do not need to modify the "purpose". Select a server (generally your local machine, or the server address or local area network address) to check whether your permissions can be operated ,), to use WINDOWS identity authentication, you can use your system administrator identity. SQL identity authentication can be used for website operations. The latter is recommended.

7. After you select SQL authentication, enter your username and password. I chose the default system number sa, *** and select the newly created ABC for the database, click Next.

8. Select two items in this step. Copy the table and view from the data source and use a query command to specify the data to be transmitted. Select the former and continue with the next step.

9. here you will see your own ACCESS database tables. After you select all, next step.

10. In the DTS import/export wizard, check that the running is selected and press next.

11. Continue by completion.

12. In this step, you will see that your data has been imported into SQL2000. When the words "XXX tables have been successfully imported to the Database" appear, and there is a green check in front of all the tables, it indicates that all data is successfully imported. If there is a problem in the middle or there is a red cross in front of the table, it indicates that the table has not been imported successfully, and you have to check whether your operations are correct.

Recently, another program is being upgraded to support multiple databases.
The original program database is SQL Server, so the "Import and Export data" function of SQL Server is used to convert an SQL Server database into an Access database, but some problems are found, however, the solution is also found and recorded here:

1. The conversion program converts the views of SQL Server to tables instead of Access queries;
2. When the default value of a field is set to null, SQL Server uses "and Access uses "";
3. Access does not include the GetDate () function. Instead, use the Now () function;
4. Access does not have the host_name () function used by SQL Server to obtain the client machine name;
5. Access does not contain the Case When Then statement, but can be simulated using the IIF () function;
6. Discovery: Access uses the Visual Basic language structure and functions;
7. The conversion program correctly converts the is null attribute of the SQL Server field;
8. Access also supports multi-field indexing, but the setting method is a bit special (see help );
9. values 1 and 0 of the bit type in SQL Server, which are True and False in Access;
10. When using multiple left join statements in Access, brackets must be used to define them;
11. Access SQL statements are not annotated, and SQL Server/***/cannot be used in general;
12. An Insert statement runs normally in the Access query, but the "Insert Into statement syntax error" appears in the program ", later, it was found that a column name in the statement is the keyword of Access (which can be defined by [] to solve the problem), but it is strange that the statement is placed in the Access query and executed without errors.

Check the converted Access database for the following items to ensure consistency with SQL Server:

1. Primary Key. The converted Access database does not have a primary key and needs to be set by yourself;
2. Auto-increment field. The conversion program converts the auto-increment field of SQL Server to the numeric type. You must manually change it to the "auto-number" type of Access;
3. default value. The conversion program does not convert the default values set in SQL Server. You need to set them manually;
4. bigint fields. The conversion program converts the bigint of SQL Server to a decimal number, and must be manually adjusted to the Access integer or long integer;
5. index. The conversion program does not convert the index. You need to manually create an index in Access.

In Delphi, to allow the program to support both Access and SQL Server, pay attention to the following aspects:

1. When SELECT * FROM Books WHERE RegDate = '1970-5-1 'is used in Access, the following error occurs: "Data Type mismatch in standard expressions" (RegDate is a date type), SELECT * FROM Books WHERE RegDate = #2007-5-1 # or SELECT * FROM Books WHERE RegDate = CDate ('2017-5-1 ') must be used ′);
However, in Access, you can use single quotes to define the date in Insert, delete, and update operations.
2. Do not use the bigint type of SQL Server, especially when the field is auto-incrementing;
3. The varchar (text) type of Access is only 255 at the maximum. Therefore, if a text field is greater than 255, it is best to define it as the remarks type (Access) or text type (in SQL Server );
4. the following error occurs in Access: the parameter object is incorrectly defined. Provide inconsistent or incomplete information. Set the ParamCheck of the corresponding Query to False;
5. The logical values in Access are-1 and 0 in the database, while those in SQL Server are 1 and 0. Therefore, there is a compatibility problem when writing statements such as BoolField = 1, and it should be changed to BoolField <> 0;
6. A Query with a primary key in Access can be updated, but SQL Server does not.

The above are the methods and precautions for mutual conversion between Access and SQL databases.