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??
I have the Chapman/Baron reference book and will be reading that a lot
eventually.
Thank you, Tom 10 2109
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
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 (@CharAtPosition)
END
SET @ASCIIOfChar = ASCII(@CharAtPosition)
IF ((@ASCIIOfChar>64 AND @ASCIIOfChar<92) OR (@ASCIIOfChar>96 AND
@ASCIIOfChar<123))
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 MakeFFDBAAccountsProperCase
AS
UPDATE FFDBAAccounts SET CommonName = dbo.ProperCase(CommonName)
RETURN
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] & ",
"+[memMedicationErrorComments],3) AS txtAdditionalInformation
Here is an example of the SQL code per se from the select query with
the Access module call:
txtStaffResponsible:
fConcatChild("tblIncidentStaff","IncidentID","txtS taffName","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
tlyczko wrote:
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] & ",
"+[memMedicationErrorComments],3) AS txtAdditionalInformation
Here is an example of the SQL code per se from the select query with
the Access module call:
txtStaffResponsible:
fConcatChild("tblIncidentStaff","IncidentID","txtS taffName","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
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 ....
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
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 CD********@FortuneJames.com
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.
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 CD********@FortuneJames.com CD********@FortuneJames.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 CD********@FortuneJames.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 CD********@FortuneJames.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Timo Virkkala |
last post by:
I'm creating a system with Python CGIs, that connect to a database. I'm
wondering about input validation. Of course I will check the length of
the passed parameters, to (hopefully) prevent any DOS...
|
by: Raymond Hettinger |
last post by:
Peter Norvig's creative thinking triggered renewed interest in PEP 289.
That led to a number of contributors helping to re-work the pep details
into a form that has been well received on the...
|
by: Kenneth McDonald |
last post by:
I'm working on the 0.8 release of my 'rex' module, and would appreciate
feedback, suggestions, and criticism as I work towards finalizing the
API and feature sets. rex is a module intended to make...
|
by: Hernán Castelo |
last post by:
should i to validate all the "Request"s calls
like Request.FORM("...") and Request.Cookies("...")
????
if it is so, i have to see inside
every "Input" elements like "Text" and even "Hidden"...
|
by: Justin Lazanowski |
last post by:
Cross posting this question on the recommendation of an
I have a .NET application that I am developing in C# I am loading
information in from a dataset, and then pushing the dataset to a grid,...
|
by: billmiami2 |
last post by:
I was playing around with the new SQL 2005 CLR functionality and
remembered this discussion that I had with Erland Sommarskog concerning
performance of scalar UDFs some time ago (See "Calling...
|
by: aarklon |
last post by:
Hi all,
Recently i was reading a c book which contained a section called C
pitfalls.
it had a paragraph on the following lines:-
...
|
by: Geoff Hill |
last post by:
What's the way to go about learning Python's regular expressions? I feel
like such an idiot - being so strong in a programming language but knowing
nothing about RE.
|
by: raylopez99 |
last post by:
Took a look at all the fuss about "lambda expressions" from Jon
Skeet's excellent book "C# in Depth". Jon has an example, reproduced
below (excerpt) on lambda expressions.
My n00b take: it's...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
| | |