Query get connection string from a function

303factory

Registered User.
Local time
Today, 15:18
Joined
Oct 10, 2008
Messages
136
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
 
Last edited:
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
 

Users who are viewing this thread

Back
Top Bottom