How to turn access into a SQL database

access|sql| Data | database

Many friends want to use the SQL2000 database programming method, but also suffer oneself is to learn access, to SQL is just a little understanding, here I give you the following reference---to convert access to SQL2000 methods and considerations

first, I said in the access2000,sql2000 between the conversion, the other I have not tried, I hope we have a lot of experimentation, there must be a way;

two, the 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", the selection of the "ABC" you just added, press "Next";

6, "purpose" does not need to modify, select the server (in general under their native local 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 on a Web site, 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,****, database selection 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, there will be a table of your own Access database, followed by the full selection, next;

10,dts Import/Export Wizard, see immediately run is selected press Next,

11, continue according to completion;

12, this step you will see your data being 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.

third, data modification

1, because there is no automatic number in the SQL2000, so your automatic numbering set of fields will become a non-empty field, this must be manually modified by these fields, and his label selection is, the seed is 1, the increment is 1,

2, in addition, when ACCESS2000 is converted to SQL2000, the field with the original attribute of Yes/no will be converted to a non-empty bit, at which point you must modify the property you want.

3, in addition, we should pay attention to the time function of the grasp. Access is a lot different from SQL.

Some experience with access to the MS SQL database

Automatic numbering type in 1.ACCESS database when converting, SQL Server does not set it to AutoNumber, we need to add identity to the SQL creation statement to indicate AutoNumber!

2. When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the range of datetime is larger than the smalldatetime type. I met this situation, with the smalldatetime type, conversion failure, but with the DateTime type, conversion success.

3. The SQL statements that operate on both databases are not identical, for example, when a record is deleted for an Access database: Delete * from user where id=10, and the SQL Server database is deleted by: delete user where id=10.

4. The date function is different, in the Access database processing, you can use the date (), Time (), and other functions, but in the SQL Server database processing, only datediff,dateadd functions, but not the date (), time () and other functions.

5. In Access database processing, SQL statements can be directly used in some VB functions, such as the CStr () function, but for SQL Server database processing, but not used.