1

I am attempting to migrate a number of stored procedures, which insert to Microsoft Access 2002-2003 format database files, as linked servers.

The process is currently working fine on the following configuration

Windows Server 2008 R2 Standard
SQL Server 2014
Microsoft Access Database Engine 2010 x64 - v14.0.7015.1000

The updated configuration is as follows

Windows Server 2019 Datacenter
SQL Server 2019
Microsoft Access Database Engine 2010 x64 - v14.0.7015.1000

The Microsoft.ACE.OLEDB.12.0 has registered fine on the newer server configuration, and is available as a linked server "provider"

The SP inserts to a number of tables, with varying data types, and all are successful, with the exception of fields where the SQL Server field is of type nvarchar and the Access mdb field is of type "Long Text", where I get the error.

Msg 7344, Level 16, State 1, Line 182
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "[Access mdb Linked Server Name]" could not INSERT INTO table "[Access mdb as Linked Server]...[Table Name]" because of column "[Column Name]". The column had a bad status.

The Long text fields in the Access mdb are reported/displayed as varchar data type, in the linked server configuration.

I have tried writing simple, short, strings to the field, CASTing as varchar data type, but still get the same error.

I've done lots of googling, but can find no examples of others experiencing similar issues.

I suspect it is a compatibility issue, between the SQL server version and access database engine but, again, have been unable to find any useful resource.

Has anyone experienced a similar issue, and found a resolution?

Or if you can direct me to a suitable resource where I can find more info.

Matt
  • 23
  • 4
  • 1
    I would question why you are *still* using Access 2003. Support for Office 2003 ended on 08 April 2014; over **8 years ago**. – Thom A Aug 16 '22 at 10:53
  • Which casts have you tried? I'd try `NTEXT` which is closest to how Access actually stores the data – Erik A Aug 16 '22 at 10:55
  • HI @Lamu , it is a specification determined by a 3rd party data processor. Since posting, I've run a test with Microsoft.ACE.OLEDB.16.0 , and an accdb format Access database, but have the same issue. – Matt Aug 16 '22 at 12:28
  • Hi @ErikA , I had previously tried varchar as-is, and limiting to 255 characters, but have the same issue. i have just tried your suggestion of NTEXT, but no joy I'm afraid. – Matt Aug 16 '22 at 12:31
  • How does a 3rd party "determine" that Access 2003 is the choice to make in 2022; what sane company would ever determine that a product released **19 years ago** is the right choice..? I would suggest that what ever methods they used to determine that decision were *very* wrong. – Thom A Aug 16 '22 at 12:36
  • HI Again @Lamu, I appreciate what you're saying, but the solution was put in place many years ago, and is something I have adopted to work with. The issue has arisen due to the decommissioning of a host server. Longer term, an alternative solution will be developed, but an interim solution is required,. – Matt Aug 16 '22 at 12:54

1 Answers1

1

I managed to resolve this, with changes to the configuration of the OLEDB provider on the SQL server.

It was necessary to enable both: Dynamic Parameter and Allow inprocess

in the provider Options

Microsoft.ACE.OLEDB.12.0 Configuration options

MS Access mdb Linked Server configuration

Matt
  • 23
  • 4