Getting linked tables to remember password?

[joebickley]

Hi, I have the following code to reconnect all of the linked tables to an SQL server. Only thing is it does not remember the password next time the DB is opened. Any ideas?

Public Function LinktoSQL() As Boolean
Dim cODBCPATH As String
cODBCPATH = "ODBC;DRIVER=SQL Server;SERVER=solihullweb;UID=sa;PWD=soldata;DATABASE=RCS_data"


Dim db As DAO.Database
Dim i As Integer
Dim tdfNew As DAO.TableDef
Dim rec As DAO.Recordset
Dim tdfLinked As DAO.TableDef
Dim strtablename As String

On Error GoTo LinktoSQL_Err

Set db = CurrentDb()
Set rec = db.OpenRecordset("tblLinkedTables")
rec.MoveLast
rec.MoveFirst

For i = 1 To rec.RecordCount
   strtablename = rec.Fields(1).Value
   Set tdfNew = db.CreateTableDef(strtablename)
   tdfNew.SourceTableName = rec.Fields(1).Value
   tdfNew.Connect = cODBCPATH 'Constant ODBC Conn. string
   db.TableDefs.Append tdfNew
   rec.MoveNext
Next i
LinktoSQL_Exit:
    Set rec = Nothing
    Exit Function
LinktoSQL_Err:
    Select Case Err.Number
        Case 3010 'In case the linked table is there
            db.TableDefs.Delete strtablename
            db.TableDefs.Append tdfNew
            Resume Next
        Case Else
            MsgBox Err.Number & Err.Description
            LinktoSQL = False
    End Select
End Function

Joe Bickley

 

[JoaoTL]

Hi, are you Using a MS Secure DB to Login or a function without MDW? Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access/eng/FAQE.htm

 

[joebickley]

im not using a MDW at the moment this is just a test app at the moment. I t is more for my convinience than to use in the actual app

 

[JoaoTL]

Hi again , try this solution.

After Update the User Name, you can Lookup the Password

Private Sub UserName AfterUpdate()
Dim varX as Variant

DLookup "[PASSWORD_FIELD]", "TABLE", "TEXT_BOX_NAME = [USER_NAME]&quot
PassW_TextBox = varX

End Sub

The password fills the Text Box. You can set a Field True/False for users confirm or ignore the password remeber next time it opens.

hope it hels

Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access