Nota del autor: Esta es la parte 2 de una serie sobre tablas sin DSN en Access.
Puedes revisar la primera parte aquí. Tercera parte aquí. Cuarta parte aquí.
En la publicación anterior hablé sobre el uso de una sola tabla en su base de datos de SQL Server para administrar fácilmente la seguridad, en la publicación de hoy voy a ir un paso más allá y usar esa misma tabla para crear todos los enlaces necesarios necesarios para su aplicación en puesta en marcha.
Use ADODB para recorrer la tabla y crear enlaces
La técnica utiliza un conjunto de registros ADODB para recorrer todos los registros en tblTablePermissions y crear los enlaces al inicio. Almacenamos el nombre y la contraseña del usuario con el enlace, por lo que utilizamos un formulario de inicio de sesión personalizado para almacenar el nombre y la contraseña del usuario, que luego se utilizan para crear los enlaces.
Paso 1: Capture la información de inicio de sesión del usuario y pruebe para ver si pueden conectarse con SQL Server
Aquí está el código que usamos cuando el usuario hace clic en el botón de inicio de sesión en nuestro formulario de inicio de sesión:
Visual Basic Private Sub cmdLogin() If IsNull(Me.txtUserID) Or IsNull(Me.txtPassword) Then MsgBox "You must supply both your user name and password", vbInformation, "Can't Login" Exit Sub End If 'Store login credentials in memory TempVars.Add "UserID", Me.txtUserID.Value TempVars.Add "Password", Me.txtPassword.Value If InStr(1, CurrentProject.Name, "Beta") > 0 Then TempVars.Add "IsBeta", True Else TempVars.Add "IsBeta", False End If If Not OpenMyConnection Then MsgBox "Connection to SQL Server failed, please verify your user name and/or password", vbInformation, "Login Failed" Exit Sub End If RelinkAllTablesADOX End Sub Private Sub cmdLogin() If IsNull(Me.txtUserID) Or IsNull(Me.txtPassword) Then MsgBox "You must supply both your user name and password", vbInformation, "Can't Login" Exit Sub End If 'Store login credentials in memory TempVars.Add "UserID", Me.txtUserID.Value TempVars.Add "Password", Me.txtPassword.Value If InStr(1, CurrentProject.Name, "Beta") > 0 Then TempVars.Add "IsBeta", True Else TempVars.Add "IsBeta", False End If If Not OpenMyConnection Then MsgBox "Connection to SQL Server failed, please verify your user name and/or password", vbInformation, "Login Failed" Exit Sub End If RelinkAllTablesADOX End Sub
Cambiar entre bases de datos beta y de producción
Al incluir el nombre «Beta» en el nombre del proyecto, el código apuntará a la base de datos Beta en lugar de a la de producción. Explicaré más sobre esto más adelante en la serie.
OpenMyConnection
Observe el uso de OpenMyConnection, que uso en mis métodos Easy ADODB. Aquí está la definición de la función:
Public Function OpenMyConnection() As Boolean
On Error GoTo OpenMyConnection_Error
10 If con.State = adStateOpen Then
20 con.Close
30 End If
40 con.ConnectionString = conConnection & "User ID =" & TempVars!UserID & ";Password=" & TempVars!Password
50 con.Open
60 If Not con.State = adStateOpen Then
70 OpenMyConnection = False
80 Else
90 OpenMyConnection = True
100 End If
On Error GoTo 0
Exit Function
OpenMyConnection_Error:
MsgBox "Error " & Err.Number & " Line " & Erl & " (" & Err.Description & ") in procedure OpenMyConnection of Module mdlConnect"
End Function
Procedimiento RelinkAllTablesADOX
Este procedimiento es donde se disparan los fuegos artificiales:
Function RelinkAllTablesADOX() As Boolean
Dim cat As Object
Dim tbl As Object
Dim fLink As Boolean
Dim strSQL As String
Dim strTableName As String
Dim strField As String
Dim strDriver As String
Dim strLocalTableName As String
Dim rs As ADODB.Recordset
Dim strCatalog As String
Const conCatalog As String = "DataBE"
Const conServer As String = "ServerName"
On Error GoTo HandleErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
strDriver = "SQL Server"
For Each tbl In cat.Tables
With tbl
' Delete any linked tables
If .Type = "PASS-THROUGH" Then
CurrentDb.TableDefs.Delete .Name
End If
End With
Next tbl
30 strSQL = "Select * from tblTablePermissions Where DontLink = 0"
<a href="https://accessexperts.com/blog/2011/01/21/easy-adodb-recordsets-and-commands-in-access/" target="_blank" rel="noopener noreferrer">OpenMyRecordset</a> rs, strSQL
If TempVars!IsBeta Then
strCatalog = conCatalog & "Beta"
Else
strCatalog = conCatalog
End If
With rs
Do While .EOF = False
strTableName = !Table_Name
35 If IsNull(!Access_Name) Then
strLocalTableName = strTableName
Else
strLocalTableName = !Access_Name
End If
40 fLink = AttachDSNLessTable(strLocalTableName, strTableName, conServer, strCatalog, strDriver, _
TempVars!UserID, TempVars!Password)
50 'Is it a view? If so check if index needed
If Left(strTableName, 2) = "vw" And fLink Then
strField = Nz(DLookup("KeyField", "tblLinkViews", "[View] = '" & strTableName & "'"), "")
If Not strField = "" Then
strSQL = "Create Index IX_" & strLocalTableName & " On " & strLocalTableName & " (" & strField & ") WITH PRIMARY"
CurrentDb.Execute strSQL
End If
End If
.MoveNext
Loop
End With
RelinkAllTablesADOX = fLink
ExitHere:
Set cat = Nothing
Exit Function
HandleErr:
RelinkAllTablesADOX = False
If Not fLink Then
'MsgBox "Linking failed, please contact tech support.", vbInformation, "Error Linking Table"
Resume ExitHere
End If
MsgBox _
Prompt:=Err & ": " & Err.Description, _ Title:="Error in RelinkAllTablesADOX"
Resume ExitHere
End Function
Solo enlazar tablas donde DontLink = 0
Observe que en la línea 30 anterior solo vinculamos tablas que tienen DontLink configurado t Falso. Quizás se pregunte por qué nos molestamos incluso en colocar una tabla en tblTablePermissions cuando no se usa en Access como tabla vinculada. Recuerde en mi primera publicación que lo usamos para configurar la seguridad de todas las tablas, y si alguna vez es necesario usar ADODB e ir directamente a la tabla en SQL Server a través de nuestro código de access o consulta de paso, entonces necesitamos esa tabla listada en tblTablePermissions.
¿View necesita índice?
En la línea 50 anterior, el código verifica si la tabla que se está vinculando es una vista y, de ser así, crea el índice en Access utilizando una tabla local llamada tblLinkViews. Probablemente podría eliminar este concepto en su código y simplemente agregar otra columna a tblTablePermissions llamada ViewIndex, luego simplemente use! ViewIndex en el código anterior para leer la sintaxis SQL en strSQL.
Cambiar el nombre de la tabla del servidor SQL?
Observe que en la línea 35 anterior, el sistema usará un nombre diferente para la tabla de Access si así se especifica. Esta técnica puede ser útil cuando se trata de una aplicación heredada y desea utilizar nombres en SQL Server que se adapten mejor a usted, pero no se pueden usar en Access debido a un código heredado.
AttachDSNLess Table Code
El procedimiento anterior llama a este sub para hacer realmente la vinculación:
'Note: Code adapted from <a href="http://support.microsoft.com/kb/892490">http://support.microsoft.com/kb/892490</a> Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, strDriver As String, _ Optional stUserName As String, Optional stPassword As String) As Boolean 10 On Error GoTo AttachDSNLessTable_Err Dim td As TableDef Dim stConnect As String 20 If stLocalTableName = "" Then 30 stLocalTableName = stRemoteTableName 40 End If 50 Application.Echo True, "Linking table " & stLocalTableName 'Stop '//WARNING: This will save the username and the password with the linked table information. 60 stConnect = "ODBC;DRIVER=" & strDriver & ";SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUserName & ";PWD=" & stPassword 70 Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect) 80 CurrentDb.TableDefs.Append td 90 AttachDSNLessTable = True 100 Exit Function AttachDSNLessTable_Err: 110 If Err.Number = 3265 Or Err.Number = 3011 Then 'Table does not exist, continue anyway 120 Resume Next 130 End If 140 AttachDSNLessTable = False 150 Stop 160 MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description End Function
En mi próxima publicación, hablaré sobre cómo destruir los enlaces cuando salgas de la aplicación.
Deja tu comentario