-1

I'm using MS Access 2013. I have a some tables which I would like to migrate to SQL Server 2012 (Management Studio). I want these tables to be used in SQL Server for addition etc., but I also want these tables to show as 'linked' in MS Access.

How do I make it so the tables look populated in MS Access, but are manipulated from SQL Server?

The Migration tool in SQL Server did not give me my desired results.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TripWire
  • 71
  • 1
  • 10
  • Well, "not desired results" is un-clear. Did the tables move to SQL server and did you check/view SQL server with the SQL management studio? After you transfer tables to SQL server, then you can and should be able to simply link to those tables using the external data tab of the ribbon and choose ODBC. – Albert D. Kallal Nov 02 '16 at 17:45
  • The tables were located there, but there was no indication of there being a link in Access. Having observed another person's project, I noticed the indicator over the tables showing the link. – TripWire Nov 02 '16 at 19:03

2 Answers2

1

I'm not sure why you want to move it to SQL server from Access. If you say it is just for addition purposes, that is a really bad idea to move from Access.

You will need to create a shell database in SQL Server. From Access select > External Data > More > ODBC Database and follow the prompts to export the data into SQL Server.

After that select > External Data under Import and Link > ODBC Database This will link the local Access table to the SQL Server table and you will be able to run queries you require.

bwilliamson
  • 391
  • 1
  • 13
  • I'm doing this on behalf of someone else. They say that Access has "too many restrictions". I received an error during the move - it looked as if it would be successful for a bit. ODBC--call failed. [Microsoft][SQL Server Native Client 11.0][SQL Server]Full-Text Search is not installed, or a full-text component cannot be loaded. (#7609) – TripWire Nov 02 '16 at 18:41
  • Try SQL Server Configuration Manger-> SQL Server(./SQL Express) (properties) Set the "Enable file Stream for Transact-SQL Access" – bwilliamson Nov 02 '16 at 19:56
0
  1. Create Database in SQL Server https://msdn.microsoft.com/en-us/library/ms186312.aspx#SSMSProcedure
  2. Migrate Data from Access to SQL Server. For Access 2013 you will need to use the SQL Server Import and Export Wizard. Search the start menu for "Import and Export Wizard". It comes installed with SQL Server. For the data source choose Microsoft Access and follow the steps.
  3. How do I make it so the tables look populated in MS Access, but are manipulated from SQL Server? Create a linked table via ODBC connection. https://support.office.com/en-us/article/Import-or-link-to-SQL-Server-data-a5a3b4eb-57b9-45a0-b732-77bc6089b84e
SQLSuperHero
  • 538
  • 7
  • 17