I've been using Doug Steele's DNSless connection code for awile to connect Access 2010 to SQL Server 2008 R2 tables, and always used trusted connection.
Now I need to connect with a device not on the domain, but has network access. I thought I could use the SQL Server Security part of the code that I'd never had a need for before.
The code I'm using is below, it's virtually unchanged by me.
The problem is though, it's not working for me. When I hover over a table, after running this, it still shows the old connection string.
No errors happen when I run the below, or any indication something's awry.... but it didn't switch the tables to SQL Sever Security.
I'm stumped...... any ideas?
CODE
Sub FixConnections( _
ServerName As String, _
DatabaseName As String, _
Database2Name As String, _
Optional UID As String, _
Optional PWD As String _
)
' This code was originally written by
' Doug Steele, MVP AccessMVPHelp@gmail.com
' Modifications suggested by
' George Hepworth, MVP ghepworth@gpcdata.com
'
' You are free to use it in any application
' provided the copyright notice is left unchanged.
'
' Description: This subroutine looks for any TableDef objects in the
' database which have a connection string, and changes the
' Connect property of those TableDef objects to use a
' DSN-less connection.
' It then looks for any QueryDef objects in the database
' which have a connection string, and changes the Connect
' property of those pass-through queries to use the same
' DSN-less connection.
' This specific routine connects to the specified SQL Server
' database on a specified server.
' If a user ID and password are provided, it assumes
' SQL Server Security is being used.
' If no user ID and password are provided, it assumes
' trusted connection (Windows Security).
'
' Inputs: ServerName: Name of the SQL Server server (string)
' DatabaseName: Name of the database on that server (string)
' UID: User ID if using SQL Server Security (string)
' PWD: Password if using SQL Server Security (string)
'
On Error GoTo Err_FixConnections
Dim dbCurrent As DAO.Database
Dim prpCurrent As DAO.Property
Dim tdfCurrent As DAO.TableDef
Dim qdfCurrent As DAO.QueryDef
Dim intLoop As Integer
Dim intToChange As Integer
Dim strConnectionString As String
Dim strConnectionString2 As String
Dim strDescription As String
Dim strQdfConnect As String
Dim typNewTables() As TableDetails
'MsgBox (ServerName)
'MsgBox (DatabaseName)
'MsgBox (Database2Name)
' Start by checking whether using Trusted Connection or SQL Server Security
If (Len(UID) > 0 And Len(PWD) = 0) Or (Len(UID) = 0 And Len(PWD) > 0) Then
MsgBox "Must supply both User ID and Password to use SQL Server Security.", _
vbCritical + vbOKOnly, "Security Information Incorrect."
Exit Sub
Else
If Len(UID) > 0 And Len(PWD) > 0 Then
' Use SQL Server Security
strConnectionString = "ODBC;DRIVER={SQL SERVER};" & _
"SERVER=" & ServerName & ";" & _
"DATABASE=" & DatabaseName & ";" & _
"User ID=" & UID & ";" & _
"Password=" & PWD & ";"
MsgBox (strConnectionString)
Else
' Use Trusted Connection
strConnectionString = "ODBC;DRIVER={SQL server};" & _
"DATABASE=" & DatabaseName & ";" & _
"SERVER=" & ServerName & ";" & _
"Trusted_Connection=YES;"
MsgBox ("This is Trusted Connection")
End If
End If
intToChange = 0
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
' Build a list of all of the connected TableDefs and
' the tables to which they're connected.
For Each tdfCurrent In dbCurrent.TableDefs
If Len(tdfCurrent.Connect) > 0 Then
If UCase$(Left$(tdfCurrent.Connect, 5)) = "ODBC;" Then
ReDim Preserve typNewTables(0 To intToChange)
typNewTables(intToChange).Attributes = tdfCurrent.Attributes
typNewTables(intToChange).TableName = tdfCurrent.Name
typNewTables(intToChange).SourceTableName = tdfCurrent.SourceTableName
typNewTables(intToChange).IndexSQL = GenerateIndexSQL(tdfCurrent.Name)
typNewTables(intToChange).Description = Null
typNewTables(intToChange).Description = tdfCurrent.Properties("Description")
intToChange = intToChange + 1
' MsgBox (tdfCurrent.Name)
End If
End If
Next
' Loop through all of the linked tables we found
For intLoop = 0 To (intToChange - 1)
' Delete the existing TableDef object
dbCurrent.TableDefs.Delete typNewTables(intLoop).TableName
' MsgBox ("Deleted")
' Create a new TableDef object, using the DSN-less connection
' MsgBox ("Create New table: " & typNewTables(intLoop).TableName)
Set tdfCurrent = dbCurrent.CreateTableDef(typNewTables(intLoop).TableName)
tdfCurrent.Connect = strConnectionString
MsgBox ("Created New table" & typNewTables(intLoop).TableName & "With Connection String " & strConnectionString)
tdfCurrent.Attributes = typNewTables(intLoop).Attributes And DB_ATTACHSAVEPWD
tdfCurrent.SourceTableName = typNewTables(intLoop).SourceTableName
dbCurrent.TableDefs.Append tdfCurrent
' Where it existed, add the Description property to the new table.
If IsNull(typNewTables(intLoop).Description) = False Then
strDescription = CStr(typNewTables(intLoop).Description)
Set prpCurrent = tdfCurrent.CreateProperty("Description", dbText, strDescription)
tdfCurrent.Properties.Append prpCurrent
End If
' Where it existed, create the __UniqueIndex index on the new table.
If Len(typNewTables(intLoop).IndexSQL) > 0 Then
dbCurrent.Execute typNewTables(intLoop).IndexSQL, dbFailOnError
End If
Next
' Loop through all the QueryDef objects looked for pass-through queries to change.
' Note that, unlike TableDef objects, you do not have to delete and re-add the
' QueryDef objects: it's sufficient simply to change the Connect property.
' The reason for the changes to the error trapping are because of the scenario
' described in Addendum 6 below.
For Each qdfCurrent In dbCurrent.QueryDefs
On Error Resume Next
strQdfConnect = qdfCurrent.Connect
On Error GoTo Err_FixConnections
If Len(strQdfConnect) > 0 Then
If UCase$(Left$(qdfCurrent.Connect, 5)) = "ODBC;" Then
qdfCurrent.Connect = strConnectionString
End If
End If
strQdfConnect = vbNullString
Next qdfCurrent
End_FixConnections:
Set tdfCurrent = Nothing
Set dbCurrent = Nothing
Exit Sub
Err_FixConnections:
' Specific error trapping added for Error 3291
' (Syntax error in CREATE INDEX statement.), since that's what many
' people were encountering with the old code.
' Also added error trapping for Error 3270 (Property Not Found.)
' to handle tables which don't have a description.
Select Case err.Number
Case 3270
Resume Next
Case 3291
MsgBox "Problem creating the Index using" & vbCrLf & _
typNewTables(intLoop).IndexSQL, _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
Case 18456
MsgBox "Wrong User ID or Password.", _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
Case Else
MsgBox err.Description & " (" & err.Number & ") encountered", _
vbOKOnly + vbCritical, "Fix Connections"
Resume End_FixConnections
End Select
End Sub