entering password for external data

[ykfc]

I am developing an application that accesses SQL server data via ODBC. I set up linked tables and run codes and the codes are supposed to run unattended.

I am confused as to what determine whether a password is required to be entered when the codes are run. If there a place where we specify it to remember the password? Thanks.

 

[VBslammer]

Normally you would have to type the password initially when you create the link, and there is a checkbox for "Save Password" on the dialog. You can view the Connect string in the MSysObjects table (Hidden System table) but you can't modify it. The Connect string should include these arguments somewhere in the string (SQL Server table):

[tt];UID=sa;PWD=yourPassword;[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ykfc]

Thank for answering. But the problem is with "normally". Normally I received the "save password" prompt when creating the link but sometimes it did not come up. Have you encountered this? That is one confusion.

The second confusion is when I import the linked table (which is simpler) from another MS-Access database, I am not really so sure if the system is going to ask for a password when my codes are running. I can test it to run a few times and feel comfortable. But if there a way to tell if it will behave the same (not asking a password) when running again. (You see, the codes will need to run many times in a day in unattended mode).

 

[VBslammer]

You can parse the table's Connect property with VBA to ensure that your valid User ID and Password are there. To MS Access, the "Connect" property is actually the "Link" to the linked table, so it has to be right before you can open the table.

You can use a function to verify that your User ID and Password are set in the table's Connect property before updating the table. I wouldn't call the function too often, maybe once at application startup:

Public Function VerifyLinkedODBCTableConnect(ByVal odbcTableName As String, _
                                            ByVal UserID As String, _
                                            ByVal password As String) As Boolean
On Error GoTo ErrHandler
  Dim strConn() As String
  Dim idxCurrent As Integer
  Dim uidOK As Boolean
  Dim pwdOK As Boolean
  Dim tdf As DAO.TableDef
  Dim db As DAO.Database
  
  Const PROP1 = "UID="
  Const PROP2 = "PWD="
  
  Set db = CurrentDb()
  Set tdf = db.TableDefs(odbcTableName)
  
  strConn = Split(tdf.Connect, ";")
  
  For idxCurrent = 0 To UBound(strConn)
    If PropertyContains(PROP1, strConn(idxCurrent)) Then
      If Not PropertyEquals(PROP1, strConn(idxCurrent), UserID) Then
        strConn(idxCurrent) = PROP1 & UserID
      End If
      uidOK = True
    ElseIf PropertyContains(PROP2, strConn(idxCurrent)) Then
      If Not PropertyEquals(PROP2, strConn(idxCurrent), password) Then
        strConn(idxCurrent) = PROP2 & password
      End If
      pwdOK = True
    End If
  Next idxCurrent
  
  tdf.Connect = Join(strConn, ";")
  
  If Not uidOK Then
    tdf.Connect = tdf.Connect & ";" & PROP1 & UserID
  End If
  If Not pwdOK Then
    tdf.Connect = tdf.Connect & ";" & PROP2 & password
  End If
  
  tdf.RefreshLink
  
  VerifyLinkedODBCTableConnect = True
  
ExitHere:
  On Error Resume Next
  Erase strConn
  Set tdf = Nothing
  Set db = Nothing
  Exit Function
ErrHandler:
  Debug.Print Err, Err.Description
  Resume ExitHere
End Function

[green]'@------------------ Helper Functions -------------------@[/green]

Private Function PropertyContains(ByVal strProp As String, strVal As String) As Boolean
  If InStr(1, strVal, strProp, vbTextCompare) > 0 Then
    PropertyContains = True
  End If
End Function

[green]'@-------------------------------------------------------@[/green]

Private Function PropertyEquals(ByVal strProp As String, strVal As String, _
                                ByVal strCompare As String) As Boolean
  If Right(strVal, Len(strVal) - Len(strProp)) = strCompare Then
    PropertyEquals = True
  End If
End Function

Sample Call:

If VerifyLinkedODBCTableConnect("dbo_Customers", "SysAdmin", "MyPassword") = False Then
  MsgBox "Contact your administrator", vbCritical, "Linker Failure"
End If

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[ykfc]

1) first my mistakes actually. The save password checkbox did come up when I setup the linked table.
2) second, yes, I built a similar piece of codes to examine the connection properties of the table based on your codes and I find it a easy way to ensure that the user/password names are (or are not) in any specific tables. That way I am more confident to know the application will run without prompting for a password. Thanks VBslammer for the effort.