Need advice on how to import/ export sql databases into access

[Course]

I have a database called <code>My Travel Project</code> in Sql Server Express 2014 which I am trying to export to an Access database called <code>Travel Project Imported</code>.


I right clicked on the My Travel Project database in SQL Server and chose export.


When the wizard opened I chose <code>Microsoft OLE DB Provider for SQL Server</code> as a data source but I am not sure if this is the correct option to choose and chose Windows authentication.


I was then asked to choose a destination. I tried <code>Microsoft Access(Microsoft Jet Database Engine)</code> and put in Travel Project Imported as the file name of the Access database.


When I clicked next I got an error message saying the operation could not be completed - unrecognised database format.

My access file has a .accdb extension and I think the Jet engine only recognise .mdbDoes anyone know how to do this correctly or if it ispossible to import Sql Databases into Access.

Thank you

 

[alansidman]

Why not just link your SQL tables to Access then you can use them as if they were tables within Access.

 

[Course]

I will do that later on but first I want to learn how to use Access itself. Im doing an Access course and need some data to work with so I thought id use my Sql tables.

I found out what was wrong with my Export method above. The Access Database that I was using had a 2007-2015 file extension .accdb but the Jet engine only recognises the 2002-2003 ext .mdb

When you link Sql tables to Access does Access serve as the GUI for your Sql database. If it does how does it compare to a GUI that would be built from scratch for the Sql database.

Thanks for your reply

 

[xenou]

When you link Sql tables to Access does Access serve as the GUI for your Sql database. If it does how does it compare to a GUI that would be built from scratch for the Sql database.

Correct. Access serves as the GUI for the sql database. In general Access has many features and works well as a GUI. Can't say how it would compare to building one from scratch, but SQL server is a popular database so there is a lot of support in software packages for creating apps that use SQL server as a backend.