Solved

Open Recordset Method Failed

Posted on 2014-04-17
9
908 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
 
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

948 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now