How to Export SQL Server Database to Access?

Microsoft SQL Server and Microsoft Access are the two acceptable tools provided by Microsoft.  Both the applications are Database Management System which serves as a Database Server to its customers. The key advantage of using MS Access over MS SQL Server isits ability to frame such a software system that manages a wide range of services very instantly.

Microsoft SQL Server has become a preferred choice for most of the organizations as it provides some best features to handle large amount of data and information. Process of storing data in SQL server is different to MS Access. You can develop an interactive user-friendly interface by using the components of MS Access. MS SQL Server when compared to MS Access, doesn’t provide front-end framework. MS Access can only handle data up to 1 GB whereas SQL Server has the ability to contain terra bytes of data.

Due to some compatibility issues, conversion of MDF file to Access is required. If you want to import MDF files in to Access, then you must create backup of original MDF file before importing. Backing up secures your data. Before converting, copy these files to MS SQL Server.

Also Read: How to Repair MDF File of Microsoft SQL Server?

Copy MDF Files to Microsoft SQL Server – Steps

  1. Press Start button > All Programs > SQL Server 2008 > right click on SQL Server Management studio > select run as administrator
  2. A pop up box will appear, select Yes. Now, “Connect to server” window will appear. Enter the details as follows:
    Server type: database engine
    Server name: PC name
    Authentication: Windows authentication requires no login credentials. Enter the details if you choose SQL Server authentication.
    Click Connect button.
  1. Once it is connected, navigate to object explorer and right click on “Databases” folder, then select “Attach” option.
  2. “Attach databases” window will pop up. Click Add button and select the entire database you want to import and click on OK.
  3. Select added databases and click OK. MDF files are now copied to MS SQL Server.

Import MDF Files to Microsoft Access – Steps

  1. Select Start button > Control Panel > Administrative Tools.
    1
  2. After selecting Administrative tools, window will pop up. Select Data Source (ODBC) and double click on it.
    2
  3. “ODBC Data Source Administrator” window will appear. Select Add button and all the available drivers will be listed. Choose SQL Server and click on Finish button.
    34
  4. A new window will appear, specify the name and choose the server name from the list, then click on Next button.
    5
  5. Provide the login credentials in the new window according to the authentication mode.
    6
  6. Select “default database” checkbox and select the databases need to import. Click on Next button.
  7. Uncheck “Perform translation for character data” and click Finish button.
  8. Now, select “Test Data Source”. Click OK if it shows the message “tests completed successfully”.
  9. Select the name which you have specified earlier and again click OK.
  10. Go to start menu > All Programs > MS Office > MS Access and select New option.
  11. On the left pane, enter the name of new database file and click Create button.
  12. Navigate to menu bar > External tab > More > ODBC Database > Click OK on the appeared window.
  13. Small window will pop up. Choose Machine Data Source > select the same data source as selected before > click OK.
  14. List of database will appear. Choose tables which you want to import into Access and then click on OK. Click Close on the next window appeared.
  15. Selected table will open in Access and to check the table data, click on table name. Finally, MDF file is imported to MS Access.

Also Recommended: Software to Repair DBF Files: Corrupt DBF File

Advance Way to Repair Multiple BKF Files

Though the solution for converting MDF file to MDB format is a lengthy process, but it gives a guaranteed solution and safely imports your data from MDF file to MS Access without changing the content.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s