First of all, I was talking about conversion between access2000 and sql2000.I haven't tried other things,Hope you guys experiment a lot,There must be a way.

Conversion method

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 bar, a box will appear after completion,Enter the name i want to write in "Database Source",I named it "abc", which means no need to fill in,Next, press the selection below,Find your database address and select (note, please backup your own access database first), and then confirm.The data source is built here,The rest is converted.

3. Open sql2000 Enterprise Manager,Into the database,Create a new empty database "abc".

4. Select the newly created database,Right-clickSelect "Import data" under "All tasks" and press "Next" to continue.

5. Select "driver do microsoft access (*. Mdb)" from the database source drop-down list. In "User/System dsn", select the "abc" you just added and press "Next".

6, "purpose" does not need to be modified,Select the server (usually local, you can also choose the server address or LAN address,Determine if your permissions are operational,), Using windows authentication means operating as your own system administrator,Using sql identity operation authentication can be used for website operations,The latter is recommended.

7. After selecting to use sql identity operation verification,Fill in your username and password,My own choice is the system default number sa, ****, the database selects the newly created abc, and clicks next.

8. Two single choices for this step,Copy tables and views from a data source and specify the data to be transferred with a single query,Choose the former,Press next to continue.

9, here will appear your own access to the database table,After pressing Select All,Next step.

10. dts import/export wizard,See Run now is selected and press Next.

11. Press Done to continue.

12, this step you will see your data is imported into sql2000, when the words that have successfully imported xxx tables into the database appearAnd all the tables have green ticks in front of them,This means that all data was successfully imported,If something goes wrong or there is a red cross in front of the watch,That the table was not successfully imported,Then go back and see if your operation is correct.

I have recently upgraded another program,Make it support multiple databases.

The original program database was sql server, so the "import and export data" function of sql server was used to convert a sql server database into an access database, but some problems were found.But also found a solution,Also recorded here:

1. The conversion program will convert the view of sql server into a table.Rather than access queries;

2. When setting the default value for the field as a null character,Use "" for sql server and "" for access;

3. There is no getdate () function in access, you should use now () function instead;

4, access does not have the host_name () function used by the SQL server to obtain the client machine name;

5. There is no case when then statement in access, but you can use iif () function to simulate the function;

6, found:access uses the language structure and functions of visual basic;

7, the conversion program will correctly convert the is null attribute of the SQL server field;

8, access also supports multi-field indexing,Just the setting method is a bit special (see help);

9, bit type 1, 0 value in sql server, true and false in access;

10. When using multiple left join statements in access,Relevant definitions must be made in brackets;

11, access SQL does not have a comment statement,/**/of sql server cannot be universal;

12. An insert statement runs normally in an access query.However, the "insert into statement syntax error" appeared in the program, and it was later found that a column name in the statement was the keyword of access (using [] to resolve the problem), but it was strange to place the statement in The query executes without error.

The following items need to be checked for the converted access database,To ensure consistency with sql server:

1. Primary key. The converted access database has no primary key.Need to set it yourself;

2. Increment field.The conversion program will convert the self-incrementing field of the sql server to a digital type.Need to be manually modified to the "automatic numbering" type of access;

3. The default value. The conversion program does not convert the default values ​​set in SQL Server.Need to be set manually;

4, bigint type field. The conversion program will convert the bigint of sql server to decimal,Must be manually adjusted to access integer or long integer;

5. Indexing. The conversion program does not convert the index,Need to be manually indexed in access.

Under Delphi, if i want the program to support both access and sql server, you need to pay attention to the following aspects:

1. Use select * from books where regdate="2007-5-1 'in access" will cause "Data type mismatch in standard expression" error (regdate is date type), you must use select * from books where regdate=#2007-5-1 #or select * from books where regdate=cdate ("2007-5-1 ');

However, in access, insert, delete, and update using single quotes to define the date can be performed normally.

2. Try not to use the bigint type of sql server, especially if the field is self-incrementing;

3. The maximum varchar (text) type of access is only 255, so if a text field is greater than 255, it is best to define it as a memo type (access) or text type (in SQL server)

4. The following error usually occurs in access:the parameter object is not properly defined.Inconsistent or incomplete information was provided.Set the corresponding query's paramcheck to false;

5. The logical values ​​in access are -1 and 0 in the library, and 1 and 0 in sql server, so writing statements such as boolfield=1 has compatibility problems.Should be changed to boolfield<>0;

6. Only queries with primary key in access can be updated.SQL Server does not have this requirement.

  • Previous JavaScript game
  • Next windowclose ();A summary of the JavaScript code for closing the browser window