Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Server Link (2003)

    When you link a SQL Server Database through the dialogs in Access one of the options is to store the connection password and userid so that you are not prompted each time you open the Application for the first time. Is there any way to do this link through code and have it save the userid and password. I have code that will allow me to link to SQL Server dynamically but I cannot see where to embed the userid and password. I realize there is a security risk doing this but in this application there is no security concern.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server Link (2003)

    What is the code you have now?

  3. #3
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Link (2003)

    I got the base of this from a book Access Cookbook from O'Reilly.

    Private Sub btnConnect_Click()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strServer As String
    Dim strDB As String
    Dim strTable As String
    Dim strConnect As String
    Dim strMsg As String

    On Error GoTo HandleErr

    ' Build base authentication strings
    ' SQL server login
    strConnect = "ODBC;Driver={SQL Server};UID=" _
    & Me.txtUser & ";PWD=" & Me.txtPwd & ";"

    ' Get rid of old links, if any
    Call DeleteLinks

    ' Create recordset to obtain server, database and table names
    Set db = CurrentDb
    Set rst = db.OpenRecordset("tblSQLTables", dbOpenSnapshot)
    If rst.EOF Then
    strMsg = "There are no tables listed in tblSQLTables."
    GoTo ExitHere
    End If

    ' Walk through the recordset and create the links
    Do Until rst.EOF
    strTable = rst!SQLTable
    ' Create a new TableDef object
    Set tdf = db.CreateTableDef(strTable)
    ' Set the Connect property to establish the link
    tdf.Connect = strConnect & _
    "Server=" & txtServer & _
    ";Database=" & txtDatabase & ";"
    tdf.SourceTableName = strTable
    ' Append to the database's TableDefs collection
    db.TableDefs.Append tdf
    rst.MoveNext
    Loop

    strMsg = "Tables linked successfully."

    rst.Close
    Set rst = Nothing
    Set tdf = Nothing
    Set db = Nothing

    ExitHere:
    MsgBox strMsg, , "Link SQL Tables"
    Exit Sub

    HandleErr:
    Select Case Err
    Case Else
    strMsg = Err & ": " & Err.Description
    Resume ExitHere
    End Select
    End Sub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Server Link (2003)

    Try changing

    Set tdf = db.CreateTableDef(strTable)

    to

    Set tdf = db.CreateTableDef(strTable, dbAttachSavePWD)

  5. #5
    Star Lounger
    Join Date
    Jun 2002
    Location
    Ontario
    Posts
    67
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Server Link (2003)

    Hans,

    As always your solution worked!

    Thanks,
    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •