Access to SQL database methods _ database Other

First of all, I said that the conversion between the access2000,sql2000, the other I have not tried, I hope we have a lot of experimentation, there must be a way.

Method of conversion

1, open the 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 bar, and a box will appear in the "Database Source" to enter the name you want to write, I call "ABC", stating that you do not need to fill in, then, the following selection , look for your database address and check (note, please back up your Access database first), and then OK. The data source has been built here, and the rest is converted.

3, open SQL2000 Enterprise Manager, enter the database, create a new empty database "ABC".

4, select the newly established database, press the right mouse button, select "All Tasks" under "Import Data", press "next" to continue.

5. In the database source Drop-down but select "Driver do Microsoft Access (*.mdb)", in "User/System DSN", choose the "ABC" you just added and press "next".

6, "purpose" does not need to be modified, select the server (generally under their own native local, you can also select the server address or LAN address, determine whether your permissions can operate,), using Windows authentication refers to use their own system administrator identity operation, Use SQL identity operations to validate actions that can be used with Web sites, and recommend the latter.

7, select the use of SQL identity operation verification, fill in your username and password, I chose the system default number sa,****, the database select just new ABC, press the next step.

8, this step of the two single choice, from the data source to copy tables and views and with a query instruction to specify the data to be transferred, select the former, press the next step to continue.

9, here will appear in your own Access database table, after the full selection, next.

10, DTS Import/Export Wizard, see immediately run is selected to press the next step.

11. Continue as finished.

12, this step you will see your data is imported into the SQL2000 inside, when the word "XXX" has been successfully imported into the database, and all the tables are preceded by a green tick, it means that all the data is successfully imported, and if there is a problem halfway through or the table has a red fork, the table is not imported successfully. It's time to go back and see if your actions are correct.

A program has recently been upgraded to support multiple databases.
The original program database was SQL Server, so using SQL Server's "Import and Export Data" feature to convert a SQL Server database to an Access database, but found some problems, but also found a solution, recorded here:

1. The conversion program converts SQL Server views into tables instead of access queries;
2. When you set the default value to null for the field, SQL Server uses ", and Access uses" ";
3, Access does not have the getdate () function, you should use the now () function instead;
4, Access does not have SQL Server to obtain the client machine name HOST_NAME () function;
5, Access does not have case when then statement, but you can use the IIF () function to simulate the implementation of functions;
6. Discovery: Access uses the language structure and functions of Visual Basic;
7, the conversion program will correctly convert the SQL Server field is NULL property;
8, Access also supports multiple-field indexing, but it's a bit special to set the method (see Help);
9, the bit type in SQL Server 1, 0 values, in Access is true and false;
10. When you use a statement in Access with multiple left joins, you must make the relevant definition in parentheses;
11, Access's SQL does not have annotation statements, SQL Server/**/can not be universal;
12. An INSERT statement works correctly in an Access query, but "syntax error in Insert into statement" appears in the program, and it turns out that a column name in the statement is the key to access (with [] defining the problem). But the strange thing is that the statement is executed in a query in Access without error.

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, you need to set it yourself;
2. Self-adding fields. The converter converts the self-increasing field of SQL Server to a numeric type, which you manually modify to the AutoNumber type of access;
3, the default value. The converter does not convert default values set in SQL Server, which need to be set manually;
4, bigint Type field. The converter converts the bigint of SQL Server to decimal numbers, which must be manually adjusted to an integral or long integer of access;
5, Index. The translator does not convert the index, and you need to manually establish an index in access.

Under Delphi, you want programs to support access and SQL Server at the same time, you need to be aware of the following:

1, in Access using SELECT * from books where regdate = ' 2007-5-1′ is the error of "data type mismatch in standard expression" (RegDate is a date type), must use SELECT * FROM books where regdate = #2007 -5-1# or SELECT * from books WHERE regdate = CDate (' 2007-5-1′);
In Access, however, the use of single quotes to define dates in Insert, delete, and update can be performed normally.
2, try not to use the SQL Server bigint type, especially the field is the case of the increase;
3. Access has a maximum of 255 varchar (text) type, so if a text field is greater than 255, it's best to define it as a memo (in Access) or text (in SQL Server);
4. In Access, the following error generally occurs: The Parameter object is not defined correctly. Provides inconsistent or incomplete information. Set the corresponding query's paramcheck to false;
5, the logical values in access to 1 and 0 in the library, and SQL Server 1 and 0, so write Boolfield = 1 Such statements have compatibility issues, should be changed to Boolfield <> 0;
6. Query with a primary key in Access can be updated, and SQL Server has no such requirement.

That's how Access and SQL databases convert to each other.