Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    41

    Unanswered: Running SQL Statements from VBA

    I am new to Access programming. I have a sql statement in a string variable (a select statement) and I want to run it from VBA, and have the results pop up in an editable grid window, just as ifI were running a normal access query. How do I do that?

    I am using access 2000.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Posts
    139

    Re: Running SQL Statements from VBA

    Never done this, but think you will find some clues in the Access Help file under the key word: QueryDef


    hth

  3. #3
    Join Date
    May 2003
    Location
    Maryland
    Posts
    11

    Post Re: Running SQL Statements from VBA

    Ok, What you want do to is use the .sql method from VBA,
    How I did this was to create a baseQuery that I knew I would
    be using for the SearchResults Form, and I manipulate the Where Clause in vb.

    It does not really matter, once you have the QDef established its really providing a object for Access to keep tract of and use, you can really manipulate the SQL under the hood.

    I did this in a DAO enviroment. You would need to reference the DAO 3.6 Library. I turned off ADO for this project, if you use it then referance DAO and ADO specifically in your objects. Ie DAO.Recordset, so VB knows what to do. -- Lost Much hair on that one. --

    This code was cut from my Seach Module, but its incomplete if you just cut and paste it wont work. It does provide a working example of the critical piece you need however.


    ' REM makes the Querys SQL statement your string.
    ' Note although Im only interested in changing the Where Clause I have
    ' to pass the complete SQL string I want for the Query now.

    Let dbs.QueryDefs!Search.SQL = StrBldSQL

    'You will need to open a connection to your db and close it,
    'I just happened to close mine here.
    dbs.Close

    ' REm Opens the form I linked to the Query, you can just open the
    query as well.

    stDocName = "frm_SearchResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit_Command10_Click:
    Exit Sub

    I hope this helps. BB


    Originally posted by secutanudu
    I am new to Access programming. I have a sql statement in a string variable (a select statement) and I want to run it from VBA, and have the results pop up in an editable grid window, just as ifI were running a normal access query. How do I do that?

    I am using access 2000.

    Thanks
    Last edited by blackbird_61; 05-17-03 at 14:41.
    K did you Flashy thing me, K??

Posting Permissions

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