4

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.

iokevins
  • 1,427
  • 2
  • 19
  • 29
  • Looks like there may not be a way. Command "print RIGHT(CurrentDb.TableDefs("-----").Connect, 10)" displays "Trusted_Co" :o( – iokevins Jul 21 '11 at 23:19
  • Although this may explain why Windows Authentication is not getting used? I am re-linking with a DSN connection string < 255 characters. – iokevins Jul 21 '11 at 23:20
  • Just noted one additional observation: the Properties field shows 255 characters, including "ODBC;Description=" prefix, while the Immediate Window displays 254 characters past "ODBC;Description=". So, in both cases, seems like I am hitting the 2^8 limit. – iokevins Jul 21 '11 at 23:23

3 Answers3

2

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.

iokevins
  • 1,427
  • 2
  • 19
  • 29
2

Update for Access 2016:

Max length of connect string for

  • Linked tables: 513 chars
  • Pass-through queries: 255 chars

(OP asked about Access 2003 but this question is the only one on this topic.)

Belladonna
  • 161
  • 1
  • 10
0

Try Shift-F2. That's the keyboard shortcut throughout Access for the Zoom box, and it works in most properties as well.

TheOtherTimDuncan
  • 603
  • 4
  • 6