How to convert an access database to an MS SQL database

It's mysql. I know your purpose to save space. But I don't know ...

1.Open SQL Server Enterprise Mananger"Enterprise Manager"On the SQL database you want to export, right-click the menu: All Tasks - >> Export Data.

2.An export wizard window appears.

Select the exported data source, which is the database you just selected, and if you find that it does not correspond, modify it by yourself.

3.Go to the selection of export to the target data source, here we want to convert it to an access database. Note: Select the data source type as Microsoft Access.

Click the button after "File Name (f)" to select the destination. MDB file.

4.Select Copy tables and views from database.

5.Notice that there is a "Transform" column on the right side of the table selection. SQL export only converts data types and doesn't take into account other scripts, so that's where the auto-numbering problem comes in. If there is an automatic number, be sure to click "Conversion".

6.We see back a Column Mapping and Conversion dialog. I noticed that the self-increasing employeeid int self-increment here has become long in access, which is definitely not right, long is not an automatic number, so I had to modify the table creation script, **The button "sql" in the red circle on the ** is definitely not right.

Open a small window to create talbe

The script circled in red is the access table creation script of the employeeid"not null"Plus on the front"identity (1, 1)"。

7.Execute it now.

Figure 1Open the SQL Server database management tool

Figure 2Select Add your database file

Figure 3Select the database you just added, right-click and select the task --- export data

Figure 4This step determines if the data source you want to export is correct, and then click Next

Figure 5In this step, select the target database as Microsoft Access Database

Figure 6Click to browse

Figure 7Select an access database file, which can be your new one

Figure 8If the access database has a username and password, enter it, and then click Next.

Figure 9You can choose a table according to the prompts, or you can write your own query, click the next step directly.

Figure 10Here you can select the table you want to export, and here you select all.

Figure 11Click on the next step here.

Figure 12Click Done here, or the next step.

Figure 13Then you can see the export.

Figure 14Open the access database file and the data will be imported

First, open the access software and create a new blank database. (Of course, you can also open a previously saved database).

By default, the software will open a blank form named [Table 1].

Change the name of the blank form table to [Test Table], and add fields and several rows of test data.

The default software toolbar is the [Start] toolbar, we click [Create] to enter the creation toolbar, and click [Query Design] in the toolbar.

The [Show Table] window pops up, and click [Close] to close the window.

At this time, the software will enter the [Design] toolbar, and we click [SQL View] on the left side of the toolbar.

SQL View] is selected as [Design View] by default, and we select [SQL View] in the drop-down menu.

At the bottom of the toolbar, a query window will automatically open, where you can enter SQL statements for query.

We enter a standard SQL query statement to query the data with gender 'female' in the [Test Table], and then click [Run].

After the SQL statement is executed, the query window is automatically converted to a table view to display the queried data in the form of a table.

I just switched my gotta from access to sql.

Your server has SQL installed.

Just use Enterprise Manager. Create a new database, then import data, select your access database for the source file and SQL database for the target file. Then it's time for the next step.

After the guide, I will go to the real work. The data structure of the two libraries is different. For example, the original note, you need to change it to nvarchar, the original automatic number, you need to change it to bigint, and you need to set up a mark, mark the seed, mark the increment, and so on.

All the field properties are done, and you have to change the program**.

At the very least, p needs to be changed. It is unpredictable what other ASP programs are to be changed. It's annoying. It's not a simple guide to the library.

I'm in Haidian, far away from you, so I won't sign up. If that doesn't work, I'll introduce you to one of our kids. Their procedures are more familiar than mine.

The steps are as follows:

2.As shown in Step 2, select Import Data3As shown in Figure Step 3, check Microsoft Access, then select the Access database file you want to convert, and enter the login information if there is a password.

4.As shown in Figure 4, enter the login information of the remote MSSQL database6As shown in Figure 6, select the auto-numbering field and click "Edit SQL".

7.As shown in Figure 7, find the corresponding field in the SQL statement section, add the command "identity(1,1)", and click OK, otherwise the newly created table cannot be automatically numbered.

8.Keep clicking Next until you see Figure 8, the conversion is successful Note: If the data in the access database has been deleted, then the automatically numbered data will be renumbered when imported to SQL Server, if you want to avoid this situation, you need to put the good SQL statement in step 5, put the good SQL statement into the query analyzer to run, the main thing is to let the conversion process no longer recreate this table, but directly attach the data to this table, as shown in the figure special, check " enable identity insert" to ensure that the data number is not set again.

You can export the database and select the access format.

Enterprise Manager, open the database that needs to be exported, right mouse button, all tasks Export database, there will be an export wizard, the data source is the database you open by default, the target can select a new access file, select the table you want to export.

It seems like trying to export only by table, and if you want to maintain the structure, just export the table and then create the view in access.

The grammar is somewhat different. Therefore, it is difficult for the software to be directly compatible with both databases. The statement needs some modification.

Enterprise Manager exports and it's OK to export a data source and target selected in the export place.