Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    SQL Select Satements (Access 2003)

    Can anyone tell me what is wrong with this Function.

    Function FindOpWells()
    Dim SQL As String
    SQL = "SELECT * FROM tblOperations"
    DoCmd.RunSQL SQL
    End Function

    When I try and run it I am told "An Sql Statements requires an arguement consisting of an SQL statement. My original Query was more complicated and I shortened it to try and see what I was doing incorrectly.

    thanks

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Select Satements (Access 2003)

    Your SQL string is for a select query that returns records. RunSQL is meant to execute SQL strings that represent action queries, i.e. make table, append, update or delete queries. Such queries begin with SELECT INTO, INSERT INTO, UPDATE or DELETE, not with just SELECT.

    What do you want to accomplish with this function?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: SQL Select Satements (Access 2003)

    The object was to show which wells were already assigned to an operator. The operator is selected from a combo box and a click on a button is tsupposed to run the query. On thinking about it I suppose I want to make a table based on the records is this correct?

    Thanks for your help.

    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Select Satements (Access 2003)

    Why don't you open a form based on tblOperations?

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: SQL Select Satements (Access 2003)

    I was trying to reduce the number of forms I have and thought a datasheet would do the job. I assume from what you have told me there is not a way to do this. Anyway once agian my thanks for all your help at least I know what I cannot do.

    regards

    Peter

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Select Satements (Access 2003)

    To display a datasheet (which I don't recommend), you would have to create and save a query, then use DoCmd.OpenQuery. There is no way you can show the records returned by a SELECT SQL string directly, without using a stored query or form.

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: SQL Select Satements (Access 2003)

    Thank you again. If I use a form I think I will have to base it on a query as I want to pass a parmeter so I only get the records relative to the operator that has been selected. Therefore I assume I cannot use the DoCmd.Openquery as I cannot pass a paremeter to it, am I correct?

    Regards

    Peter

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Select Satements (Access 2003)

    Well, you could create a query with criteria of the form [Forms]![frmOperators]![txtOperator], and open this. But I never let end users view a table or query directly; they can only view and edit data in a form. Since DoCmd.OpenForm has a WhereCondition argument, it is easy to filter a form.

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: SQL Select Satements (Access 2003)

    Thanks Hans I will try that, had not thought about what havoc users could create, its early days for me.

    regards

    Peter

Posting Permissions

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