By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,145 Members | 1,472 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,145 IT Pros & Developers. It's quick & easy.

help with Re-linking tables to SQL server

P: n/a
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

Nov 21 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 22 '06 #2

P: n/a
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

Nov 22 '06 #3

P: n/a
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
Nov 22 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.