SQL Server, expressions can call code in modules??

**Rick Brandt** · Jul 18 '06, 05:45 PM

Re: SQL Server, expressions can call code in modules??

tlyczko wrote:

Hello,
>
We have Access databases (backends) that will eventually be
consolidated into a SQL Server database, to facilitate data reporting,
analysis, etc.
>
Some queries in one Access database front end contain expressions that
reference code in Access modules in the Access front end, primarily
the ones from mvps.org/access that will concatenate data from various
fields in *other* additional tables than the main query table.
>
Other queries use the Mid function to concatenate field values from
within the same table.
>
Can such queries be carried over to SQL Server 2005, or will I have to
rewrite those queries somehow??

You'll either have to re-write them so that you get the same functionality
on the SQL Server (SPs or UDFs) or you can use a SQL query or SP to return
the basic data to Access and then still use your Access functions on the
results that are returned.

(moving data to SQL Server does not mean all of your queries are moved to
SQL Server).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

**Lyle Fairfield** · Jul 18 '06, 05:55 PM

Re: SQL Server, expressions can call code in modules??

tlyczko wrote:

Can such queries be carried over to SQL Server 2005, or will I have to
rewrite those queries somehow??

No. Yes.

You can do the calculations in line. Perhaps better, you can create
Functions within the SQL-Servers T-SQL and use them in your Stored
Procedures.

An example:

ALTER FUNCTION [dbo].[ProperCase]
(
@VarString varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
DECLARE @NewString varchar(8000)
DECLARE @Length int
DECLARE @Position int
DECLARE @CharAtPosition varchar(1)
DECLARE @ASCIIOfChar tinyint
DECLARE @WordStart bit

SET @NewString = ''
SET @Length = LEN (@VarString)
SET @Position = 1
SET @WordStart = 1

WHILE (@Position <= @Length)
BEGIN
SET @CharAtPosition = LOWER(SUBSTRING (@VarString, @Position, 1))
IF (@WordStart = 1)
BEGIN
SET @CharAtPosition = UPPER (@CharAtPositio n)
END

SET @ASCIIOfChar = ASCII(@CharAtPo sition)
IF ((@ASCIIOfChar> 64 AND @ASCIIOfChar<92 ) OR (@ASCIIOfChar>9 6 AND
@ASCIIOfChar<12 3))
SET @WordStart = 0
ELSE
SET @WordStart = 1

SET @NewString = @NewString + @CharAtPosition

SET @Position = @Position + 1
END

RETURN @NewString
END

And it could be used:

CREATE PROCEDURE MakeFFDBAAccoun tsProperCase
AS
UPDATE FFDBAAccounts SET CommonName = dbo.ProperCase( CommonName)
RETURN

**tlyczko** · Jul 18 '06, 07:25 PM

Re: SQL Server, expressions can call code in modules??

Rick Brandt wrote:

You'll either have to re-write them so that you get the same functionality
on the SQL Server (SPs or UDFs) or you can use a SQL query or SP to return
the basic data to Access and then still use your Access functions on the
results that are returned.
>
(moving data to SQL Server does not mean all of your queries are moved to
SQL Server).

Rick, from your second comment, it sounds like for the few queries
where the query uses Access module code, I should use an Access query
against the linked SQL tables??? and just put this particular kind of
code in the front end??.

That would be the simplest way to do things, but if it were possible
for me to figure out doing this in a SQL 2005 query, I would not be
limited to using Access only for this particular query??

Here is part of the SQL code per se from the select query with the Mid
formula (I got it from this NG somewhere):

Mid("12"+[txtDescription] & ", "+[memComments] & ",
"+[memMedicationEr rorComments],3) AS txtAdditionalIn formation

Here is an example of the SQL code per se from the select query with
the Access module call:
txtStaffRespons ible:
fConcatChild("t blIncidentStaff ","IncidentID", "txtStaffName", "Long",[tblIncidents].[IncidentID])

fConcatChild is a function from
http://www.mvps.org/access/modules/mdl0004.htm, which concatenates
field values from a child table's field.

Thank you,
Tom

**Rick Brandt** · Jul 18 '06, 08:25 PM

Re: SQL Server, expressions can call code in modules??

tlyczko wrote:

Rick Brandt wrote:
>

>You'll either have to re-write them so that you get the same
>functionalit y on the SQL Server (SPs or UDFs) or you can use a SQL
>query or SP to return the basic data to Access and then still use
>your Access functions on the results that are returned.
>>
>(moving data to SQL Server does not mean all of your queries are
>moved to SQL Server).

>
Rick, from your second comment, it sounds like for the few queries
where the query uses Access module code, I should use an Access query
against the linked SQL tables??? and just put this particular kind of
code in the front end??.
>
That would be the simplest way to do things, but if it were possible
for me to figure out doing this in a SQL 2005 query, I would not be
limited to using Access only for this particular query??
>
Here is part of the SQL code per se from the select query with the Mid
formula (I got it from this NG somewhere):
>
Mid("12"+[txtDescription] & ", "+[memComments] & ",
"+[memMedicationEr rorComments],3) AS txtAdditionalIn formation
>
Here is an example of the SQL code per se from the select query with
the Access module call:
txtStaffRespons ible:
fConcatChild("t blIncidentStaff ","IncidentID", "txtStaffName", "Long",[tblIncidents].[IncidentID])
>
fConcatChild is a function from
http://www.mvps.org/access/modules/mdl0004.htm, which concatenates
field values from a child table's field.
>
Thank you,
Tom

Certainly what you have there (simple concatenation) can easily be done in
SQL Server's T-SQL langauage. My experience is that the vast majority of
your Access queries can just stay as Access queries against the SQL Server
linked tables.

There will be isolated cases where that strategy performs badly because of
too much data being pulled over the wire, but despite the horror stories you
might hear from people who have never tried it, this will only be true of a
handful of your queries. Those few can be re-written As Views or Stored
Procedures on the SQL Server or changed into Pass-Through queries in Access.
MOST of your queries will be just fine as they are.

Jet/ODBC will send most of the selection work to the server even in queries
where a VBA function is being used. As long as the basic SELECT and WHERE
clause can be sent to the server the VBA functions will then be applied only
to the rows that are returned.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

**Lyle Fairfield** · Jul 18 '06, 10:05 PM

Re: SQL Server, expressions can call code in modules??

Jet/ODBC will send most of the selection work to the server even in queries

where a VBA function is being used. As long as the basic SELECT and WHERE
clause can be sent to the server the VBA functions will then be applied only
to the rows that are returned.

My understanding is that if the Server has an equivalent function, JET
will send the function to the Server for processing. Thus, with
SQL-Server, TRIM is likely to be sent to the Server and processed
there, but IIF will handled locally.JET uses the ODBC API function
SQLGetInfo function to enquire as to such existence.
Of course, there are no equivalents to UDFs, or rather SQLGetInfo is
unlikely to recognize equivalents.

But I am not an ODBC person or proponent. I remain of the opinion that
if one is to use a Server such as SQL-Server one can and should learn
to use all its power; this involves using Stored Procedures, Views and
Functions that live on the SQL-Server, regardless of the nature of the
connection to the Server.
The only advantage of ADPs that I can see at this time is that they
enable, and perhaps, encourage, the user/developer to create Stored
Procedures, Views and Functions from within the Access Interface. Thus,
they could be a good vehicle for expermintation and learning. I suppose
that if one were developing an MDB connected to SQL-Server via ODBC,
one might use a corresponding ADP connected to the same DB during
development to develop and test Stored Procedures, Views and Functions.
But I am rambling ....

**tlyczko** · Jul 19 '06, 12:25 PM

Re: SQL Server, expressions can call code in modules??

Lyle Fairfield wrote:

But I am not an ODBC person or proponent. I remain of the opinion that
if one is to use a Server such as SQL-Server one can and should learn
to use all its power; this involves using Stored Procedures, Views and
Functions that live on the SQL-Server, regardless of the nature of the
connection to the Server.

I agree, but I can't learn everything at once!! :) :)

The only advantage of ADPs that I can see at this time is that they
enable, and perhaps, encourage, the user/developer to create Stored
Procedures, Views and Functions from within the Access Interface. Thus,
they could be a good vehicle for expermintation and learning. I suppose
that if one were developing an MDB connected to SQL-Server via ODBC,
one might use a corresponding ADP connected to the same DB during
development to develop and test Stored Procedures, Views and Functions.
But I am rambling ....

I will try ADPs too. It was just that particular formula-based
expression I was concerned about because it gathers neatly into one
place important information we need, and that will be necessary for me
to learn how to do with SQL Server since our long-term goal is to use
SQL Reporting Services instead of Access to create many of our reports,
because then we can just give an internal website link...

Anyway, I know I might end up trying to rewrite that fConcatChild in
some manner that SQL can read instead of using an Access module etc.
Time will tell. Hopefully there is a good SQL Server NG out there...

Thank you, Tom

**CDMAPoster@FortuneJames.com** · Jul 20 '06, 06:55 PM

Re: SQL Server, expressions can call code in modules??

Lyle Fairfield wrote:

Jet/ODBC will send most of the selection work to the server even in queries
where a VBA function is being used. As long as the basic SELECT and WHERE
clause can be sent to the server the VBA functions will then be applied only
to the rows that are returned.

>
My understanding is that if the Server has an equivalent function, JET
will send the function to the Server for processing. Thus, with
SQL-Server, TRIM is likely to be sent to the Server and processed
there, but IIF will handled locally.JET uses the ODBC API function
SQLGetInfo function to enquire as to such existence.

Very interesting. I didn't know that.

Of course, there are no equivalents to UDFs, or rather SQLGetInfo is
unlikely to recognize equivalents.

While developing for A97 I thought that by now I would be have to start
using ADODB and SQL Server for everything. It's happening a little
differently than I expected. The UDF's in Access for queries are quite
handy. I'd love to be able to do something similar without having to
write an unwieldly T-SQL function. It's nice to know that that's
available, but do you think it might be possible to come up with nicer
UDF's using managed code and, IIRC, CreateAssembly within SQL Server?

>
But I am not an ODBC person or proponent. I remain of the opinion that
if one is to use a Server such as SQL-Server one can and should learn
to use all its power; this involves using Stored Procedures, Views and
Functions that live on the SQL-Server, regardless of the nature of the
connection to the Server.

Lyle, I appreciate the help you've given me in moving toward that goal.

The only advantage of ADPs that I can see at this time is that they
enable, and perhaps, encourage, the user/developer to create Stored
Procedures, Views and Functions from within the Access Interface. Thus,
they could be a good vehicle for expermintation and learning. I suppose
that if one were developing an MDB connected to SQL-Server via ODBC,
one might use a corresponding ADP connected to the same DB during
development to develop and test Stored Procedures, Views and Functions.
But I am rambling ....

I did that several years ago and it was a great vehicle for learning.
I just need to take over an app made by someone else that runs slowly
enough to require me to learn the ins and outs of SQL Server's
strengths :-). I look forward to learning how to tweak performance in
SQL Server.

James A. Fortune
CDMAPoster@Fort uneJames.com

**Lyle Fairfield** · Jul 20 '06, 09:05 PM

Re: SQL Server, expressions can call code in modules??

The UDF's in Access for queries are quite

handy. I'd love to be able to do something similar without having to
write an unwieldly T-SQL function. It's nice to know that that's
available, but do you think it might be possible to come up with nicer
UDF's using managed code and, IIRC, CreateAssembly within SQL Server?

I am hoping someone else can answer this as I do not know.

**CDMAPoster@FortuneJames.com** · Jul 21 '06, 06:55 PM

Re: SQL Server, expressions can call code in modules??

Lyle Fairfield wrote:

The UDF's in Access for queries are quite
handy. I'd love to be able to do something similar without having to
write an unwieldly T-SQL function. It's nice to know that that's
available, but do you think it might be possible to come up with nicer
UDF's using managed code and, IIRC, CreateAssembly within SQL Server?

>
I am hoping someone else can answer this as I do not know.

I just sent an email asking for more information to the database
specialist at Microsoft who told me about this.

James A. Fortune
CDMAPoster@Fort uneJames.com

**CDMAPoster@FortuneJames.com** · Jul 21 '06, 11:55 PM

Re: SQL Server, expressions can call code in modules??

CDMAPoster@Fort uneJames.com wrote:

Lyle Fairfield wrote:

The UDF's in Access for queries are quite
handy. I'd love to be able to do something similar without having to
write an unwieldly T-SQL function. It's nice to know that that's
available, but do you think it might be possible to come up with nicer
UDF's using managed code and, IIRC, CreateAssembly within SQL Server?

I am hoping someone else can answer this as I do not know.

>
I just sent an email asking for more information to the database
specialist at Microsoft who told me about this.
>
James A. Fortune
CDMAPoster@Fort uneJames.com

Here's the reply:

'-------
You can find information on CREATE ASSEMBLY here.

http://msdn2.microsoft.com/en-us/library/ms189524.aspx

Mike
'-------

James A. Fortune
CDMAPoster@Fort uneJames.com