vba connection strings to teradata tables at database open

[jrh71]

Hello all, I am a new member to this site, and although I have used many previous versions of Access and have done VBA programming in the past, it has been years. I have been tasked with creating a database that links to Teradata tables through ODBC. There is a universal ID that we can use for multiple users to access the tables when the database opens, however they have to type in the username and password each time. I am trying to figure out how to embed the connection string to the tables in VBA so that the user does not have to enter the credentials each time. Any help would be appreciated. Thanks!

 

[AccessMSSQL]

Here's what I use for connection to SQL Server, perhaps this will work for you:

This is for a cloud database

Public Sub openConnection(p_conn As ADODB.Connection)
 
    Dim l_sqldbname As String
 
    On Error GoTo connect_error
 
Dim str As String
Dim DatabaseName As String
Dim ServerName As String
Dim uid As String
Dim pwd As String
DatabaseName = "YourDBName"
ServerName = "YourServer"
 str = "Driver={SQL Server Native Client 10.0};"
 str = str & " Server=" & ServerName & ";"
 str = str & " Database=" & DatabaseName & ";"
 str = str & " UID=UserID;"
 str = str & " PWD=Password;Encrypt=Yes"
 
    p_conn.ConnectionString = str
    p_conn.Open
 
    Exit Sub
 
connect_error:
 
    MsgBox Err.Number & " " & Err.Description
 
End Sub

Here's a link to teradata connection strings:
http://www.connectionstrings.com/teradata

Also, if you are trying to link the tables and keep the password in the table, when you link through ODBC you have to check the save password box. See attached screenshot.

 

[jrh71]

Thank you very much for the information, I appreciate the assistance.