Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    Unanswered: What is alternative to DLookup in Sql server?

    Hey all,
    I have access as my front end and has upsized to sql server 2000.
    DLookup function that was in VBA code before was working fine. but after upsizing there is the problem in that code...
    What could be the alternative for DLookup Function in sql server..
    Do i have to write a view or stored procedure..?

    Any Help appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Dlookup (and its other D function cousins) are nasty little functions. No offence - they are convenient and an easy way to feed data from tables into VBA code but that attraction and apparent simplicity is a source of bad habits. But they are highly inefficient and should always be seen as "quick and dirty" - defo you don't want them in queries returning more than a nominal number of resultsets.

    If you examine the syntax of DLookup in relation to simple SQL statement you see they are actually very easily replaced.
    DLOOKUP:
    Code:
    DLOOKUP("columnName", "tableName", [optional]"filterExpression")
    Simple SQL statement:
    Code:
    SELECT columnName FROM tableName WHERE FilterExpression
    They are nothing more than a simple select statement with the clause keywords removed. So - can you see how to replace them?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Yup...
    I tried this but failed to run parametized select statement...
    from

    Dim s as string
    s = "Select email from owner_primary where name ='" & master_Table![prim_owner] & " ' "

    doCmd.OpenQuery(s)


    as it doesnt give any output where i could store my email's value...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah - your FE is still access huh?

    Well - you have a few options. Personally I would stick the SQL in SS proc and pass the value as a parameter to it.

    However you do it however you need to get hold of the value. You will need to user a recordset if you return the value as part of a dataset.

    Also - I missed something out in my last post - it is actually equivalent to:
    Code:
    SELECT TOP 1 columnName FROM tableName WHERE FilterExpression
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Thanks though

    Ok...

    I ll try and let you know

  6. #6
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81

    wht should be stored proc code??

    how cud i achieve my same functionality from stored procedure...
    if i have...
    strOw = DLookup("email", "owner", "name='Master_Table![Owner]'")

    coz i need to have a value return in "strOw" so lil bit confused....


    Thanks

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here are two methods:
    Code:
    set @str0w = (select email from owner where name = 'Master_Table![Owner]')
    
    select @str0w = email from owner where name = 'Master_Table![Owner]'
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Thx
    but i have five different look ups from five different tables..
    thats confusing me alot....
    for one lookup i am hope ful that this will work but for five different lookups..?
    Do i have to write five different stored procedures for them...

    or could i combine them in one......?

    Doesnt it cumbersome to write a whole stored proc to get only one "email" address..
    and as i have five so....
    Plz suggest some better way to replace my DLookUp function of which i found stored proc last option...

    Thx
    Last edited by musman; 07-26-07 at 10:41.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you don't need five stored procs

    try JOINs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Please explain what you are trying to do, musman. You seem unclear on database server best practices, so I suspect that your approach to solving your problem may be wrong.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    musman - never a good idea to abandon a thread and start a new one on exactly the same topic. People don't get the full story
    http://www.dbforums.com/showthread.php?t=1620761
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    threads merged!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by musman
    Thx
    but i have five different look ups from five different tables..
    thats confusing me alot....
    for one lookup i am hope ful that this will work but for five different lookups..?
    Do i have to write five different stored procedures for them...

    or could i combine them in one......?

    Doesnt it cumbersome to write a whole stored proc to get only one "email" address..
    and as i have five so....
    Plz suggest some better way to replace my DLookUp function of which i found stored proc last option...
    Yes - you would normally have five procs. When programming a server level database (like SQL Server) you are much more focussed on performance and security than with Access. With Access you might write (or use) generic functions that are flexible but not as "tight" as they might be. Generality takes a backseat to optimisation & performance in back end programming.

    To make things feel more generic you could write your six procedures but not expose them to the interface. Instead have one of them as a "console" procedure that you call from your application, telling it the lookup you want to get the value from. Your console procedure then calls the appropriate procedure. You only now have to access one procedure from the application (flexiblity & generality) but SQL Server only executes optimised procedures - the best of both worlds. You just have to type a lot more
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jul 2007
    Location
    Columbia SC
    Posts
    81
    Thanks blindman for your help...

    well what i have done. I was thinking joins as too cumbersome as it will slow down the server a lil bit. so what i did i created my own customized DLookUp function...
    and i include ado code in it.. and it seems that its working fine except one error and that's, i believe, is from vb...
    but its fetching the correct data...
    but above logic is also good and i m getting it what you saying...

    and i m sorry about starting a new thread. I mistakenly pressed the new thread button i gues...

    Well, I do apologize for this...

    Thanks though...

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by musman
    well what i have done. I was thinking joins as too cumbersome as it will slow down the server a lil bit. so what i did i created my own customized DLookUp function...
    and i include ado code in it.. and it seems that its working fine except one error and that's, i believe, is from vb
    Joins are very efficient in RDBMSs. They have to be afterall since relational design requires that your data will be distributed across many tables. What is inefficient is nibbling away at a data requirement by making the same, small query repetatively (e.g. if you use dlookup or an equivelent in a query). You will find this throughout SQL Server programming.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •