Access DSNless connection issue.

• Kurt Wimberger

  SSC Veteran

  Points: 236

  More actions

  July 21, 2016 at 11:31 am

  #72651

  Hello all:

  SQl server 2012, Access 2013. I have been using a file DSN to connect and link tables. I am attempting to implement Chris Downs' DSNless connection script found here: https://blogs.office.com/2011/04/08/power-tip-improve-the-security-of-database-connections/

  ... but am having an issue at the 'Set rs = ' line...

  With qdf

  .Connect = connection

  .sql = "SELECT TOP 1 Id FROM dbo.tblTable;"

  Set rs = .OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)

  End With

  here's what the connection looks like as passed to the QueryDef:

  ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=ABCD-EFGH-666\QSRV2;APP=Microsoft® Windows® Operating System;DATABASE=myDatabase;;UID=myUID;PW=myPW;TABLE=dbo.tblTable;

  I receive the standard ODBC connection prompt when this 'set rs=' line executes, even though this is exactly what I am trying to avoid. I am guessing this is because the linked table in question (tblTable) is holding onto a reference to the old file DSN.

  Can anyone tell me if this is the case? And if so, how do I force the linked table to reference the DSNless connection I am trying to create instead?

  Thanks in advance for any help.

  Kurt

• Kurt Wimberger

  SSC Veteran

  Points: 236

  More actions

  July 22, 2016 at 10:52 am

  #1891639

  Looks like you DO need to loop all linked tables and recreate them programatically. Seems to be working so far!