Record sets

[tranchemontaigne]

I have what I think should be a relatively simple task that is particularly difficult to implement. I'm using a VBA module to dynamically build a SQL statement and assign it to the strSQL variable.

SELECT 
EIWS_PFC_SURVEY_STANDARDIZED.*
, EIWS_PFC_SURVEY_PERSON.Event
, EIWS_PFC_SURVEY_PERSON.Respondent_ID
, EIWS_PFC_SURVEY_PERSON.Consent
, EIWS_PFC_SURVEY_PERSON.Eligible
, EIWS_PFC_SURVEY_PERSON.Age
, EIWS_PFC_SURVEY_PERSON.Date_Survey_Imported
, EIWS_PFC_SURVEY_PERSON.Date_Sent_to_LIMS 
FROM 
  EIWS_PFC_SURVEY_STANDARDIZED INNER JOIN   
  EIWS_PFC_SURVEY_PERSON ON 
  EIWS_PFC_SURVEY_STANDARDIZED.UniqueKey = 
  EIWS_PFC_SURVEY_PERSON.EWIS_PFC_Survey_UniqueKey 
WHERE (((EIWS_PFC_SURVEY_PERSON.Event) Like '*') AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.P_Worked) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.P_Lived) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.P_ChildCare) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.P_Mother) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.Am In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.Ma) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.Me) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.Li) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_STANDARDIZED.Be) In (1,0,88,99)) AND 
  ((EIWS_PFC_SURVEY_PERSON.Date_Survey_Imported) Between #9/9/1999# And #9/9/2099#) );

The query runs perfectly from a query design window and pulls a little more than 1,000 records, yet when I attempt to build an ADODB recordset using this SQL, the recordset always comes up empty.

Here's the basic code

'// Declarations from top of module include
Option Compare Database
Option Explicit

'// declarations within the function include
Dim strSQL As String
Dim rst1 As ADODB.Recordset

'// block of code that builds a dynamic SQL statement using strSQL
'// goes here
'//
'// a break point is used to debug.print the contents of strSQL at run time
'// and copy/paste it into a query design window where it runs perfectly
'//
'// next the rst1 recordset is build using strSQL

    Set rst1 = New ADODB.Recordset
    rst1.ActiveConnection = CurrentProject.Connection
    rst1.Open strSQL ', , adOpenForwardOnly, adLockOptimistic

'// breakpoint immediately after the recordset is opened confirms that
'// rst1.eof = true

It's hard to tell from this post, but there are white space characters at the end of each row in the strSQL statement above.

I don't think I'm hitting a hard character limit for the length of a SQL statement....

What's going wrong, or more importantly, what can I do to fix this?

 

[Uncle Gizmo]

I don't know, but I think I check the dates first because queries are more forgiving with dates. When you put them in code it's less forgiving. Other than that I have a load of questions to ask before I can think about the problem.

 

[MarkK]

I don't see in the code where strSQL is ever assigned a value. The code you posted will not run since those statements are invalid outside a procedure.

Am I missing something?

 

[tranchemontaigne]

I just commented out the code that adds date criteria to the SQL statement to test your theory. Here's the result of the test:

I extracted the value of strSQL (without date criteria) immediately before rst1 is created using a debug.print statement in the immediate window

I successfully copied/pasted the statement into a query design window and observed more than 1,000 records in the result set

Using another break point to examing rst1 immediately after it was opened reveals that the rst1.eof property is still true (even when date criteria are omitted).


ENVIRONMENT:
MS Access 2010 on Windows 7.
The tables are stored in a back end accdb file.
The VBA module exists in a front end accdb file

 

[tranchemontaigne]

MarkK,

Thanks for your help here. the code to build strSQL is several hundred lines deep and was excluded because I thought it would only add an unnecessary level of complexity to the problem description. Likewise, there are a few hundred lines of code intended to use the rst1 recordset object

What I posted originally is the output I get from the immediate window when I debug.print strSQL.

Does this help?



PS: Uncle Gizmo, I appreciate your time and interest in this problem too. I just got too caught up in the details of issue to verbalize my appreciation.

 

[MarkK]

Well, the Access query design grid creates a DAO.QueryDef, and if that executes successfully, maybe it's worth a try to open a DAO.Recordset rather than an ADODB.Recordset, and see if that makes a difference. There are differences between the SQL syntax that ADODB and DAO will allow.

Maybe "BETWEEN" is not valid in ADODB. I never use BETWEEN anyway, I typically write that express like . . .

t.Date_Survey_Imported > #9/9/1999# And t.Date_Survey_Imported < #9/9/2099#

. . . but this is guesswork on my part.