[AccessD] Rich Text Field Data from Azure Not Showing

Fri Jun 26 16:20:57 CDT 2020

Thanks Jim

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Dettman via AccessD
Sent: Saturday, 27 June 2020 1:18 a.m.
To: 'Access Developers discussion and problem solving'
Cc: Jim Dettman
Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing

 Did a little digging on the drivers and there is a good article here
covering what's available and what's not:

https://accessexperts.com/blog/2018/09/11/new-sql-server-odbc-and-oledb-driv
er/

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Stuart McLachlan
Sent: Thursday, June 25, 2020 6:38 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing

Try the OLEDB driver rather than ODBC

https://docs.microsoft.com/en-us/sql/connect/oledb/oledb-driver-for-sql-serv
er?view=sql-serv
er-ver15

"OLE DB was undeprecated and released in 2018.
The new OLE DB provider is called the Microsoft OLE DB Driver for SQL Server

(MSOLEDBSQL). The new provider will be updated with the most recent server
features going forward."

On 26 Jun 2020 at 8:14, David Emerson wrote:

> This is the driver we are using for the connection string:
> 
> gADODBConnection.ConnectionString = "Driver={ODBC Driver 17 for SQL
> Server};Server=tcp:prism-prd-failover.database.windows.net,1433;Databa
> se=Pri
> sm-prd-db;UID=USER;Pwd=VERYSECUREPWD;Encrypt=yes;TrustServerCertificat
> e=no;C onnection Timeout=60"
> 
> David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Jim Dettman via AccessD Sent: Friday, 26 June 2020 12:16 a.m. To:
> 'Access Developers discussion and problem solving' Cc: Jim Dettman;
> 'Paul Hartland' Subject: Re: [AccessD] Rich Text Field Data from Azure
> Not Showing
> 
> 
>  I would think the problem relates to the SQL driver you are using and
>  would
> want to use the SQL Native driver.
> 
>  That's the only way you are going to pick up the newer features in
> SQL/Azure.
> 
>   I'm still mostly stuck in the stone ages (pre SQL 2005), so it's not
> something I've played with, but from reading other questions/problems,
> that's probably where the problem lies.  A lot changed in SQL with
> 2005 and specifically with this, the way BLOB fields were terminated.
> 
> Jim.
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of David Emerson Sent: Wednesday, June 24, 2020 11:50 PM To: 'Access
> Developers discussion and problem solving' Cc: 'Paul Hartland'
> Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing
> 
> Further to below, we can see the data in the fields in Access now but
> we have the problem that we are limited to about 3000 characters
> depending on the formatting (bold, italics, etc) which takes up extra
> storage.  The client uses this field for pasting email body
> information which often goes over this limitation.
> 
> Does anyone have a solution to this dilemma?
> 
> Regards,  David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of David Emerson Sent: Monday, 22 June 2020 7:59 a.m. To: 'Access
> Developers discussion and problem solving' Cc: 'Paul Hartland'
> Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing
> 
> I was able to make the change to varchar(8000) and that fixed it. 
> Thanks for your help.
> 
> Regards, David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of David Emerson Sent: Sunday, 21 June 2020 6:55 p.m. To: 'Access
> Developers discussion and problem solving' Cc: 'Paul Hartland'
> Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing
> 
> I am reluctant to do that at this stage because it is their live data
> that we are working on (the original UAT testing didn't show this as a
> problem) :(
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf
> Of Paul Hartland via AccessD Sent: Sunday, 21 June 2020 6:43 p.m. To:
> Access Developers discussion and problem solving Cc: Paul Hartland
> Subject: Re: [AccessD] Rich Text Field Data from Azure Not Showing
> 
> I think I remember something about this but only on mobile at moment
> so cant look up any notes, is it possible to try changing the varchar
> from varchar(max) to varchar(8000) as a quick test.
> 
> Paul
> 
> 
> On Sun, 21 Jun 2020, 07:30 David Emerson, <newsgrps at dalyn.co.nz>
> wrote:
> 
> Sorry about the cross post but my client has decided to go live
> tomorrow and there is an annoying problem I would like to solve.
> 
> They have an existing SQL Server database that has been migrated to
> Azure. The Access front end is an accdb.  There are three tables that
> have varchar(MAX) datatypes which are used to populate text fields in
> Access that have their text Format properties set to Rich Text.  This
> works as expected with the text in the fields being able to be
> formatted.
> 
> After migrating to Azure and only needing to change the connection
> strings the Access file works as per the current system with the
> exception that the Rich Text fields do not display any data!
> 
> I can enter data in the field and it saves to the table but as soon as
> I refresh the screen nothing is shown in the field (it is still in the
> underlying table in SQL though).
> 
> Normally I get the data for my forms using ADODB.Recordsets.  When I
> put a msgbox line in the VBA to show the contents of the field it
> comes up with Null.   However, when I create a direct link to the
> table in the Tables Group and open the table I can see the text in the
> field with all the rich text formatting.
> 
> Is there something about Azure and ADODB that doesn't like
> varchar(MAX) data types?
> 
> Regards
> 
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com