help with Re-linking tables to SQL server

[javelin]

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.TableDefs
With tdf
' Delete only SQL Server tables.
If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
Not tdf.Name = "tblTableNames" Then

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

td2.SourceTableName = strTable
' Append to the database's TableDefs collection.
db.TableDefs.Append 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

[stejol377]

javelin,
Try adding the password to your connection string... I.E.:

td2.Connect = "ODBC;DSN=MyDSN;" & _
"Description=My Target Server;" & _
"UID=MyUserID;PWD=password;APP=Microsoft Office 2003;" &
_
"WSID=MyWorkStationID;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.TableDefs
With tdf
' Delete only SQL Server tables.
If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
Not tdf.Name = "tblTableNames" Then

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

td2.SourceTableName = strTable
' Append to the database's TableDefs collection.
db.TableDefs.Append 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

[Logipro]

Hi Jevelin,

You can try my tool :
http://www.logicielappui.com/tips/Ac...ginMDB_SQL.zip
Best regards

Robert Simard
Logipro
http://www.logicielappui.com/tips
"javelin" <go*************@spamgourmet.coma écrit dans le message de news:
11**********************@h54g2000cwb.googlegroups. 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.TableDefs
With tdf
' Delete only SQL Server tables.
If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
Not tdf.Name = "tblTableNames" Then

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

td2.SourceTableName = strTable
' Append to the database's TableDefs collection.
db.TableDefs.Append 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

[javelin]

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;" & _
"Description=My Target Server;" & _
"UID=MyUserID;PWD=password;APP=Microsoft Office 2003;" &
_
"WSID=MyWorkStationID;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.TableDefs
With tdf
' Delete only SQL Server tables.
If (.Attributes And dbAttachedODBC) = dbAttachedODBC And
Not tdf.Name = "tblTableNames" Then

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

td2.SourceTableName = strTable
' Append to the database's TableDefs collection.
db.TableDefs.Append 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