locked
Post migration from SSMA for Access - conversion issues RRS feed

  • Question

  • Hi SQL Migration Forum

    Because this is the first time converting, I would like to ask the best way to continue.

    Using SSMA for Access 7.8 conversion process, to remove as many errors during conversion ,

    1) I have firstly removed all the attachment types in the front end before re-converting again.

    https://blogs.msdn.microsoft.com/ssma/2011/05/27/access-to-sql-server-migration-understanding-the-assessment-report-and-error-list/

    I will later add them as strings to link up the images as per the following

    http://www.databasedev.co.uk/image-form.html

    2) I have no hyperlink formats, as a previous MVP told me how to create a link with on click, to avoid this format

    3) I have a couple of  ComplexLong

    "The ComplexLong data type is the result of using a lookup query and choosing the multi-selection option for the Long data type. Underneath the covers in Access, there is a junction table that records the results of the multiple selection. In SQL Server, you can emulate the same behavior with a junction table based on the query and then reference the junction table in your Access solution."

    Do you have an example of how to resolve with a junction table in SQL Management Studio, or dealing with this first in the access database.

    4) I would like to get an opinion of how best to deal with the date and time conversion issues.

    I have read the following articles.

    http://www.utteraccess.com/forum/lofiversion/index.php/t1972232.html

    https://stackoverflow.com/questions/20199680/display-sql-time-field-on-access-form-as-access-medium-time-hhmm-am-pm

    In situation I have a form with datasheet.

    The pre-conversion was displaying 10-May-2018 (or when you click on , it shows 10/05/2018  3:06:44 PM)

    but in the SQL SEVER it now has 2018-05-10 15:06:44

    Where would be the best place to display this datasheet column to 10-May-2018?  And how?

    Or with StartTime column pre-conversion 10:12:15 AM (or when you click on the cell, it shows 30/06/2018  10:12:15 AM)

    but in the SQL Server field now it has 2018-05-19 14:23:05

    The same effect is occouring in the drop down boxes in the form that is reading the same table as the datasheet.

    Hence where and how would be the best approach to address this.

    This is why also the VBA code is now debugging after conversion to SQL.

    Eventually, I would like to move this to Azure SQL Database, but I am first working on the local SQL Copy to get that working with the Access Front End.

    Thank you kindly

    Simon


    Thank you in Advance

    Thursday, July 19, 2018 7:38 AM

Answers

  • Datetime2 columns work without issue with Access.

    However, you WILL have to link your tables using the SQL Native 11 (or later drivers). And you thus will have to install these drivers on each workstation.

    If you link your tables using the SQL Driver (this is a older driver installed on all versions of windows by default). If you use the older ODBC driver, then datetime2 columns will be seen by Access as text columns.

    So your choice is:

    Always migrate using the older datetime format. You can change this setting in SSMA.

    After you migrate simply use sql design tools (SSMS) and change the column type to datetime. If  you just have say a few tables, then this is not much work.

    If you have a lot of tables, then I would consider adopting the native 11 (or later) drivers - that way you don't have to change anything server side, and access will see + work with those types of columns correctly.

    The newer drivers are better, and supposedly faster - but I not experienced performance increases.

    So Access has no problem with datetime2, but you have to re-link your tables using a Native 11 or later driver for Access to see these columns correctly. About the only downside of this option is the noted require to ensure that these drivers are installed on any workstation you plan to run your application on .

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

     

    Well, native 11, 12 13 will be just fine.

    I think the latest is 13, and you can find it here:

    https://www.microsoft.com/en-us/download/details.aspx?id=50420

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Well, native 11, 12 13 will be just fine.

    I think the latest is 13, and you can find it here:

    https://www.microsoft.com/en-us/download/details.aspx?id=50420


    Thank you in Advance

    Saturday, July 21, 2018 6:28 AM

All replies

  • Perhaps someone could explain how to use the tools.

    The first screen capture is SSMA for Access.  The second is the SQL Server Management Studio (Express Edition).

    They look the same for the metadata views in the SSMA for Access Project View.

    But in the SQL Server Management Studio, the data format looks different.  

    The third view is the Access front end.  And the conversion field of time agrees with the sql server.


    Thank you in Advance

    Thursday, July 19, 2018 8:02 AM
  • Hi Crosstab Query Access,

    Could you please tell us which data type did you select for the "Opened data" column?

    SQL Server provides many display format for data, you can use the select convert(varchar, [Opened data], 13) to get the format 30 Dec 2006 00:38:54:840AM.

    For more information, please refer to this blog: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

    Here is the document discussing about the datatime datatype: https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-2017

    Besides, I would suggest you using the SSIS to migrate data from Access to SQL Server in your scenario, many thing cannot be changed in SSMA for Access

    Best Regards,

    Teige


    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.


    • Edited by Teige Gao Thursday, July 19, 2018 10:21 AM
    Thursday, July 19, 2018 10:07 AM
  • Hi Tiege, 

    Thank you kindly for your analysis.

    I have not used SSIS, and I believe SQL Express Edition will not have it.  I am not sure where to download it if so.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5a6bbe65-1465-4b9e-bc09-96f4cddb2daf/free-download-for-ssis-software?forum=sqlintegrationservices

    The datatype was automatically converted to [Opened Date] datetime2(0) 

    I have read a bit and will further read on it

    https://stackoverflow.com/questions/6709670/how-do-i-import-ms-access-data-into-sql-server-using-ssis-package

    https://docs.microsoft.com/en-us/sql/sql-server/install/security-considerations-for-a-sql-server-installation?view=sql-server-2017

    https://docs.microsoft.com/en-us/sql/integration-services/sql-server-integration-services?view=sql-server-2017

    Please note when i used SSMS for Access I just followed the prompts and used the youtube mentioned above as guidance.

    I have tried converting once again, and The questions that arise from me include:

    Where can we set the dataconversion in SSMA for Access if possible before conversion?  Or after conversion?

    Or can we use SSMS Studio to set this conversion. to get the dates and times as per original in the Access front end?  and database files.

    Please excuse my excessive questions, I am learning here as I go for the first time.

    Thank you.

    Simon


    Thank you in Advance

    Thursday, July 19, 2018 1:06 PM
  • hank you most kindly Tiege

    As I am reading all the responses, and follow up documentation, I was also given some tips in the second forum:

    https://social.msdn.microsoft.com/Forums/en-US/7d70eb6b-d811-4e35-af89-93142617a966/postmigrationfromssmaforaccessconversionissues-to-sql-database-will-next-continue-to-azure?forum=ssdsgetstarted&prof=required

    I presume we can either do this with a tool such as SSIS during conversion,  (however I had some questions in the post if we were to use this tool instead of ssma for access)

    or as also indicated convert each object as each field in the converted table from SSMS for Access.

    As I am now understanding that the field itself remains as the converted Datatype - datetime2(0), 

    but then you place Transact-SQL on top of this as an extract query, as an interface to the access front end, 

    so I'll work now on how to do this?  Unless my wording is not quite correct.  Thank you kindly.

    Thank you.

    Simon


    Thank you in Advance

    Thursday, July 19, 2018 11:54 PM
  • Dear Crosstab Query Access,

    Firstly, could you please tell me if you want to the One-time migration from the Access or require migrate the Access data every time when it has new data?

    If it is a one-time migration, you can use the SSMA to migrate all the data, although the datatime display type cannot be changed in SSMA, but we can change it with convert function and then inert the data into a new table, then there is no need to "but then you place Transact-SQL on top of this as an extract query".

    For example, we can use the following code:

    select convert(varchar, [Opened data], 113),[other columns] into dbo.Calls_new from dbo.Calls.

    Then all the data include the changed time type will be inserted into the table dbo.Calls_new, after that, we can change the name of original dbo.Calls_old and rename the dbo.Calls_new to dbo.Calls.

    But if it is not a one-time migration, I would suggest you using the SSIS, it is more convenient and it is designed professionally to do this job. SSMA is also also working based on SSIS package.

    Best Regards,

    Teige


    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, July 20, 2018 2:32 AM
  • Hi Thank you Tiege, 

    In fact the data is just test data, as we are developing the program anew.

    Hence therefore I would classify in the category you first indicate, as if the data is only migrated once.

    If I empty the database, 

    and execute for each field i have indicated above, would the 

    Would i just run for example:

    select convert(varchar, [Fieldname x], 13) to each field, 

    Thank you again.

    Simon



    Thank you in Advance

    Friday, July 20, 2018 7:10 AM
  • Hi Tiege

    Thank you!  I didn't need to ask the last question....

    /****** Script for SelectTopNRows command from SSMS  ******/
    SELECT [ID]
          ,(Replace(Left(CONVERT(varchar, [Opened Date], 113),11),' ','-') + ' ' + Format([Opened Date] ,'h:mm tt')) AS 'Opened Date'
          ,[Call Back]
          ,[Job Stage]
          ,[Job Number]
          ,[Job Order]
          ,[Job Time]
          ,[Job Finish]
          ,[Estimated Time of Job]
          ,[Title]
          ,[Cost]
          ,[Cost Final]
          ,[Deposit]
          ,[Payment Outstanding]
          ,[Rates]
          ,[Payment Method]
          ,[Search Method]
          ,[Discount]
          ,[Assigned To]
          ,[Opened By]
          ,[Resolved By]
          ,(Replace(Left(CONVERT(varchar, [Resolved Date], 113),11),' ','-') + ' ' +  Format([Resolved Date] ,'h:mm tt')) AS 'Resolved Date'
          ,(CONVERT(varchar, [Start Time], 103) + ' ' + Format([Start Time] ,'h:mm:ss tt')) AS 'Start Time'
          ,(CONVERT(varchar, [Resolved Time], 103) + ' ' + Format([Resolved Time] ,'h:mm:ss tt')) AS 'Resolved Time'
          ,[Total Time]
          ,[Rate Method]
          ,[Labour Cost]
          ,[Material Cost]
          ,[Discount Amount]
          ,[Total Cost excl]
          ,[Total Cost]
          ,[Standard Variable Rate]
          ,[Caller]
          ,[Call Before]
          ,[Agent]
          ,(CONVERT(varchar, [Call Time], 103) + ' ' + Format([Call Time] ,'h:mm:ss tt')) AS 'Call Time'
          ,[Related Calls]
          ,[Status]
          ,[Category]
          ,[Priority]
          ,[Dispatch Details]
          ,[Description]
          ,(Replace(Left(CONVERT(varchar, [Due Date], 113),11),' ','-') + ' ' +  Format([Due Date] ,'h:mm tt')) As 'Due Date'
          ,[Comments]
          ,[Payment Complete]
          ,[SSMA_TimeStamp]

          INSERT INTO dbo.Calls_new

      FROM [ABA PLUMBING Call Tracker V4].[dbo].[Calls]

    I will then rename.... thank you again.

    Simon


    Thank you in Advance

    Friday, July 20, 2018 7:23 AM
  • Hi Tiege, 

    Thanks for the pointers.

    I have changed all the tables with date and time, 

    and after i tested, and in access they no longer allow the time selection pop up calendar.

    I tried reverting to the converted tables which I kept as _old

    And the same thing happens.

    Hence now I will investigate what is the proper process now to connect the drop down combo boxes, and the settings in the text boxes in access to view the data correctly, and have them to be actual dates/times that access reads properly.

    If you have any experience in what is required next.

    Thank you kindly.

    Simon


    Thank you in Advance

    Friday, July 20, 2018 9:07 AM
  • https://community.toadworld.com/platforms/sql-server/b/weblog/archive/2013/03/23/ms-access-db-to-sql-server-migration-post-migration-issues

    Thank you in Advance

    Friday, July 20, 2018 9:19 AM
  • Knowledge Sharing.

    That article above put into action


    Thank you in Advance

    Friday, July 20, 2018 10:00 AM
  • https://loopfoundry.com.au/2017/03/lessons-learned-migrating-access-to-sql-server/

    From this site:

    1. If set up correctly, the SQL Server Migration Assistant for Access (SSMA) is your best friend and will save you hours. If set up incorrectly, it’s your worst nightmare. In particular, we had a lot of trouble migrating straight from Access 2013 to SQL Server 2016 (as the software is built for SQL Server 2014). Our workaround was to migrate to an Azure SQL database (which is supported) and then move the database to our local server. Note that there are some data security concerns doing this compared to a straight local migration (as your data briefly goes off-site), but in our situation it worked really well.
    2. SSMA also defaulted to using DateTime2 as the SQL Server data type to replace Access’ Date/Time type. Unfortunately, Access doesn’t recognise DateTime2 and treats it as a text field instead, meaning you have to rewrite a lot of queries and code if you stick with that. However, there is the option to use DateTime in the migration, which Access does recognise. DateTime2 does have some added accuracy compared to DateTime, but DateTime should be sufficient in 95% of cases.
    3. After migrating, our second new best friend was the SQL Server Profiler, which allowed us to see what Access was requesting from the server. What was it requesting? e-v-e-r-y-t-h-i-n-g. There were some badly written queries in the Access front-end where the query ended up pulling every row from every related table, one row at a time. Very slow even run on the server. Over the local network, it took literal hours to complete a reporting run. Which brings us to our next point…
    4. If you have a complex query in your front-end, rewrite it as an SQL Server View. Because Views are executed on the server, and only the result set is passed to the client, it can hugely speed up processing on large datasets. It also means you can write queries in standard T-SQL rather than Access SQL. The one downside to this is that if you have a form in Access that is backed by a query, you’ll need to rewrite parts of the form, as an Access form cannot (easily) be backed by a View.

    One we realised and adopted these techniques, we were able to complete the migration with much less pain and a lot more fun. Bringing 1990’s technologies and business processes kicking and screaming into the 21st century gave us a lot of satisfaction, and laid the groundwork for the ASP.NET website to come.


    Thank you in Advance

    Friday, July 20, 2018 10:53 AM
  • Hi Tiege, 

    If you can assist:

    https://social.msdn.microsoft.com/Forums/en-US/4799fdbd-1b45-4fce-b031-d82d29ecc1d9/how-to-save-project-settings-in-ssma-for-access-7810?forum=sqltools

    The data conversions went fine, but both the old and new tables do not work in the access front end.

    There is a blog that indicates the issue as the datetime2 datatype, and datetime should work in 95% of situations.  If I can set this in the ssma for access , and keep it saved, as per my thread above.

    Thank you.

    Simon


    Thank you in Advance

    Friday, July 20, 2018 11:51 AM
  • Hi Tiege, 

    Just an update, that I did not notice after the INTO new_table... that the datatype changed to ncharvar(4000)

    I also did some testing on datatime and datatime2 and they both do not allow the calendar popup... as far as my first testing revealed.

    Hence I have gone back to the original tables , 

    and find the converted data correct

    from datetime2(0)

    1899-12-30 00:00:00

    1899-12-30 00:30:00

    instead of

    nvarchar(4000)

    12:00 AM

    12:30 AM

    So now I will concentrate on the front end and see what needs to be done.

    Thank you again, I have learnt about a SQL Studio in greater detail for future projects, or maintenance of this database when needed, because it is now the backend.

    Thank you.

    Simon


    Thank you in Advance

    Saturday, July 21, 2018 3:30 AM
  • Hi Tiege

    Some really promising news.

    It was that datetime type.  

    After I posted an article on microsoft forums

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-mso_win10-mso_365hp/access-environment-after-post-migration-by-ssma/d8c8fc36-7bd2-4a53-9af2-1586d7ed7bf4

    And then read Gina Whipp's article, 

    by simply refreshing the links (after I forgot to do this after changing the format to datatime from datatime2)

    The first testing for some of the access front end is working thus far, hence on to the next step.

    Thank you again for your help

    Simon


    Thank you in Advance

    Saturday, July 21, 2018 4:39 AM
  • In fact now thinking about it Tiege

    Your first suggest to make a view, would have been the direction to go, 

    and leaving the table with datetime2 field.

    I would have then had to write some code to be able to update the table under the view next according to that other blog as well, perhaps slowing performance unnecessarily.

    but then I would have come unstuck, because only datetime seems to be allowing the popup calendar.

    that article seems to be spot on with this observation

    https://loopfoundry.com.au/2017/03/lessons-learned-migrating-access-to-sql-server/

    The use of these tools are quite nifty.

    Thank you again.

    Simon


    Thank you in Advance

    Saturday, July 21, 2018 5:04 AM
  • Datetime2 columns work without issue with Access.

    However, you WILL have to link your tables using the SQL Native 11 (or later drivers). And you thus will have to install these drivers on each workstation.

    If you link your tables using the SQL Driver (this is a older driver installed on all versions of windows by default). If you use the older ODBC driver, then datetime2 columns will be seen by Access as text columns.

    So your choice is:

    Always migrate using the older datetime format. You can change this setting in SSMA.

    After you migrate simply use sql design tools (SSMS) and change the column type to datetime. If  you just have say a few tables, then this is not much work.

    If you have a lot of tables, then I would consider adopting the native 11 (or later) drivers - that way you don't have to change anything server side, and access will see + work with those types of columns correctly.

    The newer drivers are better, and supposedly faster - but I not experienced performance increases.

    So Access has no problem with datetime2, but you have to re-link your tables using a Native 11 or later driver for Access to see these columns correctly. About the only downside of this option is the noted require to ensure that these drivers are installed on any workstation you plan to run your application on .

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

     

    Well, native 11, 12 13 will be just fine.

    I think the latest is 13, and you can find it here:

    https://www.microsoft.com/en-us/download/details.aspx?id=50420

    Regards,

    Albert D. Kallal (Access MVP, 2003-2017)

    Edmonton, Alberta Canada

    Well, native 11, 12 13 will be just fine.

    I think the latest is 13, and you can find it here:

    https://www.microsoft.com/en-us/download/details.aspx?id=50420


    Thank you in Advance

    Saturday, July 21, 2018 6:28 AM