Open Recordset Method Failed

Can someone help me figure out why this won't open for me.

Dim strSQL As String, strF As String, strT As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


CurrentProject.Connection.Execute "Delete * From TempAssetTotals"

strF = txtDateFrom
strT = txtDateTo
 



strSQL = "SELECT DISTINCT qryFilters.Filters FROM qryFilters INNER JOIN AssetServiceDates ON qryFilters.AssetID = AssetServiceDates.AssetID" _
            & " WHERE (((qryFilters.Filters) Is Not Null) AND ((AssetServiceDates.Next) Between #" & strF & "# And #" & strT & "#))"


'''fails to open here....says method of open failed

With rs
    .Open strSQL, CurrentProject.Connection
    While Not .BOF And Not .EOF
        CurrentProject.Connection.Execute "INSERT INTO TempAssetTotals (ConsType, Size) Values ('Filters','" & rs("Filters") & "')"
    .MoveNext
        Wend
    .Close
End With
jb702Asked:
Who is Participating?
 
mbizupCommented:
Your SQL and code are fine.

The issue is in your naming conventions -- using reserved words.

In your SELECT statement, "Next" is a reserved word.

You will run into similar issues on your INSERT statement, because "Size" is also a reserved word.

If you must use these field names (ideally change them to something else), then enclose them in square brackets.  Note the changes below.

This will definitely work (tested), assuming a simple qryFilters.  If it does NOT work for you, post the SQL of qryFilters in your next comment.

Dim strSQL As String, strF As String, strT As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset


CurrentProject.Connection.Execute "Delete * From TempAssetTotals"

strF = txtDateFrom
strT = txtDateTo
  



strSQL = "SELECT DISTINCT qryFilters.Filters FROM qryFilters INNER JOIN AssetServiceDates ON qryFilters.AssetID = AssetServiceDates.AssetID" _
            & " WHERE (((qryFilters.Filters) Is Not Null) AND ((AssetServiceDates.[Next]) Between #" & strF & "# And #" & strT & "#))"


'''fails to open here....says method of open failed

With rs
    .Open strSQL, CurrentProject.Connection
    While Not .BOF And Not .EOF
        CurrentProject.Connection.Execute "INSERT INTO TempAssetTotals (ConsType, [Size]) Values ('Filters','" & rs("Filters") & "')"
    .MoveNext
        Wend
    .Close
End With

Open in new window

0
 
COACHMAN99Commented:
looks like something wrong with the SQL. Please capture this string in your debug window and attach so I can see.
0
 
rfportillaCommented:
Instead of "CurrentProject.Connection" can you try "CurrentProject.AccessConnection".  It may not make a difference, but worth a try.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
As an aside, see this list of reserved words:
http://office.microsoft.com/en-us/access-help/access-2007-reserved-words-and-symbols-HA010030643.aspx

Next and Size are included in the last list, titled "Access database engine reserved words".

Any of these words WILL cause errors in your recordset code, and generally should be avoided when naming fields (although enclosing them in square brackets identifies them as fields, and suppresses the errors).
0
 
jb702Author Commented:
I've requested that this question be deleted for the following reason:

Went another route
0
 
mbizupCommented:
I object to this simply being deleted.

The author never bothered to respond to any of the comments posted.

The original question was:

<< Can someone help me figure out why this {the recordset} won't open for me. >>

I tested my suggested correction before posting it, with the reserved word bracketed and it definitely resolved the error - allowing the recordset to properly open.

<< Went another route >>

The author MAY have encountered other problems with this method after resolving this error, in code, queries or other things not posted here -  leading him or her to go another route (or alternatively post feedback or follow up questions here).  However with the lack of feedback, there was no way for us to know.

The deletion was probably to clear out abandoned questions, in order to unlock the Question Wizard to post this new one:
http://www.experts-exchange.com/Database/MS_Access/Q_28430970.html
0
 
rfportillaCommented:
I also object.  I appreciate the fact that the author went a different route.  

If the question was answered then points should be awarded.  If the question is no longer needed/wanted/etc., then points should be awarded.  The commitment should be there when the question is posted.  

If there is another issue, so be it.  

Thanks.
0
 
jb702Author Commented:
Sorry about no seeing the post about the reserved words. I will make sure i keep this list handy to refer to. I believe you are correct in identify the problem.

thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.