July 21, 2016 at 11:31 am
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
July 22, 2016 at 10:52 am
Looks like you DO need to loop all linked tables and recreate them programatically. Seems to be working so far!
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy