First of all, I am talking about converting between ACCESS2000 and SQL2000. I have not tried the others yet. I hope everyone can experiment more, and there will definitely be a solution.
Method of conversion
1. Open "Database Source" in "Administrative Tools" under "Control Panel".
2. Press "Add" to add a new data source, select "Driver do microsoft Access (*.mdb)" in the selection column. After completion, a box will appear. Enter the name you want to write in "Database Source". I named it "ABC", which means you do not need to fill in. Then, press the selection below to find your database address and select (note, please back up your ACCESS database first), and then confirm. 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 created database, right-click, 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 the "User/System DSN", select the "ABC" you just added, and press "Next".
6. The "purpose" does not require modification. Select the server (usually your own local local, or you can also select the server address or LAN address to determine whether your permissions can be operated.), use WINDOWS authentication refers to operating with your own system administrator identity. Use SQL identity operation to verify it can be used for website operations. The latter is recommended.
7. After selecting SQL identity verification, fill in your username and password. I chose the system default number sa, ****, and select the newly created ABC in the database. Press Next.
8. Two single choices in this step, copy the table and view from the data source and use a query instruction to specify the data to be transferred. Select the former and press Next to continue.
9. The table of your own ACCESS database will appear here. After selecting all, the next step will be shown.
10. DTS Import/Export Wizard, see the run immediately and is selected and press Next.
11. 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 all tables have a green check in front of it, it means that all data is successfully imported. If there is a problem in the middle or there is a red fork in front of the table, it means that the table has not been imported successfully. At this time, you have to go back to check whether your operation is correct.
Recently, another program is being upgraded to support multiple databases.
The original program database was SQL Server, so SQL Server's "Import and Export Data" function was used to convert a SQL Server database into an Access database, but some problems were found, but solutions were found, and they were recorded here:
1. The converter will convert the SQL Server view into a table instead of the Access query;
2. When setting the default value for the field to null characters, SQL Server uses ", and Access uses "";
3. There is no GetDate() function in Access, and the Now() function should be used instead;
4. Access does not have the host_name() function used by SQL Server to obtain the client machine name;
5. There is no Case When Then statement in Access, but the function can be implemented using the IIF() function;
6. Discovery: Access uses Visual Basic's language structures and functions;
7. The converter will correctly convert the IS NULL attributes of the SQL Server field;
8. Access also supports multi-field indexing, but the setting method is a bit 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, relevant definitions must be made in brackets;
11. Access's SQL has no comment statements, and SQL Server's /**/ cannot be generalized;
12. An Insert statement runs normally in the Access query, but "A syntax error of the Insert Into statement" appears in the program. Later, I found that a column name in the statement is the keyword of Access (definition using [] can solve the problem), but strangely, there will be no errors when placing the statement in the Access query.
The following items are checked for the converted Access database to ensure consistency with SQL Server:
1. Primary key. The converted Access database has no primary key, so it needs to be set by yourself;
2. Self-increase field. The converter will convert the self-increment field of SQL Server into a numeric type, and needs to be manually modified to the "auto-number" type of Access;
3. Default value. The converter will not convert the default values set in SQL Server and need to be set manually;
4、bigintType fields。 The converter will convert the bigint of SQL Server into a decimal and must be manually adjusted to an integer or long integer of Access;
5. Index. The converter will not convert the index and needs to manually create the index in Access.
Under Delphi, if you want the program to support Access and SQL Server at the same time, you need to pay attention to the following aspects:
1. Use SELECT * FROM Books WHERE RegDate = ‘2007-5-1′ in Access. There will be an error like “data type mismatch in standard expressions” (RegDate is date type), and SELECT * FROM Books WHERE RegDate = #2007-5-1# or SELECT * FROM Books WHERE RegDate = CDate('2007-5-1');
However, in Access, using single quotes to define dates in Insert, delete and update can be performed normally.
2. Try not to use the bigint type of SQL Server, especially if the field is self-incremented;
3. The maximum varchar type of Access is only 255, so if a text field is greater than 255, it is best to define it as a comment type (in Access) or text type (in SQL Server);
4. In Access, the following error usually occurs: the parameter object is abnormally defined. Inconsistent or incomplete information is provided. Just set ParamCheck of the corresponding Query to False;
5. The logical values in Access are -1 and 0 in the library, while in SQL Server are 1 and 0, so there are compatibility issues with writing statements like BoolField = 1, and should be changed to BoolField <> 0;
6. Query with primary key in Access can only be updated, but SQL Server does not have this requirement.
The above are the methods and precautions for converting Access and SQL databases.