Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    London
    Posts
    13

    Unanswered: SQL server login

    Hi there,

    I have Access Database in which some tables are linked with SQL Server based database. When I want to access any of those linked tables I have to login into SQL server ("SQL Server Login" window). Is there any way to do that using VBA code, so I can run this procedure when I open Access Database?

    thanks for any help

    Amigos

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: SQL server login

    When you link the tables in click the "Save Password" button.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Oct 2003
    Location
    London
    Posts
    13

    Thumbs up Re: SQL server login

    OK, I just relinked all my tables, but this time I ticked "Save Password" and it works fine. I just thought that there is any VBA way to login to SQL server. Thanks for help.

    Amigos

  4. #4
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: SQL server login

    Originally posted by amigos
    OK, I just relinked all my tables, but this time I ticked "Save Password" and it works fine. I just thought that there is any VBA way to login to SQL server. Thanks for help.

    Amigos
    After you link and save the password there is a way to do it when you have to change password on existing tables. This function is partly out of the help, modified to do what I need to do. I still don't fully understand the functions, but it does work.


    Dim SQL As String
    Dim DB As database
    Dim RS As Recordset
    Dim RecCount As Integer
    Dim I As Integer
    Dim II As Integer
    Dim HoldTableName As String
    Dim CurrTableName() As String
    Dim ForeignName() As String

    Dim dbsCurrent As database
    Dim tdfLinked As TableDef

    ' Open a database to which a linked table can be
    ' appended.
    Set dbsCurrent = CurrentDb()
    SQL = "SELECT Name, ForeignName " & _
    "FROM MSysObjects " & _
    "WHERE (((Left([Connect],21))='DSN=<ODBCName>;'));"

    Set DB = CurrentDb()
    Set RS = DB.OpenRecordset(SQL)

    If RS.EOF = False Then
    RS.MoveLast
    RecCount = RS.RecordCount
    I = 1
    ReDim CurrTableName(RecCount) As String
    ReDim ForeignName(RecCount) As String

    RS.MoveFirst

    Do Until RS.EOF
    CurrTableName(I) = RS!name
    ForeignName(I) = RS!ForeignName
    Debug.Print CurrTableName(I) & " " & ForeignName(I)
    I = I + 1
    RS.MoveNext
    Loop
    RS.Close
    I = 1
    II = 1
    Do Until I = UBound(CurrTableName) + 1
    Debug.Print CurrTableName(I)
    DoCmd.DeleteObject acTable, CurrTableName(I)

    ' Create a linked table that points to a Microsoft
    ' SQL Server database.
    Set tdfLinked = dbsCurrent.CreateTableDef(CurrTableName(I))
    Let tdfLinked.Connect = "ODBC;DDSN=<ODBCName>;UID=<userid>;PWD=<pswd>"
    Let tdfLinked.SourceTableName = ForeignName(I)
    dbsCurrent.TableDefs.Append tdfLinked

    ' Display contents of linked table.
    Debug.Print _
    "Data from linked table connected to first source:"
    ' RefreshLinkOutput dbsCurrent

    ' Change connection information for linked table and
    ' refresh the connection in order to make the new data
    ' available.
    'tdfLinked.Connect = _
    "ODBC;DDSN=<ODBCName>"
    'tdfLinked.RefreshLink

    ' Display contents of linked table.
    Debug.Print _
    "Data from linked table connected to second source:"
    I = I + 1
    Loop
    End If


    I would practice this in a copy of your db before actually putting it in production. You will need to look in your MSysObjects to get the DSN string that you need to use. Good Luck.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    13

    Re: SQL server login

    uuu.. that's a lot of code for that job

    thank you very much I will try this later on my "DB for tests" as it will be usefull in the future

    Amigos

Posting Permissions

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