Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Passing username/password to ODBC DSN created on the fly

    Good day all.

    I recently set up one of my Databases (access 2007) to create an ODBC connection to SQL Server 2000 on the fly, using the aid of these two sites/articles:

    How to create a DSN-less connection to SQL Server for linked tables in Access


    SQL Server 2000, 7.0 Connection String Samples - ConnectionStrings.com

    and all was well with the world, but we've had a slight change in that we no longer want to add the user's usernames to SQL Server 2000, and have instead opted to use a

    username/password for that application/Database alone. I've created the username and password in the SQL Server, tested it and it works, but goign about making the changes for the

    application itself is not so good.

    The code I had is as follows:

    CreateDSNConnection("MySQL", "MySQLServer", "MyDB", "MyUserName", "MyPassword")

    which then calls this function:

    Function CreateDSNConnection(strDescription As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String) As Boolean
    On Error GoTo CreateDSNConnection_Err

    Dim stConnect As String

    If Len(stUsername) = 0 Then

    stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=Yes"
    Else

    stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase
    End If

    DBEngine.RegisterDatabase strDescription, "SQL Server", True, stConnect

    CreateDSNConnection = True
    Exit Function
    CreateDSNConnection_Err:

    CreateDSNConnection = False
    MsgBox "CreateDSNConnection encountered an unexpected error: " & Err.Description

    End Function

    This does in fact create the DSN on the fly but insists on usign the user's username in the DSN connection, so I figured I needed to add the username and password variables,

    changing the line to this:

    stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "User id=" & stUsername & vbCr & "PASSWORD=" & stPassword

    which now refuses to create the DSN, I suspect that my syntax is incorrect but it falls in line with the connection strings site above.

    Can anyone tell me where i'm going wrong? I've double checked the SQL server user and it has the correct rihts to teh Database, but I cannot get those two values to write to the

    DSN connection on the fly.

    Thanks in advance,
    Mitch.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Have a look at: Connection Strings
    Have a nice day!

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    Thanks for that Sinndho.

    So it looks like I should have UID and PWD instead of User_ID and password.
    I changed that line like so:

    stConnect = "Description=" & strDescription & vbCr & "SERVER=" & stServer & vbCr & "DATABASE=" & stDatabase & vbCr & "Trusted_Connection=NO" & vbCr & "Uid=" & stUsername & vbCr & "Pwd=" & stPassword

    I'm still getting Run time error 3146, ODBC - Call failed.

    Weird because without needing to pass the username and password values this code works fine for users who already have their profiles on the SQL server, but like I mentioned before this is somethign we waant to avoid for the mass rollout.

    Cheers,
    Mitch

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As far as I know each part of the connection string must be terminated by a semicolon (. Here's an example of a connection string I actually use:
    Code:
    c_strConnectionString = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;TRUSTED_CONNECTION=Yes;"
    TRUSTED_CONNECTION=Yes means that in this case the domain credentials of the user are used to validate the connection.
    Here's one using a user name and a password:
    Code:
    c_strConnectionString = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Scoreboard;UID=Sinndho;Pwd=********;"
    Last edited by Sinndho; 03-30-11 at 02:26. Reason: Username/password example added
    Have a nice day!

  5. #5
    Join Date
    Sep 2006
    Posts
    26
    Thanks again for your input Sinndho.

    I'll try again but if I still get no joy i'm goign to use the 'DSN'less' method.

    Cheers,
    Mitch.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Dec 2012
    Posts
    7
    Hi Sinndho,

    I am facing the same issue with DNS less ODBC connection.
    My code is as below:
    For Un-trusted Connection:
    stConnect = "ODBC;DRIVER={SQL Server};" & vbCr & "SERVER=" & stServer & vbCr & ";DATABASE=" & stDatabase & vbCr & ";Uid=" & stUsername & vbCr & ";Pwd=" & stPassword

    DBEngine.RegisterDatabase "myDSN", "SQL Server", True, stConnect

    Input to the code:
    CreateDSNConnection ("111.111.111.111,63314", "ABC_DB", "User1", "Passwd1")

    Error "Create DNS connection encountered an unexpected error:ODBC -- call Failed"

    Can you please let me know why I am getting this error though I am giving correct details? Thanks.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so check the actual values being set as part of the connection string
    either debug the stconnect or use a msgbox and examine each parameter and ensure its correct
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The RegisterDatabase method of the DBEngine object is not used to create a DSN-less connection to a database server. On the contrary, this methods allows the registration of a DSN into the Windows Registry.

    Moreover, it's arguments are not those of a connection string, see: RegisterDatabase Method*[Access 2007 Developer Reference]

    When you want to use a DSN-less connection, you give a value (the connection string) to the Connect property of a DAO.Tabledef or DAO.QueryDef object. e.g.
    - for creating an attached (linked) table:
    Code:
    Sub AttachedTable()
    
        Const c_ConnectionString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        
        Set dbs = CurrentDb
        Set tdf = dbs.CreateTableDef()
        tdf.Name = "Customers"
        tdf.SourceTableName = "Tbl_Customers"
        tdf.Connect = c_ConnectionString
        dbs.TableDefs.Append tdf
         Set tdf = Nothing
        Set dbs = Nothing
       
    End Sub
    - for executing a stored procedure:
    Code:
    Sub DeleteCustomer()
    
        Const c_ConnectionString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("")
        qdf.Connect = c_ConnectionString
        qdf.SQL = "Proc_Tbl_Customers_Delete @CustomerID=19"
        qdf.ReturnsRecords = False
        qdf.Execute
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Sub
    - for retrieving data from a Table or a View on the server in a RecordSet:
    Code:
    Function GetCustomersList() As DAO.Recordset
    
        Const c_ConnectionString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "SELECT * FROM Tbl_Customers WHERE Inactive = 0 ORDER BY Tbl_Customers.Customer_Name;"
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("")
        qdf.Connect = c_ConnectionString
        qdf.SQL = c_SQL
        Set GetCustomersList = qdf.OpenRecordset
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function
    - For retrieving data from a Table or a View on the server in an Access query:
    Code:
    Sub GetCustomersList()
    
        Const c_ConnectionString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "SELECT * FROM Tbl_Customers WHERE Inactive = 0 ORDER BY Tbl_Customers.Customer_Name;"
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("qry_Customers") ' A query object named "qry_Customers" is created in the current database.
        qdf.Connect = c_ConnectionString
        qdf.SQL = c_SQL
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Sub
    - For retrieving data from a Table or a View on the server in an array (Variant):
    Code:
    Function GetCustomersList() As Variant
    
        Const c_ConnectionString As String = "ODBC;DRIVER={SQL Server};SERVER=SOLIMAN;DATABASE=Sales;Trusted_Connection=Yes;"
        Const c_SQL As String = "SELECT * FROM Tbl_Customers WHERE Inactive = 0 ORDER BY Tbl_Customers.Customer_Name;"
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("")
        qdf.Connect = c_ConnectionString
        qdf.SQL = c_SQL
        GetCustomersList = qdf.OpenRecordset.GetRows(999)
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Function
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •