Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2011

    Unanswered: DSN-less connections, how?

    I have a MSAccess 2010 front-end application pointing to tables in SQL 2008 SP1. Security is integrated windows, so if they're logged into a computer, they get access. We try to keep things simple. Currently we're having to set up a file DSN on each users computer to make the linked tables work, and constantly have to relink the tables due to users messing with the DSN definition, etc..

    The obvious solution is to hard-code DSN-less connections at application start up, as the table definitions will never change.

    There are A TON of examples out there on how to do this, but my problem is that all these great examples seem geared towards folks that are expert VB programmers, and a bit over the learning curve of a typical access user without a computer programming degree. Are there any SIMPLE tutorials or examples that clearly explain this concept? Perhaps an Access Database that one can download that has all the example code embedded in these module-thingies? Forgive my ignorance, I know as much about access databases as I do nuclear physics, is just the hat I've been assigned to wear. Thanks.

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    The definition of a table and its properties in Access can be instanciated in a DAO.TableDef object. Each Tabledef object has a Connect property which can be empty (zero-length string: "") when the Tabledef represents an internal table or it can contain a connection string when the Tabledef represents a linked (attached) table. Depending on the type and location of the actual table (i.e. where the data are actually stored), the contents of the connection string varies. For SQL Server linked tables this connection string typically is in the format:
    1. For a connection using the Active Directory security:
    ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Tr usted_Connection=Yes;
    2. For a connection using a user and password defined in the SQL Server:
    ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;UI D=UserName; PWD=Password;
    See also:

    The ODBC interface available in Windows provides such a connection string.

    The easiest method for refreshing linked tables without using the DSN defined in the ODBC manager consists in providing your own connection string then use the RefreshLink method of the Tabledef object.

    Here's an example that refreshes the link of every linked table in a database:
    Sub RelinkTables(ByVal ConnectionString As String)
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs            ' For every table in the database.
            If Len(tdf.Connect) > 0 Then         ' If the current table is a linked table.
                tdf.Connect = ConnectionString   ' Provide the new connection string.
                tdf.RefreshLink                  ' Refresh the connection to the server.
            End If
    End Sub
    The only problem that remains is where to store the connection string. You can hard-code it (preferably in a constant), store it in an ini file (hazardous because everyone can open it and mess with its contents), store it in the registry (you'll need to use API's to access the registry), or any using other way you see fit.

    Here's an example that refreshes the links when the application starts:
    1. The database comprises a macro named AutoExec:
    Action: RunCode
    Function Name: StartUp()

    2. The database comprises a standard module, like this:
    Option Compare Database
    Option Explicit
    Const c_Connect As String = "ODBC;DRIVER={SQL Server};SERVER=BSMM-BRAVO;DATABASE=Sales;Trusted_Connection=Yes;"
    Public Sub RelinkTables(ByVal ConnectionString As String)
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Set dbs = CurrentDb
        For Each tdf In dbs.TableDefs
            If Len(tdf.Connect) > 0 Then
                tdf.Connect = ConnectionString
            End If
    End Sub
    Public Function Startup()
        RelinkTables c_Connect
    End Function
    Every time the database is started, the macro AutoExec is executed, which calls the StartUp() function, which calls the RelinkTables procedure which does the job.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts