Hi

I need to be able to vary the connection string in a query depending on who is logged in (I'm using SQL Server 2005 back end to my access client).

I have a function getConnectionString which generates teh connection string depending on the logged in user and the required database, but I'm not sure how to code this into the query. I tried setting the connection string property to 'getConnectionString("myDatabase")' but when you try to open the query it says 'not a valid file name'

the query code is as such:

[code]SELECT tblStaff.ID, tblStaff.UserID
FROM tblStaff IN '' [getConnectionString("MIDSQLKB")]
ORDER BY tblStaff.UserID;

Any ideas what I'm doing wrong here?

thanks

303

SELECT tblStaff.ID, tblStaff.UserID
FROM tblStaff IN '' [getConnectionString("MIDSQLKB")]
ORDER BY tblStaff.UserID;

Change to

Dim StrConn As string
StrConn = getConnectionString("MIDSQLKB")]

SELECT tblStaff.ID, tblStaff.UserID FROM tblStaff IN '' [" & StrConn & "] ORDER BY tblStaff.UserID;

David

SELECT tblStaff.ID, tblStaff.UserID
FROM tblStaff IN '' [getConnectionString("MIDSQLKB")]
ORDER BY tblStaff.UserID;

Change to

Dim StrConn As string
StrConn = getConnectionString("MIDSQLKB")]

SELECT tblStaff.ID, tblStaff.UserID FROM tblStaff IN '' [" & StrConn & "] ORDER BY tblStaff.UserID;

David

When I try to save the query it says 'Invalid SQL statement; expected 'DELTETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'.

Note I'm trying to create an MSAccess query object here not just get a recordset in my form/module code

If you are actually trying to create a query from the sql then you need to use the QueryDefs collection to identify the query and then reset the SQL for the query. There are examples of this on the forum.

David