Data Conversion Question (1 Viewer)

KACJR

Registered User.
Local time
Today, 06:10
Joined
Jul 26, 2012
Messages
81
Greetings to the well of knowledge...


When I upgraded my Access front-end from 2010 to 2016, one of my currency fields was converted to a Varbinary(510). The field displays properly in a report but I am unable to export the data into a spreadsheet; the field is blank. Is there a conversion I can do to make this export properly?


Thanks,
Ken
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Hi Ken. Just a guess but if you create a query with a calculated column like:


CCur([FieldName])


Would it work?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
PS. Are you talking about converting a 2010 Access database to a 2016 Access database? If so, I don't see Varbinary as a data type in Access 2016. How did you verify the data type?
 

KACJR

Registered User.
Local time
Today, 06:10
Joined
Jul 26, 2012
Messages
81
PS. Are you talking about converting a 2010 Access database to a 2016 Access database? If so, I don't see Varbinary as a data type in Access 2016. How did you verify the data type?
Sorry, it is VARbinary(510) in SQL Server. It comes up as an OLE Object in Access. It was Currency before I moved from 2010 to 2016. The SQL database is unchanged.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
Sorry, it is VARbinary(510) in SQL Server. It comes up as an OLE Object in Access. It was Currency before I moved from 2010 to 2016. The SQL database is unchanged.
Well, I'm still not clear. Did you convert an Access 2010 backend database into SQL Server 2016? If so, did you use SSMA to do it, or did you do it manually?
 

KACJR

Registered User.
Local time
Today, 06:10
Joined
Jul 26, 2012
Messages
81
Well, I'm still not clear. Did you convert an Access 2010 backend database into SQL Server 2016? If so, did you use SSMA to do it, or did you do it manually?


No. I upgraded the Access 2010 program to the Access 2016 program. I then relinked my SQL tables to my front-end (that was written in Access 2010) using "Linked Table Manager". After that, I noticed that a Currency field was now an OLE Object. When I looked into the SQL Server (2008R2), that particular table field was defined as "Varbinary(510)". Here's the thing, though. The data for this field displayed correctly in an Access report, but I cannot see the data in native datasheet view. All I see is "OLE Object". Strange!
 

isladogs

MVP / VIP
Local time
Today, 10:10
Joined
Jan 14, 2017
Messages
18,186

theDBguy

I’m here to help
Staff member
Local time
Today, 03:10
Joined
Oct 29, 2018
Messages
21,357
No. I upgraded the Access 2010 program to the Access 2016 program. I then relinked my SQL tables to my front-end (that was written in Access 2010) using "Linked Table Manager". After that, I noticed that a Currency field was now an OLE Object. When I looked into the SQL Server (2008R2), that particular table field was defined as "Varbinary(510)". Here's the thing, though. The data for this field displayed correctly in an Access report, but I cannot see the data in native datasheet view. All I see is "OLE Object". Strange!
Hi Ken. Thanks for the clarification. Did the problem happen as soon as you did the upgrade, or was it working before but now suddenly it changed? Just wondering if an OS update caused a problem here. There's something going on right now where an OS update affected databases connected to SQL Server. I'm curious if this is related to that.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:10
Joined
Feb 19, 2002
Messages
42,970
Fix the data type in the SQL Server table.
 

Users who are viewing this thread

Top Bottom