Mutual conversion of Access and MS SQL Server databases

Convert an Access database data migration to SQL Server 2000

The first step is to open SQL Server 2000 service management, open Enterprise Manager, and create a new database with the name "Access data Conversion";

In the second step, run import and export data and open the DTS Import/Export Wizard. When you select a data source, you have two choices: "Driver do Microsoft Access (*.mdb)" or "Microsoft Access" (the ODBC driver selected by Microsoft, which is the data interface provided by the latter), which is described separately below:

If you select the former "Driver do Microsoft Access (*.mdb)", you need to create a new file data source, select the driver to install the data source as "Driver do Microsoft Access (*.mdb)", and the file data source for this connection is saved with the name "Db.dsn". In the ODBC Microsoft Access Installation dialog box that pops up, when you choose to confirm the source Access database that you want to convert, return to select File DSN as DB.DSN, and if you choose Microsoft Access, it's relatively straightforward. Simply select the path to confirm the source Access database. Third, select the target database as Access Data transformation, select Copy tables and views from source database in specify table replication or query, and select audit-related data tables for import/export operations. This imports the access data into the SQL SERVER Access Data Transformation database.

Convert SQL Server 2000 database data migration to access

The first step is to open access and create a new database named SQL Server Data transformation.

In the second step, on the File menu, open Get External data à import, and in the Import dialog box, select the Type ODBC database (), open the Select Data Source dialog box;

The third step is to open the Create new data source to SQL Server dialog box by creating a new file data source, selecting the driver to install the data source as SQL Server, and saving the file data source for this connection with the name "SQL Server.dsn". This will help create an ODBC data source that can be used to connect to SQL Server. In this dialog box, select the name of the connected SQL Server server and how to log in, and in the change default database Drop-down list, select the name of the SQL Server database for which you want to convert data, and then test that the ODBC data source is connected successfully, and if so, go to the Import object dialog box;

Step fourth, on the Table tab of the Import Object dialog box, select the SQL Server table that you want to import into Access, and click OK to convert the data in SQL Server 2000 to the Access data format.