3 steps to convert access to a SQL database connection

In fact, the basic common steps of the two are simple 3 steps:

1, transforming the MDB database into SQL SQL Tools has
2, change the database link string below for code:

Set conn=server.createobject ("ADODB. Connection ")
Sql= "Driver={sql Server}; Server=xd-y1w9b7; Uid=sa; Pwd=sa;database=ljl "
Conn.Open (SQL)

Finally, where SQL differs from Access · This should be noted below for detailed explanation:

Turn access into a SQL 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;

Second, 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 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 operate,), "Use Windows Authentication" refers to the operation of their own system administrator, "Using SQL Identity operation authentication "Can be used for Web site operations, recommended by the latter;"
7, select the "Use SQL Identity Operation Verification", fill in your username and password, I chose the system default number "SA", "* * * * * * *, the database select just new" ABC ", press" next ";
8, this step of the two single choice, "Copy tables and views from the data source" and "Specify the data to be transferred with a query instruction", select the former, press "next" to continue;
9, here will appear your own Access database table, press "Select All" after the next step;
The DTS Import/Export Wizard, see "Run Now" is selected to press "Next",
11, press "Finish" to continue;
12, this step you will see your data being imported into the SQL2000 inside, when the words "have successfully imported the XXX table into the database," and all the tables are preceded by a green tick, it means that all the data is successfully imported, if there is a problem or a red fork in front of the table, Indicates that the table did not import successfully, then go back and see if your actions are correct.

Third, data modification
1, because there is no "AutoNumber" inside the SQL2000, so your "AutoNumber" set of fields will become Non-empty fields, which must be manually modified by these fields, and his "mark" select "Yes", the seed is "1", the increment is "1",
2, in addition, ACCESS2000 converted to SQL2000, the original property is "Yes/no" field will be converted to a non-empty "bit", at this time you have to modify the properties you want;
3, in addition, we should pay attention to the time function of the grasp. Access is a lot different from SQL. Change access to SQL what are some places to note
After database import, automatically add fields need to be rewritten, all numeric types need to be increased in length, preferably in decimal.
All default values are lost. Mainly numeric types and date types.
All now (), time (), date () is changed to GETDATE ().
All DateDiff (' d ', time1, time2) are to be converted to DateDiff (day, time1, time2)
It is possible that some true/false types cannot be used, to become 1/0.
Note types are used by the cast (column as varchar).
CursorType to be changed to 1, that is, to open the database to give the first numeric parameter is 1, otherwise the record may be incomplete.
IsNull (RowName) to be changed to RowName = NULL
When the AutoNumber type in the database of access is converted, SQL Server does not set it to AutoNumber, and we need to add identity to the SQL creation statement to represent the AutoNumber!
When converting, date-related fields, SQL Server defaults to smalldatetime type, we'd better turn it into a datetime, because the datetime type is larger than the smalldatetime type. Sometimes when using a smalldatetime type, the transformation fails, and when the datetime type is used, the conversion succeeds.
The SQL statements that operate on the two databases are not identical, for example: "Delete * from user where id=10" is used when deleting records for an Access database, and the SQL Server database is deleted by: "Delete user where Id=10 ".
Date functions are not the same, 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.
In the processing of Access database, some VB functions can be used directly in SQL statements, such as the CStr () function, but not in SQL Server database processing.

Some experience with access to SQL Server 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 the two 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.