locked
Issue in Migration from Access 2007 to SQL Server 2012 RRS feed

  • Question

  • Hi, 

    The "Microsoft SQL Server Migration Assitant v7.8 for Access" for Access 2007 was downloaded last week. 
    Today, I tried this program for the first time.   I ran into problems in Step 4 of 7 

    1. Open the wizard.
    2. Name the project.
    3. Add my Access database.
    4. Check the top level node to ensure that all objects in the Access database are selected.
    5. In the connection dialog, supply my server name and a name for the SQL Database.
    6. Since this is a new database, confirm that I want to create the database.
    7. After a time I get a report of Errors, Warnings and Info Messages documenting what happened.

    Access-metadata 
        Databases (1/1) 
             My Reports 
                   Queries (0/0) 
                   Tables (0/0) 
                   
    My Access.mdb has 60 tables in it and I am not getting any tables when I checked the Check Box 
    next next to Table.  In fact all the boxes were checked in all levels, but did not show any tables. 

    Do you know what happen to my tables not showing up when I click the + sign to expand for viewing ?

    Please help and advise.

    Thanks, 

    Patsy Farkas

    Monday, August 6, 2018 3:08 PM

All replies

  • Hi Patsy Farkas,

     

    From your description, my understanding is that your tables didn't display in Access Metadata Explorer in SSMA. If anything is misunderstood, please tell me.

     

    Could you please provide us more information about the error in SSMA log. You can look at the SSMA log via clicking View->Show Log in the menu like the following screenshot.

    Besides, if you don't use the right provider, the data can't be previewed. So, please make sure that you are using the connection named: Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider. By the way, migrating data from Access to SQL Server via using SSIS is also a suggested approach. You can refer to the article. https://www.mssqltips.com/sqlservertip/2656/simple-step-by-step-process-to-import-ms-access-data-into-sql-server-using-ssis/

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    Tuesday, August 7, 2018 7:24 AM
  • In SQL Server Migration Assistant for Access -  
    In the tab View, the log is showing 
    -----------------
    [Mandatory: Generic] [8476/1] [2018-08-08 10:33:16]: SQL Server Migration Assistant for Access v7.8.0
    [Mandatory: Generic] [8476/1] [2018-08-08 10:33:16]: Starting assembly: SSMAforAccess, Version=7.8.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35
    Created: 4/6/2018 7:32:36 PM
    Last Modified: 4/6/2018 7:32:36 PM
    OS Version: Microsoft Windows NT 6.2.9200.0
    OS architecture: x64
    Processor architecture: AMD64
    Current culture: en-US
    UI culture: en-US
    -----------------


    I the tab for Tools, the following information is what is showing. 
    TOOLS ->  DEFAULT PROJECT SETTINGS

    Conversion 
       Identifier 
    Warn when identifier name is changed   Yes
    Warn when identifier will be quoted    Yes
       Misc 
    Include a data assessment report       Yes
    Warn when a complex query uses a wildcard(*)   Yes
       Tables 
    Add primary key                         No 
    Add timestamp columns               Let SSMA decide 
    Message type when a promary key includes nulluable columns    Warning 
    Message type when foreign key columns are of different sizes  Warning 
    Message type when memo columns are indexes                    Warning 

    Migration 
       Bulk Copy 
    Check constrainst                    No
    Fire Triggers                        No
    Keep identity                        Yes
    Keep nulls                           Yes
    Table locks                          Yes
       Dates Correction 
    Replace unsupported dates            Do nothing 
       Misc
    Batch size                           10000


    Linked Table 
       Misc 
    Don't load linked tables               No 
    Store user credentials                 No

    -----------------------------------

    Please help and advise. 

    Thanks, 

    Patsy Farkas

    Wednesday, August 8, 2018 3:41 PM
  • Hi Patsy Farkas,

     

    The SSMA v7.8 requires Microsoft Data Access Object (DAO) provider version 12.0 or 14.0. Could you please check the DAO version of your Access. If not meeting the requirement, you can download from here and install it.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 10, 2018 9:44 AM
  • Emily, 

    I installed AccessRuntime.exe and I am still getting zero count on the Table when it was CHECKED - 

    Access-metadata 
        Databases (1/1) 
             My Reports 
                   Queries (0/0) 
                   Tables (0/0)

    What should I do to make the 60 tables appear?

    In addition to the above, some of the tables has data with datatype defined as MEMO in Access 2007, and is causing a problem when migrate to SQL Server 2012 even though the destination table is defined as nvarchar(max) to avoid truncation during conversion. 

    Please help and advice. 

    Patsy Farkas

    Friday, August 10, 2018 2:02 PM
  • Hi Patsy Farkas,

     

    We are currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com



    Tuesday, August 14, 2018 1:49 AM
  • Hi Patsy Farkas,

     

    Could you please tell us if you have solved the question. If not, could you please provide us your "My Access.mdb" for analysis via any trusted network share disk. Need to point out that please mask your private information in the file.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 20, 2018 8:30 AM
  • Hi,

    Can you please make sure that you have Access Runtime of the same architecture as SSMA you are trying to use? If you installed 32-bit Access Runtime, then you will need to use 32-bit version of SSMA for Access.

    We have a blog post explaining this in details.

    Regards,
    Alex.

    Wednesday, August 22, 2018 3:30 PM