How to embed and format sql in VBA

[subedimite]

This is the first time I am trying to call SQL in VBA. I am trying to read the result of the SQL in a recordset.

My Code Looks like this:

Dim dbs As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set dbs = CurrentDb

    
strSQL = "SELECT MyTests.Preconditions FROM QryTestFailure, MyTests WHERE ((([qryTestFailure]![TestID])=[MyTests]![RecNum]));"

Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset)
NoOfRecords1 = rs.RecordCount

I have directly copied SQL from the Query design, with the double quotes added at the start and the end. The code compiles but does not work. The SQL itself is working while called from Access query object. The query selects data from another query ‘queryTestFailu re’ and table ‘MyTests’ (I should have called this ‘tblMytest s’:) ). Reading some articles in the web it seems like I need to modify the SQL string inserting the symbols: doublequotes, ampersand and single quotes. Why do I need to do that, Could I please have some help on it how is done?


Thanks

[Delerna]

Your code looks OK to me

Why do you say it doesn't work ?
I have a feeling I know why but I will wait for you to answer

[subedimite]

Well, I am saying it does not work because when I run the query seperately, I end up with 12 records. In the above code rs.RecordCount is returning 1. Also when I use GetRows method to read the recordset ,I am ending up with nothing. Am I doing something silly:) or missing something obvious?

[subedimite]

Hi, When I restarted the database it was working.


Just was not confident as it was pretty much the first time, I did this.

However when I reset the code that’s where it seems to be the problem. The 'rs.RecordCount ' now returns 6 which is a right number; However NoOfRecords does not get assigned that value. It is showing the value of 1 (Very wierd). It seems, this all is due to lack of ' error handling'

I need to have a look at it bit deeper.

[Delerna]

Thought that would be the answer

The thing to be aware of here is, immediately after a recordset has been opened rs.recordcount will not return the number of records

it only returns 1.
I am not sure why but a little research would reveal that

Try this

Dim dbs As Database 
Dim rs As DAO.Recordset 
Dim strSQL As String 
Set dbs = CurrentDb 
  
  
strSQL = "SELECT MyTests.Preconditions FROM QryTestFailure, MyTests WHERE ((([qryTestFailure]![TestID])=[MyTests]![RecNum]));" 
  
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset) 
rs.MoveLast
NoOfRecords1 = rs.RecordCount 
msgbox NoOfRecords1

[subedimite]

Thought that would be the answer

The thing to be aware of here is, immediately after a recordset has been opened rs.recordcount will not return the number of records

it only returns 1.
I am not sure why but a little research would reveal that

Try this

Dim dbs As Database 
Dim rs As DAO.Recordset 
Dim strSQL As String 
Set dbs = CurrentDb 
  
  
strSQL = "SELECT MyTests.Preconditions FROM QryTestFailure, MyTests WHERE ((([qryTestFailure]![TestID])=[MyTests]![RecNum]));" 
  
Set rs = dbs.OpenRecordset(strSQL, dbOpenDynaset) 
rs.MoveLast
NoOfRecords1 = rs.RecordCount 
msgbox NoOfRecords1

Hi Thanks for that:

I got the answer; RecordCount Method can only count all the records when it fully moves to the last Record. I hope I am right.

But this happened only when I read the recordset form SQL. When I read it directly from the table or Query it has no problem.