#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    1
    Rep Power
    0

    using an SQL query as RecordSource


    I'm working on a database application in Visual Basic 6. The database seems to connect fine. Now I'm merely trying set RecordSource properties to an SQL query in run-time instead of a table, but when I run it it crashes. It seems like it will only let me use tables. Something to this effect is what I get when I hit run:


    quote:
    --------------------------------------------------------------------------------

    "Error 3011 Jet Engine cannot find Object "SELECT ClientMap.ClientName, ClientMap.Consultant_LName FROM ClientMap"
    --------------------------------------------------------------------------------

    and then it points to debug at datData1.Refresh.

    What am I doing wrong?

    Comments on this post

    • Gran Roguismo agrees
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0
    You are using a datacontrol for you datasrouce.

    There should be a property (in the connection) that you can set, or in the dataControl itself where you tell it what kind of datasource to use in the query.

    Graham

    Comments on this post

    • Gran Roguismo agrees
  4. #3
  5. /(bb|[^b]{2})/

    Join Date
    Nov 2001
    Location
    Somewhere in the great unknown
    Posts
    5,163
    Rep Power
    792
    I will give you an example of how I do this.
    This is done with ADO.
    First, I have this function which returns a recordset (readonly)
    Code:
    'opens an ado read only recordset
    Public Function OpenRSro(sql As String) As ADODB.Recordset
        Dim rst As ADODB.Recordset
        Set rst = New ADODB.Recordset
        rst.Open sql, cn, adOpenForwardOnly
        Set OpenRSro = rst
    End Function
    Now, lets say I have a DataGrid control on my form and a text box and a command button. When the user clicks the command button, it executes the sql statement entered into the text box.
    So I would have some code like this:
    Code:
    Private Sub Command1_Click()
        Set DataGrid1.DataSource = OperRSro(Text1.Text)
    End Sub
    This is just a simplified version, but you get the idea. You have to set the datasource property = to the recordset returned, and it does have to be done with set.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2003
    Posts
    7
    Rep Power
    0
    Seems OK.

    Does the first query into the recordset execute correctly ?

    Can you look at the data in the recordset before you assign it to the grid?

    Have you tried setting the command type in your connection ?
    Add this to your code in the connection bit and replace the ? with the type of command youe would like it to be


    Dim a As ADODB.Command

    Set a = New ADODB.Command
    a.CommandText = ?

    The last to lines must be done before the Open command

IMN logo majestic logo threadwatch logo seochat tools logo