Need help with connection string for ADO connections in Access.

[Sorwen]

Ok up till now I've been able get around it, but now I can't. I don't understand connection strings for use with ADO. We have users that connect to a remote AS400 Server. We use an ODBC connection to it for access. When it first connects they have to type in a shared username and password. My problem in some instances I need to be able to send that username and password rather than allow them to type it in. In Excel I just recorded a macro that made the connection and I can manipulated it enough to get buy, but in Access I don't have that option.

I've tried various code found here and links to other code, but I simply can't guess enough to get it working. Also a lot of it is using code to connect to a normal database on another drive or network drive which will be great at some other time, but doesn't help me.

Examples:

"Provider=IBMDA400;" & _
           "Data source=myAS400;" & _
           "User Id=myUsername;" & _
           "Password=myPassword"

"Provider=IBMDA400.DataSource.1;" & _
"Data Source=AS400;" & _
"User ID=UserId;" & _
"Password=Password;" & _
"Persist Security Info=True;" & _
"Catalog Library List=Lib1,Lib2,Lib3"

Excels Connection string

With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=(DSN Name here);", _
        Destination:=Range("A1"))
        .CommandText = strSQL 'sql string I want to send.
        .Name = "Query from (Here again)"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With

Could someone help or point in the direction of help (a book or something)?

 

[warthog72]

If you are using an ODBC, when you link the table into Access, in Options, you can select to save the password. Its not the best option, but if you are already using that connection method.

Steps -
1. Choose Link Table from Access
2. Choose ODBC from type
3. Select your Data Source
4. Type in your password
5. When you choose your table, click on the "Save Password" option

Hope that helps.

 

[Sorwen]

Than doesn't seem to actually save the password for me or the others here. It still prompts with the first connection. I did wonder if I misunderstood what that save password was for. I guess it is just something wrong with our setup/connection or something.

 

[jadams0173]

Here are some I found:

 IBM Client Access OLE DB provider:

"PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USER ID=myUserName;PASSWORD=myPwd" 
Where MY_SYSTEM_NAME is the name given to the system connection in OperationsNavigator

 IBM Client Access OLE DB provider:

"PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USER ID=myUserName;PASSWORD=myPwd;DEFAULT COLLECTION=MY_LIBRARY;" 
Where MY_SYSTEM_NAME is the name given to the System Connection, and MY_LIBRARY is the name given to the library in iSeries Navigator.

 IBM Client Access ODBC driver:

"Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUserName;Pwd=myPwd" 
 Exchange

You can try these websites

http://www.connectionstrings.com

http://www.carlprothman.net/Default.aspx?tabid=81

 

[Sorwen]

Thanks for the help. I seem to have gotten it. Don't know how well as My test only pulled in the first column/row from a test table, but that was more than I had before.

"ODBC;DSN=<ODBC System DSN Here>;" & _
"User ID=""<UserNameHere>"";Password=""<PasswordHere>"";" & _
"Data Source=<ODBC System DNS Here>;"

Of course replace the <XXXXX> with the correct information. Here is what I used to test. I didn't do anything with the output just did a break so I could check the string had the data.

Function ConnectionTest()
    Dim Site As String

    Dim con As Connection
    Dim rsCommandDatabase As Recordset
    Dim strSQL As String
    Dim dbconnstring As String
  
    Set con = CurrentProject.Connection
    Set rsCommandDatabase = New Recordset
    
    strSQL = ""
    strSQL = strSQL & "SELECT *"
    strSQL = strSQL & " FROM DataFile"
      
    dbconnstring = "ODBC;DSN=<DSN Name>;" & _
                    "User ID=""<UserName>"";Password=""<Password>"";" & _
                    "Data Source=<DSN Name>;"
    
    rsCommandDatabase.Open strSQL, dbconnstring, adOpenDynamic, adLockOptimistic
    Site = rsCommandDatabase!Site
    rsCommandDatabase.Close
End Function