Solved

Open Recordset Method Failed

Posted on 2014-04-17
9
897 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

758 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

25 Experts available now in Live!

Get 1:1 Help Now