Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: Opening Query in ADO

    I need to create a recordset using ADO code (because everything else is in ADO) that the user can view and edit

    Currently I use something like the following.

    Code:
     
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim SQL As String
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
     
    SQL = "SELECT partNum, account FROM customers" 
     
    rs.open SQL, cn
    This works fine and allows me to manipulate the recordset in code but I want to display the results of the recordset back to the user in the standard query datasheet.

    Thanks!

    Steve

  2. #2
    Join Date
    Jul 2004
    Posts
    64
    Why not just populate a form with the recordset in datasheet view?

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    I need to do this from several areas, the fields will differ for each query. Is there a way to make the form adapt to the fields of the query so I can have one form stored to use anytime I need to display query results? I would prefer not to have a form for this purpose, that is what a query is for.

    I have opened a code built query using DAO code and I like the result, I just wanted to change this over to ADO.

    This is what I am looking for, but I want an ADO version, I can't find the ADO equivalent of a querydef

    Code:
     
    	Dim db As Database
    	Dim qdf As QueryDef
    	Dim SQL As String
    	Set db = CurrentDb
     
    	SQL = "SELECT * FROM balances"
     
    	Set qdf = db.CreateQueryDef("temp1", SQL)
    	DoCmd.OpenQuery (qdf.Name)
     
    	db.QueryDefs.Delete qdf.Name

    Steve

  4. #4
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    No way that I know of that automatically adapts a form to the actual columns in a recordset. I think you're in for some fairly detailed nuts & bolts programming using an unbound form to do this.

    Also no easy way to bind a form to a recordset that you have created dynamically via ADO (or DAO for that matter). I have in the past done something similar but you need to create the Tabledef and then poke all the rows into the new table. In this way Access then 'knows' this table and can bind forms to it.

    Why not abandon the ADO approach altogether and as Bbender27 suggests use a bound data sheet view. Try playing with the Filter and OrderBy propeties to limit the information shown and the sequence in which it is shown. Still can't do anything about the columns.
    Rod

    fe_rod@hotmail.com

  5. #5
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    By the way you need ADOX to create tables using the ADO model and I have trouble getting ADOX to work properly when creating table fields/columns. All seems fine until I try to poke a null into the field; Access then reports that the field is not nullable. If I try to set the nullable property for that field I get another error. Needless to say my attempt to create tables dynamically using ADOX was abandoned.
    Last edited by Rod; 08-24-04 at 06:59.
    Rod

    fe_rod@hotmail.com

  6. #6
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Unhappy

    So you are telling me that opening an actual query window is something that is simple to do in DAO but impossible in ADO? That doesn't seem right somehow, I figured it would be just as easy in ADO code.

    Steve

  7. #7
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    No, I'm not saying that. In fact I think the premise that a query window depends upon your data access object model is wrong.

    ADO has a very condensed object model consisting of: Connection; Errors, Error; Command, Parameters, Parameter; Recordset, Fields, Field. No mention there of any query object. To match the functionality of DAO (or at least provide some of the essentials) Microsoft provides the ADO extension, ADOX. This extension provides objects such as Table, Index, Column, View, User - all those good things you need for the Jet RDBMS. (Incidentally it also provides the Procedure object which is useful for dynamically altering your VBA code - but we never do those sort of things, do we?) Again there is no mention of a query object.

    Queries are definitely members of Access. Query itself is a string property of the Form object and I am suggesting that you may be able to use this property to alter the underlying recordset of a form. You may also wish to use the Filter property as 'fine tuning' (remember to set FilterOn to True). However this does not overcome your requirement to adapt the form design to accommodate only those fields that are in your query result set. It can be done but is tedious, messy and almost unmaintainable. I would suggest you experiment to see if you can design a limited number of form variation that fulfil the requirements. When you know the query you are going to use then by inference you know the form variant.

    Anyone else out there got any thoughts on this?
    Rod

    fe_rod@hotmail.com

Posting Permissions

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