DSN-less connections, how?

[tpcolson]

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.

[ItsMe]

What is a file DSN and how are users able to manipulate it?

[Xipooo]

Well frankly it sounds like you're running under a domain environment and your Domain Admin should be handling ODBC connections through Group Policy. I am also not sure WHY anyone would be messing with your DSN definitions.. Again it sounds like your domain admin should be restricting local user access policy.

But I do feel your plight. Not everyone has the time to meddle with such things and instead you've become the fall guy.

So here is a code snippet from a developer form which I use to reconnect all of my tables and queries.

Private Sub btnConnect_Click()
    DoCmd.Hourglass True
    Dim db As dao.Database
    Dim tdef As TableDef
    Dim qdef As QueryDef
    Dim missingTbl() As String
    Dim upper As Integer
    Dim i As Integer
    Dim strMsg As String
    
    ReDim missingTbl(0 To 0)
    
    Set db = CurrentDb
    For i = 0 To CurrentDb.TableDefs.COUNT - 1
        Set tdef = db.TableDefs(i)
        If tdef.Connect <> "" And DCount("tablename", "tbl9TableAssignments", "TableName='" & tdef.name & "' AND EnvironmentID=" & Me.cboEnvironments & "") > 0 Then
            tdef.Connect = DLookup("DSN", "tbl9TableAssignments", "TableName='" & tdef.name & "' AND EnvironmentID=" & Me.cboEnvironments & "")
            On Error GoTo NoTable
            tdef.RefreshLink
            
NoTable:
            If Err.Number = 3011 Then
                upper = UBound(missingTbl)
                ReDim Preserve missingTbl(0 To upper + 1)
                missingTbl(upper) = tdef.name
            End If
        End If
    Next
    
    For Each qdef In db.QueryDefs
        If qdef.Connect <> "" Then
            If DCount("QueryName", "tbl9SPTAssignments", "QueryName='" & qdef.name & "' AND EnvironmentID=" & Me.cboEnvironments & "") > 0 Then
                qdef.Connect = DLookup("DSN", "tbl9SPTAssignments", "QueryName='" & qdef.name & "' AND EnvironmentID=" & Me.cboEnvironments & "")
            End If
        End If
    Next
    strMsg = "These tables could not be found on the new connection: "
    For i = 0 To UBound(missingTbl)
        strMsg = strMsg & vbCrLf & missingTbl(i)
    Next i
    If missingTbl(0) <> "" Then
        MsgBox strMsg & vbCrLf & "Some of your queries may not work as a result.", vbCritical, "Missing Tables"
    End If
    CurrentDb.Execute "UPDATE tbl9WorkControl SET CurrentEnvironment=" & Me.cboEnvironments & ""
    Me.txtCurrentEnvironment.Requery
    DoCmd.Hourglass False
    MsgBox "Connection process complete.", vbInformation, "Done"
End Sub

Now in my case I'm storing all of the tablenames with the appropriate DSN strings based on the environment I want to run the database in. IE, development environment will assign some tables to certain DSN strings while other tables may need to point to other DSN strings... etc. Then I can switch environments and connect to the appropriate databases for the environment I want to be running the application in. I suspect other developers do something similar to switch back and forth between environments.

The key bit for you is the parts in the

For i = 0 To CurrentDb.TableDefs.COUNT - 1

loop which changes all of the table definitions connection strings, and the

For Each qdef In db.QueryDefs

loop.

Each of these iterates through the table or pass-through queries and re-assigns the DSN. That should eliminate your need to constantly be resetting the DSN's in your databases because you can make the database do the changing and refreshing for you.

I should also mention my tbl9SPTAssignments and tbl9TableAssignments are the tables that hold my DSN connection strings associated with the tables and environment.

[tpcolson]

A few questions, which may seem amateur:

How would I attach this to the VB code in autoexec macro so it runs automatically at DB open?
Does this first, require, that the linked tables be defined using ODBC? I'm assuming tbl9SPTAssignments and tbl9TableAssignments are defining the ODBC connection, but I'm not entirely grasping this....sorry!
I don't see any server or database name variables...?
Does this assign a local index to non-indexed SQL views?

The overall intent here is to eliminate having to use control panel to define ODBC connections, or a domain admin that is another state (and trying to explain to him what ODBC stands for) to manage linked tables. I understand that one can define a DSN-less connection for linked tables, and have it fire on startup? Is that a correct assumption?

[Xipooo]

A couple of things you have to understand first. Macros are not VBA. Though some people call VBA code macros... for some unknown reason. But this only confuses things.

Macros are a special kind of logic that you can add to your application. VBA is a coding language separate from Macros which is more complex but offers more flexibility and maneuverability for your application than Macros do. The AutoExec macro is not the place to be doing a complex manipulation of your application like changing the connection strings on your tables. It simply isn't capable of handling such complex notions... so you MUST use VBA to do this.

The code I provided is a sample bit of VBA code which runs when someone clicks on a button on my form. It does not precisely match your situation but may offer an idea of how someone might go about doing it... and I mistakenly jumped the gun by giving it to you. It appears you don't know enough about VBA to understand what I was driving at, and I apologize for making things a bit more jumbled than they already were.

As you can see, the reason there is no "easy tutorial" on how to do this is because there is no "easy" way to do it unless you understand VBA. It turns out that it's really not too difficult a thing if you know how to code VBA, but without any knowledge at all of how VBA works then it's kind of difficult. Sorta like trying to teach someone how to back up an 18 wheeler when they've never been in the drivers seat of a car before.

Now, after that long pronouncement let me see if I can actually be of some help to you.

Step 1. Create a form. Save it and name it "frmSplashScreen".
Step 2. Open the form in "Design View"
Step 3. Click on the "Design Tab" and then make sure the "Property Sheet" button is clicked so that you can see the "Property Sheet" somewhere on your screen.
Step 4. On your Property Sheet, click on the "Event Tab"
Step 5. Look through the list and click on the empty textbox next to where it says "On Load".
Step 6. Click on the Ellipsis ("...") button next to where you just clicked, and select "Code Builder"
Step 7. Paste the following code in the entire window.

Option Compare Database
Option Explicit


Private Sub Form_Load()
    Dim db As DAO.Database
    Dim strConnect As String
    Dim tdef As TableDef
    Dim qdef As QueryDef
    Dim i As Integer
    
    Set db = CurrentDb
    '==================================================================
    '==== In the line below change <ServerName> and <DatabaseName> ====
    '==== to the Server and Database Name you want to connect to.  ====
    '==================================================================
    strConnect = "ODBC;DRIVER=SQL Server;SERVER=<ServerName>;DATABASE=<DatabaseName>;Trusted_Connection=Yes"
    For i = 0 To CurrentDb.TableDefs.COUNT - 1
        Set tdef = db.TableDefs(i)
        If tdef.Connect <> "" Then
            tdef.Connect = strConnect
            tdef.RefreshLink
        End If
    Next i
    For Each qdef In db.QueryDefs
        If qdef.Connect <> "" Then
            qdef.Connect = strConnect
        End If
    Next
    '=================================================
    '==== In the line below change the <FormName> ====
    '==== to the first form you want to open.     ====
    '=================================================
    DoCmd.OpenForm "<FormName>"
    DoCmd.Close acForm, "frmSplashScreen"
End Sub

Step 8. Change <ServerName>, <DatabaseName>, and <FormName> appropriately.
Step 9. Save and exit the window

The next part depends on your version of Access, but you want to set the "Display Form" to frmSplashScreen. In Access 2010 you go to File->Options->Current Database and click on the "Display Form" drop down to select the frmSplashScreen form.

Now when your database opens the first form it will be the frmSplashScreen. The code you inserted will go through the process of changing your connection strings on all of your tables and pass-through queries to a DSN-less string and you will no longer rely upon building one through the ODBCad32.exe.

[ItsMe]

I think the OP is trying to use code to create a connection via the ODBC drivers; without depending on the Windows ODBC connection manager.

[ssanfu]

As a passing thought:

Having people muck around in places they know they shouldn't be is an administrative problem, not a programming problem.

While you might be able to program a solution (and should have some security), I have seen where the solution was: the first time you were caught "programming" when it was not your job was time off without pay. The second time was dismissal.

The program was mission critical, and the bigwigs had no sense of humor when it comes to losing money.

@Xipooo,
Thanks for sharing your code. I am going to compare yours to mine to hopefully improve my relinking setup. I'm using an Access BE right now, but we are going to move to SQL Server sometime this year. I think your code will be very helpful.