[AccessD] Changing DSN Connection strings

Paul Wolstenholme Paul.W at industrialcontrol.co.nz
Tue Dec 7 19:00:10 CST 2021


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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


More information about the AccessD mailing list