none
Recordset Will Not Display in Listbox

    Question

  • I am using the code below to display a recordset in a List Box. The code runs a stroed procedure which generates a recordset with 8 rows - but none of them show in the list box. Why not?

    ========================

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strConn As String, strSQL As String
       
        strConn = "ODBC;DSN=KitchenDB;"
        Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConn)

    strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & 5 'Me.txtBatchNo
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
        rs.MoveLast   
        rs.MoveFirst 
        Set Me.lstIntermediateProducts.Recordset = rs
        Me.lstIntermediateProducts.Requery


    PG A bit of experimentation by trial and error often helps.

    Tuesday, July 31, 2012 9:37 PM

Answers

  • *** >>> The thing about this method is I use a parameter with the query and it’s a CTE query. If I use the DAO .SQL method to re-state the SQL in the QueryDef, I get another error – “Invalid SQL statement: Delete, Insert, Procedure, Select or Update”.    In this circumstance, Access appears to treat a CTE query starting with “WITH” as invalid SQL.    It only seems to support Delete, Insert, Procedure, Select & Update as the error states. (Sigh ….).  I think the same limitation applies to the second method too as I have already tried something similar.

    I strongly suspect you're just doing it wrong.  If you make sure your querydef is set as a pass-through query before you ever assign its SQL property, then it shouldn't inspect your SQL at all.  However, using a pass-through query, you have to build the parameter value into the SQL string before you open the query.  Try something like this:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim strConn As String, strSQL As String
         
        strConn = "ODBC;DSN=KitchenDB"
        strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & 5 'Me.txtBatchNo
    
        Set db = CurrentDb
        Set qdf = CurrentDb.CreateQueryDef("")
        qdf.Connect = strConn
        qdf.ReturnsRecords = True
        qdf.SQL = strSQL
        
        Set rs = qdf.OpenRecordset()
    
        Set Me.lstIntermediateProducts.Recordset = rs

    I am starting to think that the DAO Pass Through is quite limited in the way it behaves with Access objects such as List Box  and a Report.   I thoght the whole point of Pass Through was  to pass through any SQL to the server where it will run.  But this is not the case.

    I have managed to bind a CTE recordset to a form successfully uisng a parameter. However, I have not been able use DAO recordsets with list boxes or reports successfully which is what I want to do.  If put the CTE SQL in  pass through query, DAO will not allow me to change the parameter in the query programmatically.

    I am starting to wonder if this behaviour is an Access defect or an intended limitation to stop users getting too clever???

    I think you are being too quick off the mark in blaming Access.  I believe this will work if you set it up correctly.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Friday, August 03, 2012 8:03 PM

All replies

  • Hi Patrick,

    Would you please tell me what kind of database the KitchenDB is?

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, August 01, 2012 7:23 AM
    Moderator
  • Helo Tom. Thanks for youtr interest.

    The back end dtaabase is SQL Server 2008.  I am using it because this application does a lot of tree walking up and down hierarchies where I need to use SQL Server stored procedures using CTEs, Common Table Expressions.


    PG A bit of experimentation by trial and error often helps.

    Wednesday, August 01, 2012 8:04 PM
  • Hi Patrick,

    OK, Would you please show me a simple SQL server's sql script for reproduce your issue.

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, August 02, 2012 2:14 AM
    Moderator
  • Hi Patrick.Grant01,

    if lstIntermediateProducts is a listbox you must set the rowsource property with your recordset i.e.

    Me.lstIntermediateProducts.Rowsource=rs

    HTH Paolo

    Thursday, August 02, 2012 6:30 AM
  • This is the stored procedure. It is running a CTE which list data for a BOM hierarchy.

    USE [Kitchen]
    GO
    /****** Object:  StoredProcedure [dbo].[usp_AllIntermediateProducts]    Script Date: 08/02/2012 17:00:25 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		Patrick Grant
    -- Create date: 
    -- Description:	Input: Batch No.  Calculates Intermediate Product 
    -- required to make a batch of products
    -- ============================/================
    ALTER PROCEDURE [dbo].[usp_AllIntermediateProducts] 
    	-- Add the parameters for the stored procedure here
    	@intBatchNo int
    AS
    BEGIN
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
    
    
    WITH BOMCTE
    AS
    (
    -- Lists All Ingredients which are Intermediate Products at First Level
    SELECT BatchProducts.BatchNo AS BatchNo
    	, Products.ProductNo AS ParentProductNo
    	, Products.ProductName AS ParentProductName
    	, Ingredients.ProductNo AS IntermediateProductNo
    	, Ingredients.IngredientName AS IntermediateProductName
    	, ProductIngredients.Quantity QtyNeeded
    	, ProdIngUnits.UnitName UnitNameNeeded
    	, IntProducts.Quantity RcpQty
    	, IntProdUnits.UnitName RcpUnits
    	,0 as lvl
    FROM  BatchProducts 
    	INNER JOIN Products ON Products.ProductNo = BatchProducts.ProductNo
    	INNER JOIN ProductIngredients ON Products.ProductNo = ProductIngredients.ProductNo 
        INNER JOIN Ingredients ON ProductIngredients.IngredientNo = Ingredients.IngredientNo
        INNER JOIN Units ProdIngUnits ON ProdIngUnits.UnitNo =ProductIngredients.UnitNo  
    	INNER JOIN Products IntProducts ON IntProducts.ProductNo = Ingredients.ProductNo
    	INNER JOIN Units IntProdUnits ON IntProdUnits.UnitNo = IntProducts.UnitNo 
    WHERE (BatchProducts.BatchNo = @intBatchNo ) AND 
    (NOT (Ingredients.ProductNo IS NULL))
    
    UNION ALL
    
    --/* Child Table in Hierarchy */
    -- Lists All Ingredients which are Intermediate Products at First Level
    SELECT --BatchProducts.BatchNo AS BatchNo
    	 @intBatchNo AS BatchNo
    	, Products.ProductNo AS ParentProductNo
    	, Products.ProductName AS ParentProductName
    	, Ingredients.ProductNo AS IntermediateProductNo
    	, Ingredients.IngredientName AS IntermediateProductName
    	, ProductIngredients.Quantity QtyNeeded
    	, ProdIngUnits.UnitName UnitNameNeeded
    	, IntProducts.Quantity RcpQty
    	, IntProdUnits.UnitName RcpUnits
    	,P.lvl + 1
    FROM BOMCTE P
    	-- INNER JOIN Products ON P.ParentProductNo = Products.ProductNo new
    	INNER JOIN BatchProducts ON P.IntermediateProductNo=BatchProducts.ProductNo 
    	INNER JOIN Products ON Products.ProductNo = BatchProducts.ProductNo  
    	-- INNER JOIN Products ON Products.ProductNo = P.ParentProductNo New
    	INNER JOIN ProductIngredients ON Products.ProductNo = ProductIngredients.ProductNo 
        INNER JOIN Ingredients ON ProductIngredients.IngredientNo = Ingredients.IngredientNo 
        INNER JOIN Units ProdIngUnits ON ProdIngUnits.UnitNo =ProductIngredients.UnitNo 
    	INNER JOIN Products IntProducts ON IntProducts.ProductNo = Ingredients.ProductNo
    	INNER JOIN Units IntProdUnits ON IntProdUnits.UnitNo = IntProducts.UnitNo 
    WHERE --(BatchProducts.BatchNo = 5) AND 
    (NOT (Ingredients.ProductNo IS NULL))
    	 
    )
    
     --SQL Below to Take Data from the CTE Query above and join to Batch Products etc to get quantity of each product
     --on each batch
    SELECT DISTINCT BOMCTE.BatchNo
    	, ParentProductNo
    	, ParentProductName
    	, IntermediateProductNo 
    	, IntermediateProductName
    	, QtyNeeded
    	, UnitNameNeeded
    	, RcpQty
    	, RcpUnits
    	, CAST(ROUND(QtyNeeded/RcpQty,2) as decimal(18,2)) LotsRqd
    	,BP.Lots LotsOnBatch
    	--, lvl
    FROM BOMCTE
     --No of Lots on Batch
    LEFT JOIN BatchProducts BP on BP.ProductNo = BOMCTE.IntermediateProductNo
    AND BP.BatchNo = @intBatchNo 
    ORDER BY IntermediateProductName
    
    END


    PG A bit of experimentation by trial and error often helps.

    Thursday, August 02, 2012 5:14 PM
  • Hi Paolo

    It didn't work.  I think Rowsource only works for SQL statemements, not recordset objects. I even tried it as:

    SET Me.lstIntermediateProducts.Rowsource=rs


    PG A bit of experimentation by trial and error often helps.

    Thursday, August 02, 2012 5:19 PM
  • What are the ColumnCount and ColumnWidths properties of the list box?

    Why do you have this line:

        Me.lstIntermediateProducts.Requery

    ?  If anything, I'd think that would wipe out the recordset you just assigned to the list box.  If you remove it, do the rows appear in the list box?

    After you assign the recordset to the list box's Recordset property, but before you execute that Requery line, what do you see when you inspect the list box's Recordset property?  After you execute the Requery line, what do you see?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, August 02, 2012 6:16 PM
  • Dirk

    Column Count is 7 - and width of each column is 2 cm.

    The requery is to display the recordset. Normally, I would set the SQL or  recordset for a form or similar object, then requery it to display the data.

    I can see 8 records in the recordset in the Locals window, so the recordset is being populated. I can load the same recordset and display it on a form. For some reason, so far, I  cannot do it on the list box.  

    When you say what do I see when inspect the list box's recordset? How should I look for it? I tried "? me.lstBox.Recordset in the immediate window and I get a type mismatch error.   I also tried "? isnull(Me.lstIntermediateProducts.Recordset)" which returns "false", so there is something in the list box recordset but I cannot tell what it is.   The Me object in the Locals window is so large, I cannot find the list box to see what the listbox recordset contains...


    PG A bit of experimentation by trial and error often helps.

    Thursday, August 02, 2012 9:56 PM
  • I am trying to run a piece of code to to execute a SQL Server stored procedure and retunr the results to a report. I am getting an error suggesting it can only be done with an ADP.   I am using access 2010 and the file name is FileName.adp.accdb which I thin is an ADP.  The code is below - any suggestions to get this to work one way or another ?   I have to use a SQL Stored Procedure with a parameter to run a CTE hierarchical query.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strConn As String, strSQL As String
    
    strConn = "ODBC;DSN=KitchenDB;"
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConn) 'Note how the db is set....
    
    strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & Me.txtBatchNo
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
    
    rs.MoveLast
    rs.MoveFirst
    
    'Populate Report
    Set Me.Recordset = rs
    Me.Requery
    


    PG A bit of experimentation by trial and error often helps.

    Thursday, August 02, 2012 10:03 PM
  • Column Count is 7 - and width of each column is 2 cm.

    That seems okay, although looking at the query your SP runs, it seems to be returning 11 columns.  So presumably you don't care about the last 4.

    The requery is to display the recordset. Normally, I would set the SQL or  recordset for a form or similar object, then requery it to display the data.

    If you set the *RecordSource* property of a form or the *RowSource* property of a list/combo box, then you might want to requery the object (except that you don't have to do that for a form, because a form is always requeried automatically if you set its RecordSource.

    However, if you are setting the *Recordset* property, you would not requery it, because the point of requerying is to get a new recordset, and you just gave it one.

    I can see 8 records in the recordset in the Locals window, so the recordset is being populated. I can load the same recordset and display it on a form. For some reason, so far, I  cannot do it on the list box.  

    Did you try doing it without the Requery? And a question I forgot: what is in the RowSourceType and RowSource properties of the list box? The RowSourceType should be "Table/Query".

    When you say what do I see when inspect the list box's recordset? How should I look for it? I tried "? me.lstBox.Recordset in the immediate window and I get a type mismatch error.   I also tried "? isnull(Me.lstIntermediateProducts.Recordset)" which returns "false", so there is something in the list box recordset but I cannot tell what it is.   The Me object in the Locals window is so large, I cannot find the list box to see what the listbox recordset contains...

    In the Locals window, you can expand the tree of Me -> Controls -> lstIntermediateProducts -> Recordset, and look at the Fields collection and the RecordCount property.  Or, in the Immediate window, you can run a line of code like this:

        ?Me.lstIntermediateProducts.Recordset.RecordCount

    to see how many records are in the recordset.  Or you could enter a line like this:

        ?Me.lstIntermediateProducts.Recordset.Fields(0), Me.lstIntermediateProducts.Recordset.Fields(1)

    to see the values of the first and second fields in the current record of the recordset.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 03, 2012 12:05 AM
  • Dirk

    Thanks for the comprehensive suggestions.  I am at least close to proving the data should be accessible to the list box.

    That seems okay, although looking at the query your SP runs, it seems to be returning 11 columns.  So presumably you don't care about the last 4. ***>>> Correct.    Have now changed it to 11 to rule this as the cause...no luck.

    If you set the *RecordSource* property of a form or the *RowSource* property of a list/combo box, then you might want to requery the object (except that you don't have to do that for a form, because a form is always requeried automatically if you set its RecordSource. ***>>> OK, Requery would be redundant.

    If you are setting the *Recordset* property, you would not requery it, because the point of requerying is to get a new recordset, and you just gave it one. ***>>> OK, Requery would be redundant.

    Did you try doing it (the list box) without the Requery? ***>>> Tried it, the list box  still shows as empty ......but do I need to do anything to tie columns in the query / sp to the columns in the list box as you would for a form?

    And a question I forgot: what is in the RowSourceType and RowSource properties of the list box? The RowSourceType should be "Table/Query". ***>>> It is table/query type. Others do not work.

    In the Locals window, you can expand the tree of Me -> Controls -> lstIntermediateProducts -> Recordset, and look at the Fields collection and the RecordCount property. ***>>> The Me object in my locals window has a very different tree to the one you describe, and its very large and I can't find the list box.

     In the Immediate window, you can run a line of code like this:

    ? Me.lstIntermediateProducts.Recordset.RecordCount. ***>>> Returns 6 so the list box recordset does contain records.

    ? Me.lstIntermediateProducts.Recordset.Fields(0 - 1) ***>>> Returns 5 & 33 which is correct if I run the sp in SQL server to check. 

    ***>>> Conclusion: The Recordset is correctly stored in the list box with all the correct values, so that is all good.

    ***>>> What else have I tried?   Attempt 1: Me.lstIntermediateProducts.Recordset.MoveLast in VBA. Attempt 2: Put dummy sql in list box row source using same field names as the store proc. Attempt 3: Set a field for List Box control source which should not be necessary.

    ***>>> Big Outstanding Qestion: From what I can see, the stored proc data must be accessible to the list box, what can I do to force it to display?  Are we looking at an Access defect or have I missed something?




    PG A bit of experimentation by trial and error often helps.



    Friday, August 03, 2012 11:22 AM
  • Dirk

    One more thing, if I click refresh all on the form I get an error. "Invalid SQL Statement: Expected SELECT, Delete, Insert, Procedure, Select or Update".

    I do not think there is any invalid SQL in the form or list box row source as I can run the same SQL in SSMS. I think it may be to do with the list box issue. If I comment out the code to set the list box row source - then I get another error saying the record source does not exist.


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 11:53 AM
  • I am trying to run a piece of code to to execute a SQL Server stored procedure and return the results to a report. I am getting an error suggesting it can only be done with an ADP.   (I am using access 2010 and the file name is FileName.adp.accdb which I think is an ADP.)  The code is below - any suggestions to get this to work one way or another ?  

    I have to use a SQL Stored Procedure with a parameter to run a CTE hierarchical query.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strConn As String, strSQL As String
    
    strConn = "ODBC;DSN=KitchenDB;"
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConn) 'Note how the db is set....
    
    strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & Me.txtBatchNo
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
    
    rs.MoveLast
    rs.MoveFirst
    
    'Populate Report
    Set Me.Recordset = rs
    Me.Requery 
    


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 2:33 PM
  • Found a possible way round this ... its awkward but potentially do-able and it looks look it will will work.

    I tried it using an Access PassThrough with SQL incorporating the quey parameter and it ran perfectly.

    So presumably, if I create a querydef using DAO and generate the SQL before running the report that may work, so I am going to try it.


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 3:46 PM
  • I am trying to run a piece of code to to execute a SQL Server stored procedure and return the results to a report. I am getting an error suggesting it can only be done with an ADP.   (I am using access 2010 and the file name is FileName.adp.accdb which I think is an ADP.)  The code is below - any suggestions to get this to work one way or another ?  

    I have to use a SQL Stored Procedure with a parameter to run a CTE hierarchical query.

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strConn As String, strSQL As String
    
    strConn = "ODBC;DSN=KitchenDB;"
    Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, strConn) 'Note how the db is set....
    
    strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & Me.txtBatchNo
    
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)
    
    rs.MoveLast
    rs.MoveFirst
    
    'Populate Report
    Set Me.Recordset = rs
    Me.Requery 
    


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 5:22 PM
  • Did you try doing it (the list box) without the Requery? ***>>> Tried it, the list box  still shows as empty ......but do I need to do anything to tie columns in the query / sp to the columns in the list box as you would for a form?

    No, that would not be necessary.

    In the Locals window, you can expand the tree of Me -> Controls -> lstIntermediateProducts -> Recordset, and look at the Fields collection and the RecordCount property. ***>>> The Me object in my locals window has a very different tree to the one you describe, and its very large and I can't find the list box.

    I made a mistake, as the Controls collection won't list the controls under it by name.  Instead, you'll see a series of "Items":  Item1, Item2, Item3, etc.  Each of these represents a control on the form -- this *is* a form, right? -- and the properties of that control (including the Name property) will be shown underneath it when you expand that subtree.

     In the Immediate window, you can run a line of code like this:

    ? Me.lstIntermediateProducts.Recordset.RecordCount. ***>>> Returns 6 so the list box recordset does contain records.

    ? Me.lstIntermediateProducts.Recordset.Fields(0 - 1) ***>>> Returns 5 & 33 which is correct if I run the sp in SQL server to check. 

    Okay, so that sounds like the recordset is being assigned properly.

    ***>>> Conclusion: The Recordset is correctly stored in the list box with all the correct values, so that is all good.

    ***>>> What else have I tried?   Attempt 1: Me.lstIntermediateProducts.Recordset.MoveLast in VBA. Attempt 2: Put dummy sql in list box row source using same field names as the store proc. Attempt 3: Set a field for List Box control source which should not be necessary.

    ***>>> Big Outstanding Qestion: From what I can see, the stored proc data must be accessible to the list box, what can I do to force it to display?  Are we looking at an Access defect or have I missed something?

    I'm thinking we've missed something, but it isn't obvious what. This is interesting. When I use a simple form, a simple 2-column list box, and a simple query of an Access table, this code works fine to load the list box:

        Dim rs As DAO.Recordset
        
        Set rs = CurrentDb.OpenRecordset("SELECT ID, Description FROM Table1")
        
        Set Me.List0.Recordset = rs
    

    The appropriate rows are shown in the list box.  So it clearly works in principle.  Something special is going on in your case.  I don't know if it has anything to do with the way you open your recordset, or the stored procedure itself, or what.

    Out of curiosity, what happens if you comment out the line "SET NOCOUNT ON" in your stored procedure?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 03, 2012 6:04 PM
  • Okay, I've been fooling around with this, opening a recordset on a SQL Server database, and I can't get code like yours to work, either.  I think the sticking point is that the recordset isn't owned by the current database, but rather by the separate database object you opened.

    HOWEVER, I find two methods that do work.

    The first is to simply create a stored pass-through query in the current database.  Then you can either just set this statically as the rowsource of your list box, or you can open a recordset on it (from the current database, not a separate one), and then assign that to the list box's .Recordset property.  I tried this, and it worked fine.

    The second is to create a temporary pass-through query in code, open a recordset from it, and assign that to the list box's .Recordset property.  I tried that, and it worked fine also.  Here's the code I used:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim strConn As String, strSQL As String
         
        strConn = "ODBC;DSN=PMSDATA_ODBC2"
        strSQL = "SELECT CostCtrID, CCName FROM dbo.tblCostCenters"
        
        Set db = CurrentDb
        Set qdf = CurrentDb.CreateQueryDef("", strSQL)
        qdf.Connect = strConn
        
        Set rs = qdf.OpenRecordset()
    
        Set Me.List0.Recordset = rs

    So it does work, but apparently not the way you've been trying to do it.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 03, 2012 6:33 PM
  • A file name of FileName.adp.accdb  would tend to tell me that it is not an ADP.  You can tell if you have an ADP because there is NO QUERIES window available.  It is named VIEWS.  And you cannot have any local tables in the database file.  So, they would all be on SQL Server and you can't create a local table.


    Bob Larson, Access MVP 2008, 2009, 2011

    Friday, August 03, 2012 6:39 PM
  • Hi Dirk

    Thanks for your continuing interest and hanging on in there.

    I think the sticking point is that the recordset isn't owned by the current database, but rather by the separate database object you opened. ***>>> OK ....

    The first is to simply create a stored pass-through query in the current database. Then you can either just set this statically as the rowsource of your list box, or you can open a recordset on it (from the current database, not a separate one), and then assign that to the list box's .Recordset property. I tried this, and it worked fine.

    *** >>> The thing about this method is I use a parameter with the query and it’s a CTE query. If I use the DAO .SQL method to re-state the SQL in the QueryDef, I get another error – “Invalid SQL statement: Delete, Insert, Procedure, Select or Update”.    In this circumstance, Access appears to treat a CTE query starting with “WITH” as invalid SQL.    It only seems to support Delete, Insert, Procedure, Select & Update as the error states. (Sigh ….).  I think the same limitation applies to the second method too as I have already tried something similar.

    I am starting to think that the DAO Pass Through is quite limited in the way it behaves with Access objects such as List Box  and a Report.   I thoght the whole point of Pass Through was  to pass through any SQL to the server where it will run.  But this is not the case.

    I have managed to bind a CTE recordset to a form successfully uisng a parameter. However, I have not been able use DAO recordsets with list boxes or reports successfully which is what I want to do.  If put the CTE SQL in  pass through query, DAO will not allow me to change the parameter in the query programmatically.

    I am starting to wonder if this behaviour is an Access defect or an intended limitation to stop users getting too clever???


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 7:46 PM
  • *** >>> The thing about this method is I use a parameter with the query and it’s a CTE query. If I use the DAO .SQL method to re-state the SQL in the QueryDef, I get another error – “Invalid SQL statement: Delete, Insert, Procedure, Select or Update”.    In this circumstance, Access appears to treat a CTE query starting with “WITH” as invalid SQL.    It only seems to support Delete, Insert, Procedure, Select & Update as the error states. (Sigh ….).  I think the same limitation applies to the second method too as I have already tried something similar.

    I strongly suspect you're just doing it wrong.  If you make sure your querydef is set as a pass-through query before you ever assign its SQL property, then it shouldn't inspect your SQL at all.  However, using a pass-through query, you have to build the parameter value into the SQL string before you open the query.  Try something like this:

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim qdf As DAO.QueryDef
        Dim strConn As String, strSQL As String
         
        strConn = "ODBC;DSN=KitchenDB"
        strSQL = "EXEC [dbo].[usp_AllIntermediateProducts] " & 5 'Me.txtBatchNo
    
        Set db = CurrentDb
        Set qdf = CurrentDb.CreateQueryDef("")
        qdf.Connect = strConn
        qdf.ReturnsRecords = True
        qdf.SQL = strSQL
        
        Set rs = qdf.OpenRecordset()
    
        Set Me.lstIntermediateProducts.Recordset = rs

    I am starting to think that the DAO Pass Through is quite limited in the way it behaves with Access objects such as List Box  and a Report.   I thoght the whole point of Pass Through was  to pass through any SQL to the server where it will run.  But this is not the case.

    I have managed to bind a CTE recordset to a form successfully uisng a parameter. However, I have not been able use DAO recordsets with list boxes or reports successfully which is what I want to do.  If put the CTE SQL in  pass through query, DAO will not allow me to change the parameter in the query programmatically.

    I am starting to wonder if this behaviour is an Access defect or an intended limitation to stop users getting too clever???

    I think you are being too quick off the mark in blaming Access.  I believe this will work if you set it up correctly.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html


    Friday, August 03, 2012 8:03 PM
  • Drat !!!  I can see queries, so its not an ADP. I have now created an ADP and imported forms into it from an accdb....now this looks like it is not working well because:

    • When I run a form, I am getting all sorts of errors like:
    1. Click on button to open form - Syntax Error :
    2. Try to compile VBA - syntax error on "Dim db as DAO.Database"
      Any thoughts? Will I have to rebuild everything to get round these problems?


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 8:16 PM
  • Drat !!!  I can see queries, so its not an ADP. I have now created an ADP and imported forms into it from an accdb....now this looks like it is not working well because:

    • When I run a form, I am getting all sorts of errors like:
    1. Click on button to open form - Syntax Error :
    2. Try to compile VBA - syntax error on "Dim db as DAO.Database"
      Any thoughts? Will I have to rebuild everything to get round these problems?

    ADPs are very different from ACCDBs and MDBs.  Before you decide to convert your app to an ADP, you had better do some reading on the differences.  Any code you have that uses DAO objects to manipulate the database will have to be rewritten to use ADO instead.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Friday, August 03, 2012 8:25 PM
  • Dirk

    I will check my  QueryDef code against yours...

    It is starting to occur to me I may have made this more difficult than it ever had to be.   This was all done in an accdb and perhaps it would have been much simpler to have done it in an ADP - Access Data Project.  If I had done that, then the stored procs show as view/queries which can be used directly on list boxes and reports, so perhaps there was no need for recordsets in VBA. Am now reworking everything in accdb and adp to figure what was best / easiest.


    PG A bit of experimentation by trial and error often helps.

    Friday, August 03, 2012 10:44 PM
  • Dirk

    Thanks for your help.  Problems with the List Box and Report now resolved.   You were right I was doing wrong while thinking I was doing it correctly. So too quick off the mark to blame Access.   But it does appear quite trick to get the DAO working as you want.

    The mistake I made was bit more fundamental than getting the VBA correct, if I had created an ADP, then the VBA would have been much simpler.

    Now I know.


    PG A bit of experimentation by trial and error often helps.

    Saturday, August 04, 2012 7:33 AM
  • I see what you mean.

    • If I use an ADP, then there is little or no need to use VBA and DAO recordsets to use recordsets to link stored procedures to forms, reports etc.  So the maintenance and development is easier.
    • If I have an existing Access Database and wish to convert to an ADP, if I have a lot of code using DAO, then it has to be converted to ADO or becomes redundant.
    • An ADP can link to only one SQL server Database whereas an Access Dtaabase can link to multiple Access and SQL Server databases.

    I have now got both versions of the database working, an adp and an accdb and can compare them.


    PG A bit of experimentation by trial and error often helps.

    Saturday, August 04, 2012 8:18 AM
  • To anyone who comes to this thread with the problem of Lisboxes and Comboboxes not displaying results of a Pass-through recordset, the answer is in fact a QueryDef.

    I had a similar problem which was discussed here.

    In my case I had a dao.database object instantiated called db which had connection information to the SQL server and saved as the "ODBC" property of my class.  The end result was this:

    Public Function OpenRecordset(SQL As String, Optional dbType As RecordsetTypeEnum = dbOpenSnapshot, Optional dbOptions As RecordsetOptionEnum = dbSQLPassThrough) As Recordset
        Dim qdef As QueryDef
        Set qdef = db.CreateQueryDef("", SQL)
        qdef.Connect = Me.ODBC
        Set OpenRecordset = qdef.OpenRecordset(dbType, dbOptions)
    End Function

    As you can see, create an anonymous querydef, set it's connection string to the ODBC connection string and set your SQL string.  Then open a recordset with that querydef and it will work for the recordsets of your listboxes and comboboxes.  

    This is very similar to some of the suggestions in this thread, but I thought I would help add to the conversation with my own solution.


    Ruler of the undershorts

    Thursday, March 27, 2014 9:27 PM