0

I am working on Windows Forms Application with SQL Server Database. Now I have to convert the SQL Database to MS Access database as per the requirement. Can any one help me out how to convert SQL database to MS access database so that, I can access the same tables.

Thanks & Regards, Vani.

Improve this question
2
  • 1
    Are you sure you want to do this? You might want to link the tables instead? See office.microsoft.com/en-us/access-help/…. And, whilst it is the season to be jolly, something about going FROM SQL Server TO Access makes me wince, too ;-) See also stackoverflow.com/questions/2500519/… Commented Dec 27, 2011 at 12:09
  • Why? it sounds like a step back. The best you can do is generate create scripts for the tables and run them one-by-one in the access database but you have to take-out sql server specific stuff and replace it with the Access equivalent Commented Dec 27, 2011 at 12:10

2 Answers 2

Reset to default
1

You can use MS SQL management studio to export tables data and structure to access. To do this right click on you db in Management studio and select Tasks -> Export data. Now follow the steps of the wizard. First you'll select the source - it would be you db, next destination - it would be an access file. And there you go :) Have fun!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

As Elastep says, you can export data from MS SQL Management Studio - but that will only get you to .MDB-files; ie. prior to Access 2007. The steps below works both for 2007 and prior (although the actual path to execute them may vary in the different versions):

In Access you can import the tables from SQL Server - when in your target database in Access, choose External Data --> More --> ODBC Database. In there you can set up a connection to the SQL Server and choose between Import the source data and Link to the datasource. After selecting either, you can choose the tables you wish to import/link.

If you import, the data will be copied physically to the Access-file. It sounds like this is what you want to achieve.

However, as comments above suggest, this sounds off. Are you sure you want to move your data to a weaker engine? If you do want to keep the data in the SQL Server, whilst using Access only for forms, etc. you can link the tables instead. This way, you can use the rapid-prototyping, datacentric tools in Access, while keeping the security, consistency and scalability of the SQL Server.

For reference, you might want to take a look at this: http://office.microsoft.com/en-us/access-help/import-or-link-to-sql-server-data-HA010200494.aspx

Improve this answer

3 Comments

Hi Anders, I have gone through the Link you have suggested to refer. When I am following the steps suggested in the link I have encountered to enter DNS Name(.dsn) file. Could you please explain me what should i specify there and what it means. Thanks & Regards, Vani.
In the section for importing SQL Data, there is a small link between points 4 & 5 on how to create a new .dsn-file. This applies regardless of whether you import or link. Have you looked at this?
No Sorry.I have missed out the Link. I have referred it. Its working. Regards,

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.