SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MS Access Queries

    For reasons beyond my control, I may have to use an Access database for a small project. Fortunately, I am unfamiliar with Access.

    My goal is to be able to create queries that act like stored procedures (i.e., I can use conditional statements in combination with simple selects/inserts/updates/deletes). Below is an example of something I may like to do:

    Code:
    @user as string
    @pass as string
    
    select id from client where user=@user and pass=@pass
    if @@rowcount > 0 begin
       -- do something
    else begin
       -- do something different
    end
    return 0
    Excuse the syntax, but would this be possible in Access queries?

  2. #2
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can use 'queries' as stored procedures, but the syntax is a little different:
    Code:
    PARAMETERS Username String, Password String;
    SELECT id FROM client 
    WHERE user=Username and pass=Password
    I'm not too sure on the If-Then part, though... depends what you are trying to do. The 'do something' is a bit too vague. ;-)

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, just picture the "do something" as an insert statement. I'm more concerned with whether conditional statements are possible in queries.

  4. #4
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Open the MS Access help file and look into the iif() syntax.

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't currently have Access on my system, but thanks for the keyword. Is there a definitive list of keywords that can be used in Access queries? For instance; for/while loops, case statements, identity/rowcount statements, etc.?

  6. #6
    morphine for a wooden leg randem's Avatar
    Join Date
    Jun 2002
    Location
    .chicago.il.us
    Posts
    957
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you dig a little bit, you'll find one at http://msdn.microsoft.com

  7. #7
    SitePoint Zealot JEmLAC's Avatar
    Join Date
    Apr 2002
    Location
    Kansas City
    Posts
    156
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's a wee bit confusing, what you're asking for. On the one hand you want to build a query, a la SQL. On the other hand you're talking about procedural syntax (if/else, while loops etc.). The two are distinct, although there is support for inserting functions (native and user-defined) in Access queries. e.g.
    Code:
    SELECT [Time Table].HOURS, IIf([hours]>0,"positive",IIf([hours]<0,"negative","Zero")) AS PosNeg
    FROM [Time Table];
    This uses the the IIf() function already mentioned. More advanced procedural process like the select case and looping examples you mention will require creating code modules, which you can do in Access, if you have a taste for VB. Judging by your earlier comments I'm betting against that.

    Hope it helps.

    B
    Morning person by habit, not by nature.


Bookmarks

Posting Permissions

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