Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    California, USA
    Posts
    25

    Unanswered: Run SQL from VB in an Option

    I need help to code VB to run an SQL code to replace a query. I want to eliminate the query and use its SQL code in the form's VB module. How do I code VB to run this SQL from the query. I can't seem to find a way to use "DOCMD.RunSQL" in this VB code. I have an existing database with the following codes.
    Part of the VB that needs to be re-coded to run the SQL is:

    code:
    --------------------------------------------------------------------------------

    Select Case fraSearchOpt
    Case 1
    Me.RecordSource = "qryFindAllWords"

    --------------------------------------------------------------------------------

    The SQL codes from the query is:

    code:
    --------------------------------------------------------------------------------

    SELECT [tblRegulations - California].TrackingID, [tblRegulations - California].[Code Citation], [tblRegulations - California].[Code Name], [tblRegulations - California].[Update Summary]
    FROM [tblRegulations - California]
    WHERE (((AllWordsExist([Code Name],[Forms]![fdlgSearchEngine]![txtKeyword]))=True)) OR (((AllWordsExist([Update Summary],[Forms]![fdlgSearchEngine]![txtKeyword]))=True));

    --------------------------------------------------------------------------------

    If I can get this one option coded, I can do the remaining options myself.

    Thanks much,
    PC

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb Re: Run SQL from VB in an Option

    Originally posted by PC User
    I need help to code VB to run an SQL code to replace a query. I want to eliminate the query and use its SQL code in the form's VB module. How do I code VB to run this SQL from the query. I can't seem to find a way to use "DOCMD.RunSQL" in this VB code. I have an existing database with the following codes.
    Part of the VB that needs to be re-coded to run the SQL is:

    code:
    --------------------------------------------------------------------------------

    Select Case fraSearchOpt
    Case 1
    Me.RecordSource = "qryFindAllWords"

    --------------------------------------------------------------------------------

    The SQL codes from the query is:

    code:
    --------------------------------------------------------------------------------

    SELECT [tblRegulations - California].TrackingID, [tblRegulations - California].[Code Citation], [tblRegulations - California].[Code Name], [tblRegulations - California].[Update Summary]
    FROM [tblRegulations - California]
    WHERE (((AllWordsExist([Code Name],[Forms]![fdlgSearchEngine]![txtKeyword]))=True)) OR (((AllWordsExist([Update Summary],[Forms]![fdlgSearchEngine]![txtKeyword]))=True));

    --------------------------------------------------------------------------------

    If I can get this one option coded, I can do the remaining options myself.

    Thanks much,
    PC
    In order to use the DoCmd.RunSQL you should do an action such as Deleting , INSERT INTO , or SELECT INTO. But to use if just for normal SELECT, you would not be able to use this command.

    My Idea, use an SELECT ...... INTO tempTable so you will be able to use the DoCmd.RunSQL then use the temp table as your RowSource of your Report



Posting Permissions

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