Solved

Open Recordset Method Failed

Posted on 2014-04-17
9
917 Views
Last Modified: 2014-05-13
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
0
Comment
Question by:jb702
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 40008027
looks like something wrong with the SQL. Please capture this string in your debug window and attach so I can see.
0
 
LVL 9

Expert Comment

by:rfportilla
ID: 40008232
Instead of "CurrentProject.Connection" can you try "CurrentProject.AccessConnection".  It may not make a difference, but worth a try.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 40008474
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 61

Expert Comment

by:mbizup
ID: 40008477
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
 

Author Comment

by:jb702
ID: 40056654
I've requested that this question be deleted for the following reason:

Went another route
0
 
LVL 61

Expert Comment

by:mbizup
ID: 40056655
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
 
LVL 9

Expert Comment

by:rfportilla
ID: 40059248
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
 

Author Comment

by:jb702
ID: 40063660
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

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question