Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    DSN less connections

    Hi Guys,

    when i was setting up my FE and BE i did the following steps:
    1. Create Applications
    2. Create one folder with FE on share drive
    3. When changes are made to FE i updated it and updated all links to BE within it (using DSN i think

    I know from Minty that you can use also DSN-less connections to link to your Database.

    What does it mean?
    You have code in FE which while starting creating connection to BE?

    Please help,


    Jacek

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Create one folder with FE on share drive
    this sounds like the road to corruption - hopefully users are not using this copy of the FE, but copying it to their local drive

    I don't understand point 3. links to BE should not need updating unless you have made changes to the BE

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Ajax,

    thank you.
    Why is it road to corruption?

    The second matter. FE changes are made in my local disc and i am linking it to local BE copy.
    And after updating it i am linking my FE with BE once again on share drive.

    What aoubt this DNS less connections? How it can help me with my updating system model?

    Jacek

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Why is it road to corruption?
    your post implies you have multiple users sharing the same front end. That is the road to corruption, each user must have their own copy of the front end on their local machine. Look around this forum, there are a number of current threads where the OP has allowed users to share a front end and is now experiencing problems. Here is one I've contributed to https://www.accessforums.net/showthr...815#post388815

    What aoubt this DNS less connections? How it can help me with my updating system model?
    As said before, I don't understand the question - No idea whether DSN less connections are relevant to your requirements or how they can help you.

    Here are some links which may help you
    https://www.databasejournal.com/feat...n-Tutorial.htm
    https://support.microsoft.com/en-us/...nked-tables-in
    http://www.accessmvp.com/djsteele/DSNLessLinks.html

  5. #5
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you Ajax.

    Please look here at post #19:

    https://www.accessforums.net/showthread.php?t=70731&page=2&p=388840#post388840


    Here Minty said that:

    Code:
    Because we only use the database internally we have preset up DSN saved in every users machine. 
    So the links are maintained automatically.
    
    I would have a read up about DSN less connections - this is by far the easiest way in your situation.
    and i am wondering how to do it.

    Jacek

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ok, regret not my area of expertise. Suggest you start a new thread with a more specific title such as 'how to preset dsn on each users machine' But I think Minty is installing odbc drivers with a specific connection string to each machine. Also include in your thread specific links to the thread you are referencing. As far as dsn less connections are concerned, the links I provided explain how to do it

  7. #7
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    thank you Ajax,

    i will wait a little maybe others can help us with this topic

    Best,
    Jacek

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have a look at this site which covers connection strings in detail with lots of examples
    https://www.connectionstrings.com/access/
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Thank you ridders52.

    I am using DSN connections like here:

    http://webcheatsheet.com/asp/dsn.php

    I am wondering how Minty set up DSN or DSN-less connection on every computer.
    (to set up it only once).

    I read that you can register every dsn connection once and have access to BE from every computer.

    Best Wishes,
    Jacek

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Following on from Paul's article, I use this function in various different setups:

    Code:
    Function GetConnectionString(strLinkType, strLinkServer, strLinkDatabase, _             strLinkUsername, strLinkPassword, strAppName ) As String
            
        Select Case strLinkType 'Get the link type to determine the connection string
    #If SQL_DRIVER = "Native" Then
            Case "SQL"
                GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=strAppName;" & _
                                        "UID=" & Nz(strLinkUsername, "") & ";" & _
                                        "PWD=" & Nz(strLinkPassword, "")
            Case "SQL-Trusted"
                GetConnectionString = "ODBC;DRIVER={SQL Native Client};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=strAppName;" & _
                                        "Trusted_Connection=yes;"
    #ElseIf SQL_DRIVER = "MDAC" Then
            Case "SQL"
                GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=strAppName;" & _
                                        "UID=" & Nz(strLinkUsername, "") & ";" & _
                                        "PWD=" & Nz(strLinkPassword, "")
            Case "SQL-Trusted"
                GetConnectionString = "ODBC;DRIVER={SQL Server};" & _
                                        "SERVER=" & Nz(strLinkServer, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkDatabase, "") & ";" & _
                                        "APP=strAppName;" & _
                                        "Trusted_Connection=yes;" & _
                                        ""
    #End If
            Case "Access"
                If Nz(strLinkPassword, "") <> "" Then
                    GetConnectionString = "MS Access;" & _
                                        "PWD=" & Nz(strLinkPassword, "") & ";" & _
                                        "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
                Else
                    GetConnectionString = "MS Access;" & _
                                        "DATABASE=" & Nz(strLinkServer, "") & Nz(strLinkDatabase, "")
                End If
            Case Else
                GetConnectionString = ""
        End Select
    
    
    End Function

    The table details are listed in two tables
    - tblTableLinkTypes - lists all external databases (SQL/Access) used by the FE application
    - tblTableLinks - lists all tables in each of the linked databases

    This makes it easy to add new tables or remove outdated tables

    The function below is used to add/remove linked tables based on the connection string(s)

    Code:
    Option Compare Database
    Option Explicit
    
    Public LinkFlag As Boolean
    Public MySet As DAO.Recordset
    
    
    Public Function UpdateTableLinks() 
    
    
    '################################################
    'Adds/removes linked tables depending on value of 
    'LinkFlag which is set in form used to manage table links
    '################################################
    
    
    On Error GoTo Err_UpdateTableLinks
    
    
    Dim strSQL1 As String
    
    
    strSQL1 = "SELECT tblTableLinks.TableName, tblTableLinks.TableAlias, tblTableLinks.LinkType," & _
        " tblTableLinkTypes.LinkServer, tblTableLinkTypes.LinkDatabase, tblTableLinkTypes.LinkUsername," & _
        " tblTableLinkTypes.LinkPassword, tblTableLinks.LinkActive" & _
        " FROM tblTableLinks INNER JOIN tblTableLinkTypes ON tblTableLinks.LinkType = tblTableLinkTypes.TableLinkType;"
    
    
    'Debug.Print strSQL1
    Set MySet = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset, dbSeeChanges)
    
    
    With MySet
        'CR - check if any links exist
        If .RecordCount = 0 Then Exit Function
        
        strLinkType = "SQL"
        strLinkServer = !LinkServer
        strLinkDatabase = !LinkDatabase
        strLinkUsername = !LinkUsername
        strLinkPassword = !LinkPassword
        
        strConnectionString = GetConnectionString(strLinkType, strLinkServer, strLinkDatabase, strLinkUsername, strLinkPassword)
        'Debug.Print "strConnectionString = " & strConnectionString
        
        If LinkFlag = "Yes" Then
            'add links to  tables
                Do Until .EOF
                    .Edit
                    strTableAlias = !TableAlias
                    strTableName = !TableName
                    If TestLink(strTableAlias) = False Then
                        LinkTable strTableName, strTableAlias, strConnectionString, True
                    End If
                   ' Debug.Print !TableAlias & " relinked"
                    .MoveNext
                    Loop
                .Close
        Else 'LinkFlag = "No"
            'remove all linked  tables
                Do Until .EOF
                    'strTableName = !TableAlias
                    .Edit
                    strTableAlias = !TableAlias
                    If TestLink(strTableAlias) = True Then
                        RemoveTableLink strTableAlias, True
                    End If
                   ' Debug.Print !TableAlias & " link removed"
                    .MoveNext
                    Loop
                .Close
        
        End If
    End With
    
    
    Set MySet = Nothing
    
    
    Exit_UpdateTableLinks:
        Exit Function
    
    
    Err_UpdateTableLinks:
        If Err.Number <> 3265 And Err.Number <> 3021 Then MsgBox Err.Number & " " & Err.Description
        Resume Exit_UpdateTableLinks
    
    
    End Function
    Once you have relinked the FE database to all external databases it is placed in a shared network Upgrade folder together with a version.txt file
    Users click on a desktop shortcut which checks whether the version in the Upgrade folder is newer than the file on their own local drive.
    If so, the newer file(s) are COPIED to that user's computer automatically

    IMPORTANT: USERS NEVER RUN THE APPLICATION FROM THE SHARED NETWORK FOLDER
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'll try and keep this short. Here is my $0.02....

    Quote Originally Posted by jaryszek View Post
    Hi Guys,

    when i was setting up my FE and BE i did the following steps:
    1. Create Applications
    2. Create one folder with FE on share drive
    3. When changes are made to FE i updated it and updated all links to BE within it (using DSN i think

    I know from Minty that you can use also DSN-less connections to link to your Database.

    What does it mean?
    You indicate that you have an Access FE and an Access BE.
    As stated above, each user should have a COPY of the FE on their computer (NOT a shortcut to a single shared FE ).
    As the dB is split, the FE should be linked to the BE using the "Linked Table Manager".
    This is because Access is a RAD application with a built in DBE (database engine).
    Pre-Office 2007, the DBE was "Jet". MS changed the Access built in DBE to "ACE" for Office 2007 and later.


    If you have an Access FE and a non-Access BE, and the DBE is SQL Server, MySQL, dB2, Oracle, Excel, a Text file, etc, the most common method to link the BE tables is to create an ODBC connection. In Windows you use the "Data Source (ODBC)" program.
    Somehow, each computer that has the Access FE needs access to an ODBC connection: a User, System or File DSN.

    An alternative to a DSN, is a DSN-less connection. This is creating a link to each table using a connection string. There is no DSN ODBC created.
    But you DO need to write VBA code to manage the connection strings if you want/need to change BEs.
    Using DSN-less connection strings is more secure (I read) because there is no DSN ODBC connection that would allow anyone to connect to the BE tables.

  13. #13
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Wow thank you Guys, i love you!

    fully understand and no further questions.

    Jacek

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    Am I right to assume, that this DNS-less connection is something like Connection for Excel ODBC query, where password to DB is displayed as plain text for any user having access to Excel file, even when the user doesn't have access to DB. (A reason, why in our network no Excel ODBC query to password-protected databases is allowed except using DNS connection.)

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not sure who Arvil's question is aimed at but it's nothing like that for my purposes.

    I distribute several databases to a number of clients e.g. schools.
    The Access FE will typically be linked to 1 or 2 SQL Server BE databases and up to three Access databases (one of which is a configuration db)
    Each client has their own paths for each item.
    So I distribute each version of the FE via my website with all links removed.
    When the client installs the updated FE, it reads their settings from the config file, then relinks all SQL /Access external databases using connection strings as outlined in my previous post. The big advantage for the client is that it requires no effort and for me as the developer, the big advantage is that I almost never have to provide assistance. It just works!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Help to find SQL connections string
    By datadc in forum Access
    Replies: 4
    Last Post: 01-16-2017, 08:37 PM
  2. Connections between forms and tables
    By lqangel in forum Programming
    Replies: 4
    Last Post: 09-15-2015, 07:25 PM
  3. Problems with Database connections
    By Goinfory in forum Misc
    Replies: 1
    Last Post: 06-22-2015, 06:13 AM
  4. DSN-less connections, how?
    By tpcolson in forum Access
    Replies: 6
    Last Post: 02-17-2014, 07:53 PM
  5. How many FE connections to BE are too many?
    By ItsMe in forum Database Design
    Replies: 3
    Last Post: 12-06-2013, 04:07 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums