Upsize to SQL - Query Criteria

[Scott]

I want to upsize my Access DB which is already split as FE BE to SQL Server
2000; in upsizing you have 2 options.

1. Upsize just tables, and then all data processing is done locally on
Access Jet DB Engine.

2. Upsize Tables and queries, then you have to create an ADP file instead of
an MDB, the benefit of this option is that the data processing is done on
SQL Server which is much faster than Jet.

I have in my DB a lot of Queries that uses me own defined function as the
criteria, the function is as follows:

*************************************

Public Function Client() As Long
Client =
CurrentDb.Containers!Databases.Documents!UserDefined.Properties("Client")
End Function

************************************

That refers to the property I have added to the Database Properties, and in
my Queries I set the criteria of the client field to:

Client()

Now how do I upsize these Queries to SQL Server. ?


Thanks in advance,


Scott

 

[Narayana Vyas Kondreddi]

Use the HOST_NAME() function
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm




I want to upsize my Access DB which is already split as FE BE to SQL Server
2000; in upsizing you have 2 options.

1. Upsize just tables, and then all data processing is done locally on
Access Jet DB Engine.

2. Upsize Tables and queries, then you have to create an ADP file instead of
an MDB, the benefit of this option is that the data processing is done on
SQL Server which is much faster than Jet.

I have in my DB a lot of Queries that uses me own defined function as the
criteria, the function is as follows:

*************************************

Public Function Client() As Long
Client =
CurrentDb.Containers!Databases.Documents!UserDefined.Properties("Client")
End Function

************************************

That refers to the property I have added to the Database Properties, and in
my Queries I set the criteria of the client field to:

Client()

Now how do I upsize these Queries to SQL Server. ?


Thanks in advance,


Scott

 

[Scott]

Could you please provide more detailed info on your answer. ?

Thanks

Scott

 

[Narayana Vyas Kondreddi]

Oops...sorry, I misread your question.

Here's an example:

CREATE FUNCTION dbo.Client()
RETURNS varchar(25)
AS
BEGIN
DECLARE @Client varchar(25)
/*
Set the @Client variable to the desired value here.
If you are talking about extended properties of SQL Server 2000,
then look up fn_listextendedproperty in SQL Server 2000 Books Online.
*/
RETURN @Client
END

--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm




Could you please provide more detailed info on your answer. ?

Thanks

Scott

 

[Mary Chipman]

If you want to continue to use Jet features, then choose NOT to create
an ADP when upsizing. You assumption is incorrect when you state that
all data processing is done locally otherwise. If your function is
embedded in a query field, then yes, it will run once for every row
retrieved. However, if it is simply passed to a WHERE clause (say in
VBA code), then the SELECT statement is executed on the server, which
returns the result set back to Access.

These days even Microsoft is recommending upsizing using an mdb as the
FE instead of an adp (based on talks given by the Access team at
conferences in the last year). It gives you a lot more flexibility
having Jet around as a local cache and query processor. That being
said, fetch only needed data, avoid binding forms to entire tables and
try to take advantage of stored procedures, UDFs, and other server
features as much as possible. Access doesn't have to be a pig of a FE
unless you build it that way.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446