Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Transfer data from SQL 2005 to ACCESS 2003 (2003SP2)

    Transferring from SQL 2000 to Access used to be a breeze but with SQL 2005 the process fails upon validating and the constant error message is "Cannot convert between unicode and non-unicode string data types.". I've discovered by experimenting on one table that if I create it first within Access and change all the Memo fields to Text with a 255 character limit, then the export will work. But there are nearly 100 tables so what VBA code is required to loop through all tables and change all Memo fields to Text with a 255 character limit?

    Alternatively, how do I get SQL 2005 to export the data without this infernal drama!??

    Thanks

  2. #2
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer data from SQL 2005 to ACCESS 2003 (2003SP2)

    Are the fields in SQL Server 2005 nvarchar() fields? If so, you can change them to varchar() or you can cast them to varchar() during the export. I believe you can do the casting of data types if you are using SSIS I believe you can performing the casting there.

    Patrick

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Transfer data from SQL 2005 to ACCESS 2003 (2003SP2)

    (Edited by HansV to make URL clickable. It takes only a few seconds and it is much more user-friendly - see <!help=19>Help 19<!/help>)

    Thanks for the reply. I gave up on this and found some reasonably priced software at http://www.sqlmanager.net/en/products/mssql/dataexport which will do for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •