Results 1 to 5 of 5
  1. #1
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066

    Upgrade from SQL Server 2008 to SQL Server 2014

    We have an Access 2010 Front end to SQL Server Backend Database here for 4 departments. There is a daily spreadsheet sent out from our Corporate HQ that is imported into this DB and the new data is this spreadsheet is added to our database. This worked flawlessly for the last year until this week. Over the weekend our SQL Server was upgraded from SQL Server 2008 R2 to SQL Server 2014 to accommodate a change at the corporate level. Now the daily spreadsheet fails to import the data with a data conversion error.



    Click image for larger version. 

Name:	dataconversionerror.jpg 
Views:	9 
Size:	130.4 KB 
ID:	29391

    It looks to me like one field has the conversion error so I click on yes then I can query the data and see which field is the problem. However, none of the data is actually uploaded. I've checked the field types that are being updated and other than the field size they match. Access gives the text fields on the imported spreadsheet as 255 characters. None of the actual text fields are over 100 characters and all of the data in the spreadsheet is under the field length for each of the imported fields. The field types match with the exception of 2 fields in SQL Server they are binary MS Access shows them as yes/no. The data for those fields when displayed in Access is -1 and 0 when displayed in SQL Server is 1 and 0. Other than that I don't see any difference. Anybody have an idea? I've checked everything I can think of but still get the same error.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you may need to upgrade your ODBC driver - think it is up to v13 now

  3. #3
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    We use the Native Client V11 ODBC Driver. which is current.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just a wild guess, but the only other thing I can think of to check is if the primary keys on your server are bigint - it's not recognised in 2010.

  5. #5
    RayMilhon is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,066
    Nope the Primary Keys are Longs using the Identity property. Finally figured out which field it is still not sure why it occurred there is a field for created date that was putting in the Access Function Now(). Took that out and changed the Field in SQL Server to default to getdate() that solved it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help on Access Attachments Migration to SQL server 2014
    By sahadevreddy@gmail.com in forum Import/Export Data
    Replies: 2
    Last Post: 12-23-2016, 01:51 AM
  2. Replies: 1
    Last Post: 08-11-2016, 08:25 AM
  3. Replies: 8
    Last Post: 11-05-2015, 06:06 AM
  4. Query not working after migrating to SQL Server 2014
    By Esmatullaharifi in forum Queries
    Replies: 7
    Last Post: 04-18-2015, 05:21 AM
  5. Replies: 0
    Last Post: 12-20-2012, 01:44 PM

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