MS Access project with SQL Server - dlookup with stored proc's

Author

dlookup with stored proc's

stuartb113 via AccessMonster.com

2006-03-31, 3:32 am

hi, i am upgrading an Access97 mdb to Access2002 adp. As part of the
exercise i am implementing further security by utilising stored procedures
calls.

The existing mdb calls dlookup in a number of instances. Has anyone found a
workaround to using Dlookup with stored procedures? or is the alternative to
replace dlookup an ADO connection to the database?

thanks,
Stuart

--
Message posted via http://www.webservertalk.com

David Portas

2006-03-31, 3:32 am

stuartb113 via webservertalk.com wrote:
> hi, i am upgrading an Access97 mdb to Access2002 adp. As part of the
> exercise i am implementing further security by utilising stored procedures
> calls.
>
> The existing mdb calls dlookup in a number of instances. Has anyone found a
> workaround to using Dlookup with stored procedures? or is the alternative to
> replace dlookup an ADO connection to the database?
>
> thanks,
> Stuart
>
> --
> Message posted via http://www.webservertalk.com

Hi Stuart,

In a SQL stored procedure you can use a join instead of the DLOOKUP
function. If the lookup criteria is a unique one then an outer join is
equivalent to a DLOOKUP. The obvious difference between a join and a
lookup is when the criteria isn't unique. In that case DLOOKUP will
return an arbitrary value from the set of matching rows while a join
will return _every_ row.

This is an example of why it is important to have a solid logical model
to start with. Jet databases in Access permit non-deterministic results
based on the arbitrary order in which rows are stored and processed
internally. For very good reasons, SQL generally does not support those
kinds of results (admittedly there are one or two exceptions in SQL but
we usually try to avoid them because unpredictable results aren't
usually what we want).

In conclusion, do not assume that a data model designed for Access will
be suitable for SQL Server. You should review your logical data model
and it is wise to assume that you will have to make some changes in
order to implement it properly in SQL. If your data model is normalized
to at least BCNF level then you should always be able to replace
DLOOKUP with a join, perhaps utilizing a derived table or subquery as
well.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Vadim Rapp

2006-03-31, 7:40 am

svA> The existing mdb calls dlookup in a number of instances. Has anyone
svA> found a workaround to using Dlookup with stored procedures? or is the
svA> alternative to replace dlookup an ADO connection to the database?

if dlookup(field, sproc, "@parm1=value") does not work, it's easy to code a
"replacement" function in VBA

Vadim Rapp.

stuartb113 via AccessMonster.com

2006-03-31, 7:40 am

David thanks for your response.

The lookup criteria is unique based on the primary key value but i'm missing
your point on implementing an outer join.
Currently I have for eg's:
DLookup("RefId", "Reference", "RefComponent='I' AND InstrumentId=" & Me.
cInstrumentId)

I'ved created a simple stored proc:
CREATE PROCEDURE up_reference_list
AS
SELECT *
FROM reference r
GO

Tried amending the Dlookup but as the error message tells me a stored proc
can not been used in place of the domain
i.e.
DLookup("RefId", "up_reference_list", "RefComponent='I' AND InstrumentId=" &
Me.cInstrumentId)

I could amend the stored proc to allow parameters and use ADODB excute call
to return the Refid but was hoping not to add the additional coding

thanks,
Stuart

David Portas wrote:
>[quoted text clipped - 6 lines]
>
>Hi Stuart,
>
>In a SQL stored procedure you can use a join instead of the DLOOKUP
>function. If the lookup criteria is a unique one then an outer join is
>equivalent to a DLOOKUP. The obvious difference between a join and a
>lookup is when the criteria isn't unique. In that case DLOOKUP will
>return an arbitrary value from the set of matching rows while a join
>will return _every_ row.
>
>This is an example of why it is important to have a solid logical model
>to start with. Jet databases in Access permit non-deterministic results
>based on the arbitrary order in which rows are stored and processed
>internally. For very good reasons, SQL generally does not support those
>kinds of results (admittedly there are one or two exceptions in SQL but
>we usually try to avoid them because unpredictable results aren't
>usually what we want).
>
>In conclusion, do not assume that a data model designed for Access will
>be suitable for SQL Server. You should review your logical data model
>and it is wise to assume that you will have to make some changes in
>order to implement it properly in SQL. If your data model is normalized
>to at least BCNF level then you should always be able to replace
>DLOOKUP with a join, perhaps utilizing a derived table or subquery as
>well.
>
>Hope this helps.
>
>--
>David Portas, SQL Server MVP
>
>Whenever possible please post enough code to reproduce your problem.
>Including CREATE TABLE and INSERT statements usually helps.
>State what version of SQL Server you are using and specify the content
>of any error messages.
>
>SQL Server Books Online:
>http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>--

--
Message posted via http://www.webservertalk.com

David Portas

2006-03-31, 9:34 am

stuartb113 via webservertalk.com wrote:
> David thanks for your response.
>
> The lookup criteria is unique based on the primary key value but i'm missing
> your point on implementing an outer join.
> Currently I have for eg's:
> DLookup("RefId", "Reference", "RefComponent='I' AND InstrumentId=" & Me.
> cInstrumentId)
>
> I'ved created a simple stored proc:
> CREATE PROCEDURE up_reference_list
> AS
> SELECT *
> FROM reference r
> GO
>
> Tried amending the Dlookup but as the error message tells me a stored proc
> can not been used in place of the domain
> i.e.
> DLookup("RefId", "up_reference_list", "RefComponent='I' AND InstrumentId=" &
> Me.cInstrumentId)
>
> I could amend the stored proc to allow parameters and use ADODB excute call
> to return the Refid but was hoping not to add the additional coding
>
> thanks,
> Stuart
>

My point about joins was that when DLOOKUP is used in a SELECT
statement it can be replaced by a join. It seems like you are doing
something other than a SELECT statement with your Access code. I would
suggest using a parameterized proc but I can't help you with what other
alternatives might exist in Access.

PS. Don't use SELECT * in procs. It harms performance and makes your
code harder to maintain. List the columns by name.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

Sylvain Lafontaine

2006-03-31, 1:32 pm

If all you want is to implement further security while still keeping
compatibility with DLookup, then you should use a View instead of a SP.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"stuartb113 via webservertalk.com" <u20302@uwe> wrote in message
news:5e14243fdeb39@u
we...
> David thanks for your response.
>
> The lookup criteria is unique based on the primary key value but i'm
> missing
> your point on implementing an outer join.
> Currently I have for eg's:
> DLookup("RefId", "Reference", "RefComponent='I' AND InstrumentId=" & Me.
> cInstrumentId)
>
> I'ved created a simple stored proc:
> CREATE PROCEDURE up_reference_list
> AS
> SELECT *
> FROM reference r
> GO
>
> Tried amending the Dlookup but as the error message tells me a stored proc
> can not been used in place of the domain
> i.e.
> DLookup("RefId", "up_reference_list", "RefComponent='I' AND InstrumentId="
> &
> Me.cInstrumentId)
>
> I could amend the stored proc to allow parameters and use ADODB excute
> call
> to return the Refid but was hoping not to add the additional coding
>
> thanks,
> Stuart
>
> David Portas wrote:
>
> --
> Message posted via http://www.webservertalk.com

Sponsored Links