MS Access DSN-less connection

It is possible to create a DSN less linked table and/or pass-through query from Microsoft Access to SQL Server Express without deleting those objects first, as is often suggested, e.g. here:

http://www.accessmvp.com/djsteele/DSNLessLinks.html

This is safer, because the linked objects will be gone if the code fails after deleting the objects, but before re-adding has completed.

One of the downsides of this approach is that MS Access has to be re-started for the new connections to be effected. In more detail: Access caches ODBC connections. For example, when a working ODBC connection of a linked table is replaced with one that has a wrong username, it will still work, because the previous one is retrieved from cache. It is not possible to clear this cache programmatically or manually, unless Access is restarted. Furthermore, Access only adds new connections to the cache when the driver, server or database property changes. Adding a new and correct ODBC connection to a linked table that only as a different login than the old one has no effect! The old connection from the cache will be used.

Another downside is that the database grows significantly after each execution of the code. A compact/repair resolves this. Set the "compact on close" property on the database.

Use this code to loop over all tables and queries to set the connection string:

Dim db As Database
Dim td As TableDef
Dim qd As QueryDef
Dim strODBC As String

  Set db = DBEngine(0)(0)
  strODBC = "ODBC;DRIVER=SQL Server;SERVER=REMOTESERVER\SQLEXPRESS;DATABASE=TestDB;UID=sa;PWD=sa;Trusted_Connection=No;"

  ' reconnect pass-through queries
  For Each qd In db.QueryDefs
    With qd
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a passthrough query the uid and pwd are always stored
        .Connect = strODBC 
        .Close
      End If
    End With
Next qd

  ' reconnect linked tables
  For Each td In db.TableDefs
    With td
      If Left$(.Connect, 4) = "ODBC" Then
        ' note: for a linked table the uid and pwd are only stored when this property is set!
        .Attributes = dbAttachSavePWD
        .Connect = strODBC
        .RefreshLink
      End If
    End With
  Next td

  db.Close
  Set db = Nothing

Notes:
  • Use DBEngine(0)(0) in stead of CurrentDB(); the implementation is 1000x faster
  • Use Left$ in stead of Left; the implementation is faster
  • This particular code assumes that DAO is used, not ADO
  • The connection uses a SQL Server Authentication instead of Windows Authentication
  • The connection string value is assigned to variable strODBC
  • The connection string has to start with the text "ODBC;" both for the linked table as the pass-through query
  • If the connection is to a default instance of SQL Server, the string "\SQLEXPRESS" can be removed
  • By default, the username and password are stored for a pass-through query, but not for the linked table. This appears to be a bug, it either should be set for both or for neither. Setting the uid/pwd parameters for a linked table does, however, not result in an error
  • ... but if the code td.Attributes = dbAttachSavePWD is used, then the uid/pwd are also stored for a linked table. At least for MS Access 2010 on Windows 7 x64, this is enough. In some cases two registry settings need to be set for this to work:
    REGEDIT4
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\Network]
    "DisablePwdCaching"=dword:00000000
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\8.0\Common\Security]
    "DisablePwdCaching"=dword:00000000
  • After the code has run, the connection string has been set for all the objects, but is not yet effective! For this, MS Access needs to be closed and reopened
  • I found that the reconnecting loop using DRIVER=SQL Server in stead of
    DRIVER={SQL Server}or
    PROVIDER={SQL Server Native Client 10.0 ODBC Driver}
    was much faster
  • Close and destroy the reference to the database object to prevent memory leaks
Related errors might include:

"login failed. The login is from untrusted domain and cannot be used with windows authentication"

PALO backend is unavailable

You might get an "backend is unavailable" error when connecting to the PALO server from an internet page.

Check the %SYSTEMDRIVE%\Program Files (x86)\Jedox\Palo Suite\log\core.log file for a probable cause. In my case the fonts folder did not exist:

[2012/05/02 10:22:28] ERROR [core] palo web core server version boost::filesystem::basic_directory_iterator constructor: The system cannot find the path specified: "C:\WINDOWS\Fonts"

The reason for this error is that Windows and the program files folder are installed on my G drive and PALO apparently hardcodedly searches the C drive for these fonts. The service PaloWebCoreServerService cannot start because of this error.

Resolution:
  • Create a folder called c:\windows\fonts
  • Copy at least the following fonts from the g:\windows\fonts folder:
    andalemo.ttf
    arial.ttf
    arialbd.ttf
    comic.ttf
    courier.ttf
    georgie.ttf
    impact.ttf
    times.ttf
    trebuc.ttf
    verdana.ttf
    webdings.ttf
    If any of these fonts is not available in the folder, then download them from Microsoft:
    http://sourceforge.net/projects/corefonts/files/the%20fonts/final/
  • Start the service PaloWebCoreServerService