Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2009
    Posts
    7

    Unanswered: query connection string from a function

    Hi

    My access client has many query objects that return data from tables linked to a SQL server back end.

    They all work fine with my connection string hard coded into the query property

    ODBC;DSN=MIDSQL;Description=MID SQL Connection;UID=User123;PWD=Digital123;DATABASE=MID SQL;LANGUAGE=us_english

    The problem is I need the username/password to vary depending on the logged on user.

    I've set up a public function in my access client called 'getConnectionString' which returs the connection string dending on who opened the client. However I cant work out how to put this in the query. If I put 'getConnectionString()' in the connection string query property then try open the query it says 'cannot find file C:\Documents and Settings\Administrator\My Documents\getConnectionString.mdb'.

    I've used functions in queries before so i thought it would be possible, yet it seems to be looking for a file instead of running the function and using the returned string.

    Is what I'm trying to do possible? Is there another way?

    thanks in advance
    303

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Query

    Please post the SQL of your query.

    What is the purpose of the seperate connection strings - what are you trying to achieve here?

    You could modify the properties of the query and set the connection string dynamically but I guess there is a better option depending on what you are trying to do.
    Last edited by garethdart; 08-25-09 at 10:44. Reason: Second thoughts...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Aug 2009
    Posts
    7
    SELECT tblStaff.ID, tblStaff.UserID
    FROM tblStaff IN '' [getConnectionString()]
    ORDER BY tblStaff.UserID;

  4. #4
    Join Date
    Aug 2009
    Posts
    7
    Quote Originally Posted by garethdart
    Please post the SQL of your query.

    PS What is the purpose of the seperate connection strings - what are you trying to achieve here?
    Well I'm new to SQL server but I'm assuming I want each person who logs on to have a different logon to the database, and therefore have a different user name and password. So presumably I cant have the user name and password hard coded into the connection string!

    I could just have one logon for the database and have everyone using that, I just assumed that would be bad practice?

  5. #5
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    The result of a function in a query will not affect the properties of the query itself.

    You could write a function to change the connectionstring property as the user logs on or uses that query;

    Dim db as DAO.database
    Dim qd as DAO.querydef

    Set db = currentdb
    set qd = db.querydefs("yourqueryname")

    qd.connectionstring = getconnectionstring

    Set qd = nothing
    set db = nothing

    Prior to them using the query.

    But the important question is why are you trying to do this?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Which version of Access are you using please?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Aug 2009
    Posts
    7
    Quote Originally Posted by garethdart
    But the important question is why are you trying to do this?
    My understanding is:

    My MSAccess query objects do not function properly unless they include a connection string in the property

    The connection string requires a username and password

    I plan for the SQL database to have multiple users, each with a different username and password.

    So as far as I can tell I need a variable connection string in the query.

    Have i missed something? PS I'm using Access 2003 and SQL Server 2005

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Many of the members here will probably use their own custom user / security modules;

    Ahem!...I'm sure you have already searched the codebank for examples!!!

    Access 2003 supports user and group security (Dumped in 2007) which should suffice for what you are trying to acheive.

    I don't think you need to go down the road of dynamically changing connectionstrings at runtime if you just want seperate logons.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Aug 2009
    Posts
    7
    Quote Originally Posted by garethdart
    Many of the members here will probably use their own custom user / security modules;

    Ahem!...I'm sure you have already searched the codebank for examples!!!

    Access 2003 supports user and group security (Dumped in 2007) which should suffice for what you are trying to acheive.

    I don't think you need to go down the road of dynamically changing connectionstrings at runtime if you just want seperate logons.
    Well I havent looked for new code actually, I was kinda hoping I could just link up to the SQL tables with minimum change to my front end client!

    So if I set up user and group security I wouldnt need to hard code a username/password into my queries?

    Excuse my ignorance but I've been thrown in at the deep end with no SQL server experience and a complicated front end client I have inherited.

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you intend to use the user's name in the database, why don't you fetch it there, using the SYSTEM_USER function (suser_sname()) on the server?

    Code:
    DECLARE @sysuser nvchar(50)
    SET @sysuser = SYSTEM_USER
    SELECT 'The current user is: '+ @sysuser
    GO
    Have a nice day!

  11. #11
    Join Date
    Aug 2009
    Posts
    7
    Quote Originally Posted by garethdart
    The result of a function in a query will not affect the properties of the query itself.

    You could write a function to change the connectionstring property as the user logs on or uses that query;

    Dim db as DAO.database
    Dim qd as DAO.querydef

    Set db = currentdb
    set qd = db.querydefs("yourqueryname")

    qd.connectionstring = getconnectionstring

    Set qd = nothing
    set db = nothing

    Prior to them using the query.

    But the important question is why are you trying to do this?
    Hi, I've got around my probllem with different logins by just having everyone sharing the same logon/password. However I still have more than one database so I'm writing a script that goes through every query and changes the connection string to point at the new database. I'm trying your code but I get 'Method or data member not found' on the qd.connectionstring line
    Any idea why?

  12. #12
    Join Date
    Aug 2009
    Posts
    7
    I tried it with qd.Connection instead, but the code changes all my queries to a pass through query! Arg!

Posting Permissions

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