Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79

    Migrating tables to SQL Server

    Hi,

    I have migrated my tables in Access Back End to SQL Server. I have created links between Access Front End and SQL server. The connection works but I have some questions that confuses me:
    - I have read that I need to set my primary keys on all tables in SQL server also. Is that correct?
    - Where do I set my relations? My relations still exist in Access FE. But do I need to set them in SQL Server also?
    - Do I need my Access Back End any more or can I delete that?


    - I have added a new column in one table in SQL Server. However I can't see that column in the linked table in Access Front End. How do I refresh the structure?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes make the tables the same, Same keys.
    Make the relations on SQL side, they should come over.
    keep the table names the same.
    hold onto the old access, you may needed it.
    if a column is missing ,refresh the link,the col Should show.

  3. #3
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Thanks for your reply,

    - I didn't get any relations copied to SQL Server. I exported one table at a time. I don't know any other way to do it. Is there another way to export to get all tables and relations at the same time? I followed this instruction https://www.accessforums.net/showthread.php?t=53456
    - How do I refresh the link? I have searching for a while. The only way I found was to delete the old linked table and create a new link. Feels there should be a better way.

    The SQL connection is quite new to me.

  4. #4
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Hi,

    Following code helped me to update links to all tables:

    Code:
    Sub RefreshLinksToAllSQLTables()
        Dim tbl As TableDef
        
        On Error Resume Next
        For Each tbl In CurrentDb.TableDefs
            tbl.RefreshLink
        Next tbl
        On Error GoTo 0
    End Sub
    Anyone that can help me to export all tables and relations to a SQL Server? I have only been able to export each table at a time and no relations.

    I found a tool in Microsoft SQL Server Managemant Studio that should import all Access tables and relations. However, this tool required a .mdb-format. I have a .accdb-format. I couln't save my datanase as a .mdb-format either.

  5. #5
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    I would recommend that you look into using the SQL Server Migration Assistant (SSMA) tool from Microsoft.

    I would use the SSMA to create another new database. If you don't want to use this new database, at least you can get a list of the issues you will need to fix.

    You could use what you learn form SSMA to make changes to your Access back end then migrate it again. Repeating until there are no more issues during migration.


    See:
    SQL Server Migration Assistant for Access (AccessToSQL)

    and

    https://blogs.msdn.microsoft.com/ssma/

  6. #6
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Thanks for your reply,

    I have trouble reading an .accdb-file. It seems that I can select a .mdb-file which I believe is a fileformat of older Access. I am using Access 2016.

    Here is what happens:

    Selecting Data Source and then Browse...

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	24.7 KB 
ID:	28178

    .mdb is the standard file extension:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	47.0 KB 
ID:	28179

    I select file extension All Files and then my .accdb to migrate:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	55.8 KB 
ID:	28180

    I return to my source form:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	22.9 KB 
ID:	28183

    Click "Next >" in form above I get:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	32 
Size:	10.9 KB 
ID:	28184

    Any ideas what to do?

    Regards

    \Joakim

  7. #7
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    That usually indicates that you installed the SSMA on a machine that does not have the ACE drivers installed.

    Where did you install the SSMA? Did you install it on your desktop (dev PC) that has Access 2016 installed? I find this to be the best place to install it.

    What version of SSMA did you install?

    If you did install SSMA on a machine hat does not have Access 2016 install , like a server, then is does not have the ACE database drives installed that can read the .accdb file. You will need to install the ACE distribution package from Microsoft.

  8. #8
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Thanks for your reply,

    I was using the installed migration tool integrated in SSMS.

    I have now installed SSMA v7.3. However, I get following message when I try to connect to my Access database. It seems like a 32/64-bit issue.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	27 
Size:	51.2 KB 
ID:	28233

    It seems from message above that I can change between 32/64-bit in Programs Menu. I have ben looking in main and sub menus in SSMA but I can't find any Programs Menu. I have looked through all settings on Tools menu but I can't find the 32/64-bit setting.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	27 
Size:	22.9 KB 
ID:	28234

    Any ideas?

    Regards
    \Joakim

  9. #9
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    The SSMS issue is probably caused by the missing ACE drives as previous posted.


    Where did you install the SSMA? Did you install it on your desktop (dev PC) that has Access 2016 installed? I find this to be the best place to install it.

  10. #10
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Hi,

    I have everything (Office 2016 including Access, SSMS, SSMA v7.3) stored locally on my computer. I can run them all without connected to any network.

    I know I can read Access databases (.accdb-files) into Excel without any issue. I believe the installed driver beneath is used by default (MS Acess Database). Shouldn't this drive be enough to connect to Access from either SSMS or SSMA?

    Click image for larger version. 

Name:	Capture.PNG 
Views:	25 
Size:	20.4 KB 
ID:	28238

  11. #11
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Having everything local for learning helps. Once I start development I g move the SQL server database to a separate machine. This allows me to devlope and test with the speed over a network.

    I would expect Access and Excel to work together on the same machine. They would both have the same bits (32 or 64).

    The next thing is get the bits matching between the the ACE drives and what SSMS or SSMA are using.

    You would think that Access 2013/2016 installed would be enough for SSMS and SSMA. Unfortunately it is not. The fix that has worked for me and other is to install this: Microsoft Access Database Engine 2010 Redistributable

  12. #12
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Thanks,

    Now I can use the SSMS tool --> Tasks --> Import Data ... with your fix above.

    Still SSMA doesn't work. I believe it's enough to use SSMS.

    I see that get following correct:
    - Columns
    - Data Type
    - Allow Nulls

    But Primary Key, Unique, Indexes ... are lost? Is it possible to get them also somehow?

    Also Table Relations are lost. Possible to get them migrated also? I believe I shall have them defined in SQL Server and not in Access.

    Appreciate your help
    \Joakim

  13. #13
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Joakim,

    We are getting closer.

    Quote Originally Posted by Joakim N View Post


    But Primary Key, Unique, Indexes ... are lost? Is it possible to get them also somehow?

    Also Table Relations are lost. Possible to get them migrated also? I believe I shall have them defined in SQL Server and not in Access.
    Yes. Us SSMA.

    Next is to figure out why you still can;t get SSMA to work ed to get everything to migrate.


    What is the error message u get now with SSMA?


    About defining relationships:

    You only need to define relationships at the database level (Access back end or SQL Servers) if you want the Database Engine to enforce referential integrity. Otherwise you have to write your own code to handle it.

  14. #14
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Hi,

    Using SSMA gives following error message (Same as before I installed "Microsoft Access Database Engine 2010 Redistributable") when trying to connect to my Access database:

    "Retrieving the COM class factory for component with CLSID {CD7791B9-43FD-42C5-AE42-8DD2811F0419} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)). This error may be a result of running SSMA as 64-bit application while having only 32-bit connectivity components installed or vice versa. You can run 32-bit SSMA application if you have 32-bit connectivity components or 64-bit SSMA application if you have 64-bit connectivity components, shortcut to both 32-bit and 64-bit SSMA can be found under the Programs menu. You can also consider updating your connectivity components from https://go.microsoft.com/fwlink/?linkid=834402."

    I have 32-bit on my computer.

    \Joakim

  15. #15
    Joakim N is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    79
    Hi,

    Finally I got connection with Access Db from SSMA. Previously I was running the SSMA shortcut created directly on my desktop at installation. This seems to be a 64-bit version, I assume. However, there was a 32-bit version on the Start menu. I didn't look there earlier.

    Click image for larger version. 

Name:	2017-04-14 08-22-52.png 
Views:	19 
Size:	48.1 KB 
ID:	28266

    Now I will try to migrate. Maybe I have some further questions later.

    Thanks again for all your help.

    \Joakim

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error when migrating Access database to SQL Server
    By AnneForumer in forum Import/Export Data
    Replies: 1
    Last Post: 11-18-2016, 10:24 PM
  2. Migrating Access to SQL Server using an ODBC
    By cbende2 in forum Tutorials
    Replies: 2
    Last Post: 07-06-2015, 06:44 AM
  3. Query not working after migrating to SQL Server 2014
    By Esmatullaharifi in forum Queries
    Replies: 7
    Last Post: 04-18-2015, 05:21 AM
  4. Replies: 2
    Last Post: 03-24-2012, 01:29 AM
  5. Replies: 0
    Last Post: 12-01-2011, 10:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums