ADP and T-SQL Problem

[Ecohouse]

I'm using Access 2002 and SQL 2000 on a stand alone computer and have
created an Access Project connecting to an existing SQL database.

All the tables and everything else in the SQL db is in the Access
project. My problem is that when I'm running functions my T-SQL
functions aren't working.

For instance if I have a line of code referring to the GetDate or
SUSER_SNAME functions in my module they don't return any values.

I tried creating test projects connecting to other dbs and I'm still
having the same problem.

So I was wondering what I have to do to get around this issue?

[Lyle Fairfield]

Could you give us examples of these "lines of code"?

[Ecohouse]

Here's an example:

strsql = "Select * From dbo.tblPlannedTasks Where [PlannedTaskID] = -1
AND PlannedTaskID IN " _
& "(SELECT dbo.tblPlannedTaskAssignees.PlannedTaskID FROM
dbo.tblPlannedTaskAssignees INNER JOIN " _
& "dbo.tblUsers ON dbo.tblPlannedTaskAssignees.UserID =
dbo.tblUsers.UserID " _
& "WHERE (dbo.tblUsers.LoginUserName = SUSER_SNAME()))"

The SUSER_SNAME function doesn't return a name.

[Lyle Fairfield]

I'm stumped.
Have you tested
SELECT SUSER_SNAME() AS Test
?

[Br]

Ecohouse wrote:

Here's an example:

strsql = "Select * From dbo.tblPlannedTasks Where [PlannedTaskID] = -1
AND PlannedTaskID IN " _
& "(SELECT dbo.tblPlannedTaskAssignees.PlannedTaskID FROM
dbo.tblPlannedTaskAssignees INNER JOIN " _
& "dbo.tblUsers ON dbo.tblPlannedTaskAssignees.UserID =
dbo.tblUsers.UserID " _
& "WHERE (dbo.tblUsers.LoginUserName = SUSER_SNAME()))"

The SUSER_SNAME function doesn't return a name.

Try using SYSTEM_USER instead?
--
regards,

Bradley

A Christian Response
http://www.pastornet.net.au/response

[Ecohouse]

I tried both of the above suggestions and neither one worked. This is
from a freeware version of software, so I assume that it must work
otherwise it wouldn't have been distributed.

So I still don't know what's going on with this. Any other ideas?

[Ecohouse]

Thanks for all the help.

I thought that the SUSER_SNAME value would show up in a SQl string in
the immediate window. Once I copied the sql string into a view it
worked.

This was a program that was distributed free. I assumed that it was
working, but I guess not.
When I played around with the query I was able to get it to work.