?
Solved

Export an Access Database to SQL mdf file

Posted on 2015-02-10
6
Medium Priority
?
196 Views
Last Modified: 2016-02-11
I want to export an Access database that I created to a SQL database with an mdf extension. How do I do that in Access 2010? Eventually, I want to use it in Microsoft SQL 2008 R2 Express for queries.
0
Comment
Question by:chaverly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 2000 total points
ID: 40601716
For a small database:

Create a new SQL database.
In access, select table/Query in navigation pane > External Data > Export > More > ODBC Database

Follow instructions.
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40602459
With A2010 and earlier, you can use the built in upsizing wizard.  It is on the External data ribbon.  This feature was dropped from A2013 and that leaves you with SSMA as recommended by SStory or a third party tool unless you want to do the tables one at a time as hnasr recommended.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 26

Expert Comment

by:Nick67
ID: 40603591
it could be coded in VBA, but that is a lot of work to go to when the very good, free SSMA tool exists.
Be aware that SSMA comes in 32 bit and 64 bit flavours.
Both run on x86 and x64 platforms.
The correct one to use depends on the version of the connectivity components you have installed.
If you have 32 bit ODBC drivers, you need the 32 bit version
If you have 64 bit ODBC drivers, you need the 64 bit version.
Both install from the download.
Try the 32 bit first if you do not know for certain (it's highly unlikely) that you have installed 64 bit Office.
0
 

Author Closing Comment

by:chaverly
ID: 40613312
Thank you
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40613340
Welcome!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question