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

creating tabledef connection to login to ODBC dbase

P: n/a
HI, anyone know why this code doesnt work? I am trying to set up the
connect property of a tabledef so that it uses a given login and
password. however once I run it I still need to login when I open the
table

Function SetConnections()
Dim strTableName, strSourceTableName As String
Dim objTableDef As TableDef
Dim strConnect As String '= "Set your connection string here"
Dim rs As Recordset
Dim li_size As Integer, ls_user As String, ls_host As String
Dim QuerydefTemp As querydef

'On Error GoTo Trapper
ls_dsn = "VAR"
ls_database = "uvarprod"
ls_user = GetLogin
ls_pwd = getPassword(CVarDB, ls_user)
ls_uid = ls_user
strConnect = "ODBC;DSN=" & ls_dsn &
";NA=varsybhostprod.opf.swissbank.com,9996;DB= " & _
ls_database & ";UID=" & ls_uid & ";PWD=" & ls_pwd '&
";TABLE=dbo.currency"

'Reset the connection property for all pass through queries
'For Each QuerydefTemp In CurrentDb.QueryDefs
'If querydef.Connect <"" Then
'querydef.Connect = "ODBC;" & strConnect
'End If
'Next

'Reset connections for linked tables
For Each TableDef In CurrentDb.TableDefs
If TableDef.Connect <"" And TableDef.Name = "dbo_currency" Then
'Get the table name and source table name
strTableName = TableDef.Name
strSourceTableName = TableDef.SourceTableName

'Recreate the tabledef
Set objTableDef = New TableDef
With objTableDef
..Name = strTableName & "Temp"
..SourceTableName = strSourceTableName
..Connect = strConnect
End With
CurrentDb.TableDefs.append objTableDef

'If the new tabledef was successfully created, delete the old TableDef
CurrentDb.TableDefs.Delete strTableName

'Change the name of the new tabledef to that of the old TableDef
CurrentDb.TableDefs(strTableName & "Temp").Name = strTableName

End If
Next
Set objTableDef = Nothing
Exit Function
Trapper:
MsgBox "Error setting connections to SQL Server Database"
Exit Function
End Function

Jul 21 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.