dlookup with stored proc's

  • Thread starter Thread starter stuartb113 via AccessMonster.com
  • Start date Start date
S

stuartb113 via AccessMonster.com

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
 
stuartb113 said:
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
Click to expand...

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
--
 
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.
 
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 said:
hi, i am upgrading an Access97 mdb to Access2002 adp. As part of the
exercise i am implementing further security by utilising stored procedures
Click to expand...
[quoted text clipped - 6 lines]
thanks,
Stuart
Click to expand...

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
--
Click to expand...
 
stuartb113 said:
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
Click to expand...

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

--
 
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 AccessMonster.com said:
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 said:
hi, i am upgrading an Access97 mdb to Access2002 adp. As part of the
exercise i am implementing further security by utilising stored
procedures
Click to expand...
[quoted text clipped - 6 lines]
thanks,
Stuart
Click to expand...

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
--
Click to expand...
Click to expand...
 
Back
Top