help with Re-linking tables to SQL server

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • javelin
    #1

    help with Re-linking tables to SQL server

    I have this code written that lets me relink my SQL server links to a
    specific SQL server (via a different DSN). The problem I have is that I
    have to enter the password when I run it. Can any one show me how to
    code the password in so the user doesn't have to deal wtih that?
    Thanks. Here's the code:

    ---- BEGIN CODE

    Public Sub ReConnectTables ()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim td2 As DAO.TableDef
    Dim strTable As String
    Dim strMsg As String

    On Error GoTo lblErr
    strMsg = "Tables were relinked successfully."

    ' Get rid of any old links.
    'Call DeleteLinks

    ' Create a recordset to obtain server object names.
    Set db = CurrentDb
    ' Walk through list of tables and create the links.
    For Each tdf In CurrentDb.Table Defs
    With tdf
    ' Delete only SQL Server tables.
    If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
    Not tdf.Name = "tblTableNa mes" Then

    DoCmd.Rename "bak_" & tdf.Name, acTable, tdf.Name
    strTable = tdf.Name '"dbo." ' rst!SQLTable
    ' Create a new TableDef object.
    Set td2 = db.CreateTableD ef(strTable)
    ' Set the Connect property to establish the link.
    td2.Connect = "ODBC;DSN=MyDSN ;" & _
    "Descriptio n=My Target Server;" & _
    "UID=MyUserID;A PP=Microsoft Office 2003;" &
    _
    "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
    _
    "TABLE=dbo. " & strTable

    td2.SourceTable Name = strTable
    ' Append to the database's TableDefs collection.
    db.TableDefs.Ap pend td2
    'DELETE old table
    DBEngine(0)(0). Execute "DROP TABLE [" & "bak_" &
    tdf.Name & "]"

    End If
    End With
    Next tdf

    lblExit:
    Set tdf = Nothing
    Set db = Nothing
    MsgBox strMsg, , "Link SQL Tables"
    Exit Sub

    lblErr:
    Select Case Err
    Case Else
    ErrMsgFunction( )
    Resume lblExit
    End Select
    Resume
    End Sub

    ---- END CODE

    Tags: None
    • stejol377
      #2
      Re: help with Re-linking tables to SQL server

      javelin,
      Try adding the password to your connection string... I.E.:
      td2.Connect = "ODBC;DSN=MyDSN ;" & _
      "Descriptio n=My Target Server;" & _
      "UID=MyUserID;P WD=password;APP =Microsoft Office 2003;" &
      _
      "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
      _
      "TABLE=dbo. " & strTable
      Replace the word password with the appropriate values.

      Hope this helps

      javelin wrote:
      I have this code written that lets me relink my SQL server links to a
      specific SQL server (via a different DSN). The problem I have is that I
      have to enter the password when I run it. Can any one show me how to
      code the password in so the user doesn't have to deal wtih that?
      Thanks. Here's the code:
      >
      ---- BEGIN CODE
      >
      Public Sub ReConnectTables ()
      Dim db As DAO.Database
      Dim tdf As DAO.TableDef
      Dim td2 As DAO.TableDef
      Dim strTable As String
      Dim strMsg As String
      >
      On Error GoTo lblErr
      strMsg = "Tables were relinked successfully."
      >
      ' Get rid of any old links.
      'Call DeleteLinks
      >
      ' Create a recordset to obtain server object names.
      Set db = CurrentDb
      ' Walk through list of tables and create the links.
      For Each tdf In CurrentDb.Table Defs
      With tdf
      ' Delete only SQL Server tables.
      If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
      Not tdf.Name = "tblTableNa mes" Then
      >
      DoCmd.Rename "bak_" & tdf.Name, acTable, tdf.Name
      strTable = tdf.Name '"dbo." ' rst!SQLTable
      ' Create a new TableDef object.
      Set td2 = db.CreateTableD ef(strTable)
      ' Set the Connect property to establish the link.
      td2.Connect = "ODBC;DSN=MyDSN ;" & _
      "Descriptio n=My Target Server;" & _
      "UID=MyUserID;A PP=Microsoft Office 2003;" &
      _
      "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
      _
      "TABLE=dbo. " & strTable
      >
      td2.SourceTable Name = strTable
      ' Append to the database's TableDefs collection.
      db.TableDefs.Ap pend td2
      'DELETE old table
      DBEngine(0)(0). Execute "DROP TABLE [" & "bak_" &
      tdf.Name & "]"
      >
      End If
      End With
      Next tdf
      >
      lblExit:
      Set tdf = Nothing
      Set db = Nothing
      MsgBox strMsg, , "Link SQL Tables"
      Exit Sub
      >
      lblErr:
      Select Case Err
      Case Else
      ErrMsgFunction( )
      Resume lblExit
      End Select
      Resume
      End Sub
      >
      ---- END CODE

        Comment

        • Logipro
          #3
          Re: help with Re-linking tables to SQL server

          Hi Jevelin,

          You can try my tool :
          http://www.logicielappui.com/tips/AccXP_LoginMDB_SQL.zip



          Best regards

          Robert Simard
          Logipro
          http://www.logicielappui.com/tips



          "javelin" <google.1.jvmai l@spamgourmet.c oma écrit dans le message de news:
          1164145161.6014 87.254610@h54g2 00...legr oups.com...
          >I have this code written that lets me relink my SQL server links to a
          specific SQL server (via a different DSN). The problem I have is that I
          have to enter the password when I run it. Can any one show me how to
          code the password in so the user doesn't have to deal wtih that?
          Thanks. Here's the code:
          >
          ---- BEGIN CODE
          >
          Public Sub ReConnectTables ()
          Dim db As DAO.Database
          Dim tdf As DAO.TableDef
          Dim td2 As DAO.TableDef
          Dim strTable As String
          Dim strMsg As String
          >
          On Error GoTo lblErr
          strMsg = "Tables were relinked successfully."
          >
          ' Get rid of any old links.
          'Call DeleteLinks
          >
          ' Create a recordset to obtain server object names.
          Set db = CurrentDb
          ' Walk through list of tables and create the links.
          For Each tdf In CurrentDb.Table Defs
          With tdf
          ' Delete only SQL Server tables.
          If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
          Not tdf.Name = "tblTableNa mes" Then
          >
          DoCmd.Rename "bak_" & tdf.Name, acTable, tdf.Name
          strTable = tdf.Name '"dbo." ' rst!SQLTable
          ' Create a new TableDef object.
          Set td2 = db.CreateTableD ef(strTable)
          ' Set the Connect property to establish the link.
          td2.Connect = "ODBC;DSN=MyDSN ;" & _
          "Descriptio n=My Target Server;" & _
          "UID=MyUserID;A PP=Microsoft Office 2003;" &
          _
          "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
          _
          "TABLE=dbo. " & strTable
          >
          td2.SourceTable Name = strTable
          ' Append to the database's TableDefs collection.
          db.TableDefs.Ap pend td2
          'DELETE old table
          DBEngine(0)(0). Execute "DROP TABLE [" & "bak_" &
          tdf.Name & "]"
          >
          End If
          End With
          Next tdf
          >
          lblExit:
          Set tdf = Nothing
          Set db = Nothing
          MsgBox strMsg, , "Link SQL Tables"
          Exit Sub
          >
          lblErr:
          Select Case Err
          Case Else
          ErrMsgFunction( )
          Resume lblExit
          End Select
          Resume
          End Sub
          >
          ---- END CODE
          >

            Comment

            • javelin
              #4
              Re: help with Re-linking tables to SQL server

              That worked. It didn't work the first 200 tries, for some reason.

              Thanks for the quick (and accurate) reply.

              stejol377 wrote:
              javelin,
              Try adding the password to your connection string... I.E.:
              >
              td2.Connect = "ODBC;DSN=MyDSN ;" & _
              "Descriptio n=My Target Server;" & _
              "UID=MyUserID;P WD=password;APP =Microsoft Office 2003;" &
              _
              "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
              _
              "TABLE=dbo. " & strTable
              >
              Replace the word password with the appropriate values.
              >
              Hope this helps
              >
              javelin wrote:
              I have this code written that lets me relink my SQL server links to a
              specific SQL server (via a different DSN). The problem I have is that I
              have to enter the password when I run it. Can any one show me how to
              code the password in so the user doesn't have to deal wtih that?
              Thanks. Here's the code:

              ---- BEGIN CODE

              Public Sub ReConnectTables ()
              Dim db As DAO.Database
              Dim tdf As DAO.TableDef
              Dim td2 As DAO.TableDef
              Dim strTable As String
              Dim strMsg As String

              On Error GoTo lblErr
              strMsg = "Tables were relinked successfully."

              ' Get rid of any old links.
              'Call DeleteLinks

              ' Create a recordset to obtain server object names.
              Set db = CurrentDb
              ' Walk through list of tables and create the links.
              For Each tdf In CurrentDb.Table Defs
              With tdf
              ' Delete only SQL Server tables.
              If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
              Not tdf.Name = "tblTableNa mes" Then

              DoCmd.Rename "bak_" & tdf.Name, acTable, tdf.Name
              strTable = tdf.Name '"dbo." ' rst!SQLTable
              ' Create a new TableDef object.
              Set td2 = db.CreateTableD ef(strTable)
              ' Set the Connect property to establish the link.
              td2.Connect = "ODBC;DSN=MyDSN ;" & _
              "Descriptio n=My Target Server;" & _
              "UID=MyUserID;A PP=Microsoft Office 2003;" &
              _
              "WSID=MyWorkSta tionID;DATABASE =TargetDB;" &
              _
              "TABLE=dbo. " & strTable

              td2.SourceTable Name = strTable
              ' Append to the database's TableDefs collection.
              db.TableDefs.Ap pend td2
              'DELETE old table
              DBEngine(0)(0). Execute "DROP TABLE [" & "bak_" &
              tdf.Name & "]"

              End If
              End With
              Next tdf

              lblExit:
              Set tdf = Nothing
              Set db = Nothing
              MsgBox strMsg, , "Link SQL Tables"
              Exit Sub

              lblErr:
              Select Case Err
              Case Else
              ErrMsgFunction( )
              Resume lblExit
              End Select
              Resume
              End Sub

              ---- END CODE

                Comment

                Previous template Next
                Working...