sql-server-2008ms-accessodbcms-access-2003

Microsoft Access ODBC Connection Strings Limited to 255 Characters?


Microsoft Access 2003 database (.mdb) containing a linked table which connects via ODBC to a backend Microsoft SQL Server 2008 table.

When I enter design view for the linked table, then view the Properties field, I can only see the first 255 characters (specific fields replaced with hyphens):

ODBC;Description=------------------------------------;DRIVER=SQL Server;SERVER=----;APP=--------------------------------;WSID=---------;DATABASE=------------------;StatsLog_On=Yes;StatsLogFile=--------------------------------------------------------------

When I print the DAO field using the VBA Immediate Window (table name ommitted: "print CurrentDb.TableDefs("-----------").Connect"), I see a prefix plus 254 characters (prefix "ODBC;Description=" plus 254 characters, plus, presumably, a one-byte null character):

ODBC;Description=------------------------------------;DRIVER=SQL Server;SERVER=----;APP=--------------------------------;WSID=---------;DATABASE=------------------;StatsLog_On=Yes;StatsLogFile=------------------------------------------------------------------;Trusted_Co

How do I view the entire text of the ODBC connection string?

We experience an issue where the SQL Server server logs show error "Login failed for user "{user}". Reason: Could not find a login matching the name provided. [CLIENT: {ip address}]"

I am attempting to confirm the full string "Trusted_Connection=Yes" is part of the stored string.


Solution

  • The problem turned out to be a 255 character Microsoft Access (2003/2010) limitation on the ODBC connection string.

    I removed the ODBC references to StatsLog ant re-linked all the tables within the Microsoft Access database. It has been one week and the DBA team reports no further server log errors.