Access Data Project, Dlookup() and A stored Procedure

[Guest]

Hi,

I am converting an Access 2003 database to an Access Data Project using a
SQL Server back end.

I have a form that is bound to an Employee table and insert, updates and
deletes records. My table doesnpot have Employee name, it is an extra field
that is read only and not in the table I'm bound too.

It comes from a seperate table using a dlookup() formula. I would like the
dlookup to read a stored procedure and return an Employee's Name.

Here's the code

=DLookUp("EmployeeName","sp_NameLookup","@EMPLOYEE =
Forms![frmLastStepDates]!txtEMPLOYEE")

Any help would be great

Thanks,

Mike

 

[Sylvain Lafontaine]

To my knowledge, you can't use the DLookup function to call a stored
procedure. Furthermore, even with a table or an Access query taking no
parameter, you syntax for the filter is wrong and should be:

, "@EMPLOYEE = " & Forms![frmLastStepDates]!txtEMPLOYEE

Also, don't use the prefix sp_ for your stored procedures. This prefix has
a special meaning for SQL-Server, will change the way your stored procedures
are compilated and can, on some occasions, lead to some very hard to find
bugs. (However, excerpt for a slight loss of performance, the chances are
high that your SP will still work correctly even if you use it.)

Event for a table, the use of DLookup might give your interface some visual
disconfort; especially if you are using continuous forms. Even in the case
of single forms, you might see some delay when navigating from record to
record or a bigger delay at the beginning when opening the form.

Instead of a DLookup, you should use a Join to directly associate the
Employee name. If you have trouble editing the main table after adding the
Join, it will be solved by specifying the UniqueTable property (and possibly
the ResyncCommand property too) of the form.

Finally, the official newsgroup for ADP is m.p.a.adp.sqlserver.