Geeks With Blogs
Tony Wissler My... Blog...

In my little corner of the world, I have seen this issue come up three times by three different people in the past 7 days so I thought I would posts this.  I actually just went through this myself a little bit ago:

  • Open Microsoft SQL Server Management Studio Express
  • Create a database for the import, if necessary
  • Open the Microsoft Access database that will be imported into SQL
  • Switch to “Tables” in Microsoft Access
  • Right click on the Table to be exported
  • Select “Export”
  • In the ‘Save Type As…’ field select “ODB Databases()”
  • Name the Table and Press “OK”
  • Press the “New…” button
  • Select “SQL Server” and Press “Next>”
  • Enter a name for the Data Source then Press “Next>”
  • Press “Finish”
  • Enter a Description if necessary
  • Select the SQL Server to connect to and Press “Next>”
  • Select the correct Authentication settings and Press “Next>”
  • Check “Change the default database to:” and Select the correct Database then Press “Next>”
  • Press “Finish”
  • Press “Test Data Source…” to very the information entered was correct
  • If test completed successfully then Press “OK”
  • Press “OK”
  • In “SQL Server Login” dialog box, enter correct authentication information and Press “OK”
  • Export should occur without any further dialog boxes or prompts
  • Switch back to the Microsoft SQL Server Management Studio Express
  • Navigate to the database, refresh the console and verify the table was created/imported
Posted on Tuesday, August 15, 2006 6:07 PM SQL Server | Back to top


Comments on this post: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks exactely what I needed
Left by Frank on May 17, 2007 8:03 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Your post worked for me, thank you !!
Left by MARSHALL on Jul 02, 2007 12:35 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Perfect! Thanks so much!
Left by Carrie on Aug 16, 2007 3:31 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks so much - this was what I've been looking for and saved me HOURS of time. You're the best!
Left by Stella on Oct 08, 2007 1:48 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thank you so much. You are a genius and my savior!
Left by Sowdha C on Jan 12, 2008 2:53 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Many thanks - you helped me a lot !!!
Left by Markus on Jan 27, 2008 2:56 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks so much.....
Left by Fahad on Feb 26, 2008 10:39 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks, great!!
Left by Robert on Mar 26, 2008 9:25 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Cheers mate, saved me loads of time!
Left by Shay on Mar 28, 2008 5:20 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
so nice, thank you :)
Left by sien on Apr 09, 2008 11:34 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Lovely! - also works converting from Access 2007. Thanks.
Left by jw on Apr 17, 2008 10:45 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
What if the table is already created in SQL Server? What if you have data already in this table? Does access remove the esiting data? One of the columns is a counter, identity field. Does this need to be disabled?
Left by Paul Byrum on Apr 18, 2008 6:23 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Its fine, But what I need to do the same pogrammetically.
Left by Yash on Apr 28, 2008 3:31 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Tony! Thanks for posting this! As SQL Server help leaves much to be desired, your post saves the day! Clear and straight forward - follow the steps - worked for my Access 2007 tables just fine!
Left by Frank C on May 30, 2008 1:48 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thank you so much!!! It also works converting from Access 97.
Left by Heidi on Jun 24, 2008 7:05 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
hi there! would just like to ask how am i gonna delete all data in an sql server table using ms access. is this possible? help please. thanks!
Left by benz on Jul 06, 2008 10:43 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thank you so much!!!
Left by vijay on Aug 20, 2008 11:52 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thankx ..>This worked gud
Left by Manojkumar on Aug 28, 2008 6:30 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Excellent! This is exactly what I was looking for, well looking for Excel, but after I imported it into Access everything worked like a charm, thank you very much
Left by Patrick on Aug 28, 2008 1:49 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
SWEET!!! THANK YOU SO MUCH FOR THIS POST...
Left by Brandon on Sep 23, 2008 1:14 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
WOW, this worked SOOOOO well!! When I found NO dts or import facility in express, I had done another load by building individual SQL insert statements (using global changes) in my text editor... this was SUCH a relief from that PITA. I used MS Access XP (v2002) and SQL Server Management Studio Express (2005) and it worked just fine...
HOWEVER, I did not see any SQL server instance to connect to in the dropdown, in the step:

Select the SQL Server to connect to and Press “Next>”

I pasted in my complete local instance name (right click in SQL Express and get properties... "LocalServerName\sqlexpress") and it had no trouble connecting and validating the connection.
Left by Jack on Nov 09, 2008 2:26 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Please can you help me to import my acess data into SQL management studio, I followed the process above but I am stock at not know what exactly is meant by data source. please can anyone tell me what to do?

Thanks

Chris
Left by Chris Vandan on Nov 11, 2008 8:13 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks. But when there is a datetime cell on my Access table, an error "Error converting data type varchar to datetime (#8114)" will popup and the convertion will fail.

Do you know how I can solve this problem?

Thanks
Left by Alan Tai on Jan 08, 2009 12:26 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
i think this is just great i love the open source world
lets continue to share knowlege freely and the digital world would be better for everybody


yours FVT(Harare Zimbabwe)
Left by FVT(Harare Zimbabwe) on Mar 05, 2009 1:19 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Hi thx a lot :) helped us.
Left by Expoting tables to sql server on Mar 10, 2009 6:17 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Yeah Babee!

Help on this subject is somewhat sparse and since SQL Express is NOT intended to be a full-featured FREE db management tool, any procedures that facilitate dts is welcomed.

Thank you for the effort you made to work this out and thanx for sharing!

Page has been duly bookmarked.

Fred
Left by FRED on May 11, 2009 10:25 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks a lot Mate !! Thats exactly what i was looking for !
Left by Kuldeep on Sep 01, 2009 7:52 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks a Ton! It was great
Left by Devender Singh on Oct 12, 2009 2:11 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks for sharing this How-To information, it was quite helpful

Michael
:-)
Left by Michael on Nov 14, 2009 4:19 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Perfect. This is exactly what i need. Thank you very much. Gob bless you.
Left by Amadeus on Dec 12, 2009 11:13 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
It worked! Very good post! Save me hours of research!! Thank you!!
Left by larruda on Feb 08, 2010 10:11 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
i want to ask how i import all database from acsess to sql server and not table by table
tanks alot for your help
rachel
Left by rachel on Feb 27, 2010 5:47 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Try the upsizing wizard.
Left by tony on Feb 27, 2010 6:08 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks a lot,
Left by Nikson Kanti Paul on May 18, 2010 8:54 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Fabulous article. Worked exactly. Thanks a lot.
Left by sudhakar on Aug 25, 2010 11:32 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Hi,
I have 100 table... --> die
Left by Cong N. Le on Sep 06, 2010 3:43 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
sir then what about the relation ships
Left by Zahid Hussain Indher on Sep 17, 2010 5:27 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Great brother!
keep it up.
Left by zain ali on Oct 18, 2010 2:52 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks a lot
Left by Ajay on Nov 17, 2010 5:40 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
thank you so much... this is what need
Left by Gaurav Kumar on Dec 15, 2010 1:26 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thanks so much
Left by DuongBui on Mar 09, 2011 3:22 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Thank you so much. Success ACCESS2010 to SQLServer 2008
Left by Ferny on Mar 30, 2011 9:49 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
This is an elegant solution. A thousand thanks!
Left by Steve Carpenter on Oct 14, 2011 12:24 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Great post. Here’s how you can convert Microsoft Access to web in minutes http://www.caspio.com/extend/platform-extensions/ms-access-database-online/convert-ms-access-to-web.aspx
Left by Cameron Reid on Jan 24, 2012 2:16 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
It works perfect, but what if i've 100 tables? Is it possible tot convert all the tables in 1 time?
Left by Rachid on May 14, 2012 3:43 AM

# Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
please urjent
Left by sateesh on Jun 20, 2012 5:13 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
Will this server allow my company to access/update our MS Access database from any location?
Left by Kenneth Mitcham on Jul 14, 2012 7:07 PM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
the orignal post is what i needed. many many thanks after a lot of year
Left by nicolas on Oct 24, 2012 5:07 AM

# re: Import Microsoft Access (v2003) tables into SQL Server Express Edition (v2005)
Requesting Gravatar...
This worked well.

Thank you. I am very satisfied.
Left by Jorma Hämäläinen on Nov 25, 2013 3:25 AM

Your comment:
 (will show your gravatar)


Copyright © Tony Wissler | Powered by: GeeksWithBlogs.net