Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Other Forums  MS Access  Assign return value from function to access string

Author  Topic  YevS Starting Member 3 Posts Posted - 2009-08-06 : 06:17:38 Hi guys, I normaly dwell on the access forums but this is more of an SQL server question. I have a bit of a backwards problem with our system. Its an Access front end and SQL back end (2000) legacy system. The problem is I cant change the code in the access bit without jumping through a lot of corporate hoops, but I do have authority to change the SQL server. There is a bit of code that runs a simple select statement in access. Unfortunately the client is requesting that columns be added to that select statement, which will require a recompile of the front end (new mde file). I will still have to go jump through hoops to get this done, the client requests new/different columns every other week... What I want to do is store the SQL statment on the server as a string so I can add/remove columns as I want without having to go get the code changed. I made a Scalar function on the server: CREATE FUNCTION [dbo].[SQLStr] () RETURNS nvarchar(2000)AS BEGIN Declare @SQL nvarchar(2000) Set @sql =N'SELECT...' return @sql END GO But I have no idea how to get that return value and assign it to a string in access! Can any one suggest a way? Thanks!