[AccessD] Changing DSN Connection strings

Tue Dec 7 19:47:53 CST 2021

Ah, that is for creating a table link.  I was hoping for something easier
but may need to go down the delete and recreate link path.

-----Original Message-----
From: AccessD <accessd-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com> On
Behalf Of Paul Wolstenholme
Sent: Wednesday, 8 December 2021 2:00 pm
To: Access Developers discussion and problem solving
<accessd at databaseadvisors.com>; David Emerson <David at dalyn.co.nz>
Subject: Re: [AccessD] Changing DSN Connection strings

David,

This routine works for me in Access 2010 with SQL Server 2014:

===
Public Function LinkTableDAO( _
    ByRef stAccessTableName As String _
    , Optional ByRef stSourceTableName As String = "" _
    , Optional ByRef stConnectionString As String = "" _
    , Optional ByRef stDescription As String = "" _
    , Optional ByRef stIndexSQL As String = "" _
    , Optional ByRef bSavePassword As Boolean = False _
    ) As Boolean
' Based on:
'   Chipman & Baron pg 189
' Modified to accept connect string and support indexing (of views) '
Created ICEL.prw 10/11/2017.

    ' Un-links, Links or Re-links a single table(or view).
    ' Returns True on success.
On Error GoTo MyErrorHandler
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim prp As DAO.Property

    LinkTableDAO = False    ' default
    Set db = CurrentDb
    ' if the link already exists, delete it On Error GoTo
MyErrorHandlerNoLink
    Set tdf = db.TableDefs(stAccessTableName)
    ' Found an existing tabledef.
    If (tdf.Attributes And dbAttachedODBC) _
              <> dbAttachedODBC Then
        ' This isn't an ODBC linked table - ERROR
        Call LogError(-421001, "Table already exists but it is not an ODBC
link", "mICELBackEnd - LinkTableDAO()", stAccessTableName)
        GoTo MyExit
    End If
    db.TableDefs.Delete stAccessTableName
    db.TableDefs.Refresh
NoLinkExists:
On Error GoTo MyErrorHandler
    ' Interpret no connection string as do not link
    If "" = stConnectionString Then
        LinkTableDAO = True ' completed ok
        GoTo MyExit
    End If
    ' Create a new TableDef object
    Set tdf = db.CreateTableDef(stAccessTableName)
    ' Set the Connect and SourceTableName
    ' properties to establish the link
    tdf.Connect = stConnectionString
    tdf.SourceTableName = stSourceTableName
    If bSavePassword Then
        tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
    End If
    ' Append to the database's TableDefs collection
    db.TableDefs.Append tdf
    ' Where it existed, add the Description property to the new table.
    If Len(stDescription) > 0 Then
      Set prp = tdf.CreateProperty("Description", dbText, stDescription)
      tdf.Properties.Append prp
    End If
    ' Create the index if specified
    If Len(stIndexSQL) > 0 Then
        CurrentDb.Execute stIndexSQL, dbFailOnError
    End If
    ' Turn off SubDataSheets
    ' tdf.Properties("SubDataSheetName").Value
    Set prp = tdf.CreateProperty("SubDataSheetName", dbText, "[None]")
    tdf.Properties.Append prp

    LinkTableDAO = True
MyExit:                 ' Label to resume after error.
On Error Resume Next
    Set prp = Nothing
    Set tdf = Nothing
    Exit Function       ' Exit before error handler.
MyErrorHandlerNoLink:   ' Handler for table not currently linked
    If 3265 = Err.Number Then Resume NoLinkExists   ' item not found in
this collection
MyErrorHandler:         ' Label to jump to on error.
    Call LogError(Err.Number, Err.Description, "mICELBackEnd -
LinkTableDAO()")
    LinkTableDAO = False
    Resume MyExit       ' Pick up again and quit.
    Resume              ' for debug (Set Next Statement)
End Function
===

Paul Wolstenholme

On Wed, 8 Dec 2021 at 13:53, David Emerson <newsgrps at dalyn.co.nz> wrote:

> I tried that but got an Invalid Argument error.
>
> -----Original Message-----
> From: AccessD 
> <accessd-bounces+newsgrps=dalyn.co.nz at databaseadvisors.com>
> On
> Behalf Of David Emerson
> Sent: Wednesday, 8 December 2021 1:42 pm
> To: 'Paul Wolstenholme' <Paul.W at industrialcontrol.co.nz>
> Cc: 'AccessD' <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Changing DSN Connection strings
>
> Thanks Paul,
>
> Should that be just before
>                     tdf.RefreshLink                     'Refresh the Link.
>
> Regards
> David
>
>
>
> From: Paul Wolstenholme <Paul.W at industrialcontrol.co.nz>
> Sent: Wednesday, 8 December 2021 1:28 pm
> To: newsgrps at dalyn.co.nz
> Cc: AccessD <accessd at databaseadvisors.com>
> Subject: Re: [AccessD] Changing DSN Connection strings
>
>  David
>
> You need to add this VBA line:
> tdf.Attributes = tdf.Attributes Or dbAttachSavePWD
>
> That corresponds to the linked table manager's pop-up that warns about 
> saving the password  with the linked table in the Access database file 
> because it isn't safe.  Some would suggest you should unlink all 
> tables on closing the database and relink them on opening (presumably 
> they also assume the front end is never shared).
>
> Paul Wolstenholme
>
>
> On Wed, 8 Dec 2021 at 13:10, David Emerson <newsgrps at dalyn.co.nz 
> <mailto:newsgrps at dalyn.co.nz> > wrote:
>
> Hi Listers,
>
> I have inherited a data base which has code which updates the 
> connection string of the tables between two different databases.  The 
> user selects which database to link to and the code updates the 
> table's connect
> property:
>
> tdf.Connect = strConnect_NEW
>
> strConnect_NEW is in this format (with the actual password in place of
> {Pwrd}):
>
> ODBC:DSN= StockListSQL_Data_Archive;Description=
> StockListSQL_Data_Archive;APP=Microsoft
> Office;DATABASE=SL_DataSQL;User_Id=SQL_User;Password={Pwrd]
>
> The only difference between the two connection strings in the name of 
> the Database
>
> The Database could initially be connected to either of the SQL databases.
> The first time the database connection is changed with this code:
>
>                     tdf.Connect = strConnect_NEW
>                     'Refresh the table link.
>                     tdf.RefreshLink                     'Refresh the Link.
>
> an SQL Server Login dialog box opens showing the Data Source, and 
> Login ID, and asking for the password.  Once the Password is entered 
> then the user can switch between the database tables without having to 
> reenter the password.
> However, if the Access program is closed and reopened then the first 
> time the database connection is changed the password is requested again.
>
> Why is the Access program asking for the password when the password is 
> entered in the Connection string?  Is there some other connection that 
> needs the password saved in it?
>
> When the DSN is set up for each user the "Connect to SQL Server to 
> obtain default settings for the additional configuration options" box 
> is ticked and the Login ID and Password is entered.
>
> Regards
>
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand